… all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that… that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will… breaks reliably on the __tmp_x table why ? The first question sounds like a potential MySQL bug. Are you sure this is…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD LOCKS… no need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… database at all (use mysql -A in this case, to prevent it from getting field list from all tables) Reads though would not be blocked, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: Table locks in SHOW INNODB STATUS
June 8, 2010 By 1 Comment
… lock struct(s), heap size 368 MySQL thread id 8, query id 164 localhost root TABLE LOCK table `test/t1` trx id 0 4872 lock…locks are called intention lock, hence abbreviation IX = Intention eXclusive. Intention locks do not work the same way as table locks – Intention exclusive lock does not prevent…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…enforce logical referential integrity (RI) in SQL, preventing rows from becoming orphaned. But then …control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> …early so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the cost…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
…also get into synchronization aspects making backup which prevents from having hot backup in many cases….. We could get rid of SQL level table locks and using Innodb internal data dictionary instead… way without nasty “FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more….
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
…configuration adds effectively global table locks besides extra overhead you get from external locking. Plus key cache … large tables. Furthermore as I remember MySQL simply was not repairing corrupted tables in this…doing FLUSH TABLES does not really works as it does not allow to prevent race …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…hundred customer downtime incidents, and their causes and preventions. You can read more about that research …by MySQL, you won’t have any problems until you try to create or drop a table, … all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped…
Post: Watch out for your CRON jobs
October 14, 2009 By 19 Comments
… having “production requirement” of no cron jobs allowed unless they prevent themselves from started in multiple copies or put a wrapper… locks (do not just create files, files left after script crash can prevent it from starting again) or using GET_LOCK function in MySQL… gradually until it could not complete in time. Create the table in the database and store information about how long cron…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… some > exception however. For example Innodb tables do internal full table > lock if auto_increment is used so …Read more in my > Why MySQL Could be slow with Large Tables article. Deciding whether or not … the indexes to join rows together, potentially preventing a table scan. The trick is to know …
