MySQL Bugs: #8325: Deadlock in replication thread stops replication

Bug #8325	Deadlock in replication thread stops replication
Submitted:	4 Feb 2005 19:35	Modified:	23 Mar 2005 19:20
Reporter:	Chris Tucker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.22	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
If the replication thread hits a deadlock condition and is killed by the InnoDB deadlock detector, replication halts and must be manually restarted.  This will retry the deadlocked transaction, and replication will then continue normally.  Rather than forcing a manual restart, the database should automatically retry the transaction (by restarting the slave): this is the theoretically correct fix for a deadlock, and is the practical effect of executing SLAVE START in this situation.

How to repeat:
Tricky to repeat reliably as with any deadlock situation.  May be possible to design queries to increase likelihood of a deadlock in the replication thread by running many insert/updates on master and complex, long-running selects on slave (the instance it occurred in for me most recently involved a select currently holding >50 locks and a rapidly updating/inserting master across the same tables).

Suggested fix:
When the slave is stopped with errno 1213 (ER_LOCK_DEADLOCK), attempt to restart the slave SQL thread automatically.

Chris,

I had already decided to change 4.1.10 or 4.1.11 so that the replication thread is never chosen as the deadlock victim, and also, that the lock wait timeout is raised for the replication thread.

If the problem comes from:

INSERT INTO t SELECT ... FROM s;

where s is a replicated table, then an upcoming change to the option innodb_locks_unsafe_for_binlog in 4.1 will also help. We intend to make the read of s a consistent, non-locking read if that option is set.

Regards,

Heikki

Hi Heikki,

I'm glad to hear this is a problem that is being addressed.  However, I'm not sure always choosing the non-replication thread as the victim is necessarily a good thing to do: in my situation, particularly, I may have a read transaction that has been open for 30 minutes, while the replication transaction is typically under a second.  In that case, I would much prefer the replication transaction be chosen as the victim, and then retried automatically (via a SLAVE START) than have to redo the expensive read queries.  As my read transactions get longer (perhaps to the order of a couple of hours or so), there's a growing risk of them never completing successfully if they're always chosen as the deadlock victim, rather than using a who-has-done-the-most-work metric of some kind.

Regards,
Chris

Heikki, I'm stealing this bug from you as agreed.
Chris, I'm preparing a fix which will treat slave thread as a normal thread and make it retry transaction if it gets rolled back by deadlock or lock timeout.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 4.1.11 and 5.0.3, by introducing the SLAVE_TRANSACTION_RETRIES option (will be documented in the next days); the slave shall be started with --slave-transaction-retries=x where x>0 and then it will retry a failed transaction for x times before giving up.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23323

Sorry, the previous message "a patch has been committed" was created by an automated robot; all I did today is making small modifications to the patch I had already committed; i.e. nothing new. The feature --slave_transaction_retries appears in 4.1.11 and 5.0.3.