Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 !!!

share|improve this answer

Your Answer

 
discard

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.