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.

1 Answer 1



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.


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.


  • Thanks for your solution. Does mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table only happen with ALTER TABLE or all DDL statments? I see thousends of CREATE TABLE IF NOT EXISTS in audit log.
    – Sybil
    Commented Apr 25, 2017 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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