Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


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.

share|improve this question
up vote 1 down vote accepted


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

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.


Setup Master/Slave Replication

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

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


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.