Flush tables With read lock - possible deadlocking

[Matthew Moles]

Hi,

We have an issue where “Flush tables With read lock” command (a part of a daily backup) sometimes takes 50 seconds to complete, the rest of the time it takes less than 50ms. We use InnoDB for the vast majority of our tables so the flush part should be quick (MyISAM is used on a few tables but these have not had the data updated in over 2 years).
Managed to get a capture of the running processes at the time of the wait, the vast majority of connections including the “Flush Tables with read lock” were all in the “Waiting for global read lock” state. One was in the “updating” state, however the update was to a single row on a table with 7 rows, looking up the row by an index column (varchar(50)). Also seems to a bit too much of a coincidence that it takes just over 50 seconds to get a lock which is the same time as innodb_lock_wait_timeout.

We are using mysql 5.5.20-log on RHEL 5.6, access is via .net connector and sprocs for the update command.
It seems as though the transaction which is attempting to update a record grabs a lock for some data, then the “FlushTables with read lock” grabs the rest of the lock needed. After this then are in deadlock of a type which InnoDB can’t detect. After innodb_lock_wait_timeout the updating record rolls back allowing the “FlushTables with read lock” to complete.

Can anyone confirm if this is possible or offer any insights into other causes/solutions?

Regards

Matthew