MySQL Bugs: #21736: add ability to auto-repeat transactions in case of deadlock

Bug #21736	add ability to auto-repeat transactions in case of deadlock
Submitted:	20 Aug 2006 7:22	Modified:	28 Aug 2006 9:32
Reporter:	Stephen Dewey	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any
Tags:	deadlocks, repeat, transactions

Description:
Unless I am very mistaken, it is not currently possible to write a transaction such that it auto-repeats the attempt if MySQL has to roll it back in the case of a deadlock. It would be great to be able to write a transaction such that it repeats a specified number of times before giving up and returning an error message. That would reduce the number of errors given to users on the application level.

How to repeat:
Simultaneously:
Table 1 is locked by Transaction A, which is attempting to access Table 2.
Table 2 is locked by Transaction B, which is attempting to access Table 1.

To solve the problem, MySQL rolls back either Transaction A or B and serves an error message to the relevant client.

What would be nice is if the transaction that was rolled back could be auto-repeated.

Suggested fix:
Allow an auto-repeat parameter somewhere in the START TRANSACTION / BEGIN WORK syntax.

Thank you for a feature request. I think, this kind of action (as any other processing of errors) should be performed by client. We will not introduce any non-standard extentions to transaction processing statements, almost surely.