Queries/Connections Hanging During Backup with --single-transaction

[Justin Dennis]

I have run into a situation where our daily backup is causing deadlocks, and I'm hoping someone can shed some light on the intracacies of the mysqldump options that I'm using.

My tables, with the exception of one full-text-indexed table that doesn't change much, use the InnoDB engine.

My backup script is running mysqldump with the following options:

--single-transaction
--master-data=2
--create-options

The "deadlock" I refer to is a condition where the number of connections begins increasing and the "state" of these connections, reported from SHOW PROCESSLIST is "Locked". Connections will continue increasing to the point of hitting max connections, effectively disabling MySQL.

I have noticed that each time this deadlock occurs, a SELECT query, for which MySQL created a temporary table to store the result, was running BEFORE the backup started. Upon the backup starting, the deadlock occurs. The only way to return MySQL to normal operation is to kill the thread running the big SELECT, which happened to have the status "statistics".

Does the creation of a tmp table as the result of a SELECT select statement cause "--single-transaction" to wait for the tmp table to be created?

Please let me know if I can provide more detail. I'm using 5.0.17-standard-log on Mac OS X 10.3.

Thank you in advance for your time and assistance.



Edited 1 time(s). Last edit at 05/24/2006 03:55PM by Justin Dennis.