Deadlock from mysqldump --single-transaction, is it possible?

Question

Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ?

If it is used to backup a live site with many other querys running at the same time.

Extra info:

The mysqldump process could take over few minutes to complete.

The tables are innodb.

EDIT

I am concerned about the deadlocks errors that could happen on the live applications relying on the database during the time where mysqldump is running.

Answer 1

PROBLEM

It is logically possible because --single-transaction gets "thrown under the bus" if any ALTER TABLE commands are launched intermittently during the mysqldump (See this post from mysqlperformanceblog.com).

What happens when a dump faces off against an ALTER TABLE ?

• If session #1 is running the mysqldump on mydb.mytable, an ALTER TABLE from session #2 may pause because it needs to perform a table lock.
• If session #1 is running ALTER TABLE on mydb.mytable, it could produce spurious results from session #2 trying to run mysqldump on that table (Possible empty table [missing data])

It is not so much a deadlock in the traditional sense, but it safe to say that the --single-transaction option can get its loyalty displaced and lose it point-in-time window from the first victimized table and all other tables after it.

SOLUTION

Setup Master/Slave Replication

• Use binlog_format = ROW to have changes as granular as possible
• Perform mysqldumps of the Data from the Slave
  • STOP SLAVE;
  • mysqldump --single-transaction ...
  • START SLAVE:

You should never encounter deadlocks as long as the Slave is used for reads and backups only.

GIVE IT A TRY !!!