CODEWITHSUNDEEP
mysqltransactionsdeadlock
Ravi Sankar Raju
Ravi Sankar Raju

Reputation: 2950

Restarting transaction in MySQL after deadlock

I am facing a deadlock in my MySQL. How can I configure MySQL to restart the transaction automatically when it encounters a deadlock?

Upvotes: 10

Views: 13936

Answers (4)

Daniel Schneller
Daniel Schneller

Reputation: 13926

You cannot do that automatically, as the deadlock occurred, because two transactions were trying to modify the same data. If you know that simply retrying the same statements again will be the right thing to do, you need to implement this in your application.

However having the database do that automatically would be irresponsible, because there might just as well be cases where your application first needs to take a look at the new situation in the database, before issuing potentially modified statements, if any at all.

Upvotes: 7

oxygen
oxygen

Reputation: 6039

Restarting a transaction means:

  • (Optionally) initiate a new database connection handle;
  • Execute the first function/line code which initiates a new transaction and retries the entire execution path until commit.

Because the database engine cannot possibly know the queries which would have been executed after the deadlock occured, it cannot retry the whole thing for you (not to mention there could be application logic which would maybe execute different queries based on newly changed database data).

Upvotes: 3

jeha
jeha

Reputation: 10710

Well, you can't and it would not make sense. It's up to your application to cope with deadlock and how it's done depends very much on your business logic, e.g. catch the exception, wait some seconds, retry x times ...

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html

The manual suggests that no such configuration option exists.

Upvotes: 6

Related Questions