Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORM Support for Handling Deadlocks

Tags:

orm

deadlock

Do you know of any ORM tool that offers deadlock recovery? I know deadlocks are a bad thing but sometimes any system will suffer from it given the right amount of load. In Sql Server, the deadlock message says "Rerun the transaction" so I would suspect that rerunning a deadlock statement is a desirable feature on ORM's.

like image 467
Otávio Décio Avatar asked Feb 27 '09 15:02

Otávio Décio


People also ask

How can we avoid deadlock in SSIS package?

To work around this problem, change the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement to SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.

How can we prevent deadlock problem in SQL Server?

Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.


1 Answers

I don't know of any special ORM tool support for automatically rerunning transactions that failed because of deadlocks. However I don't think that a ORM makes dealing with locking/deadlocking issues very different. Firstly, you should analyze the root cause for your deadlocks, then redesign your transactions and queries in a way that deadlocks are avoided or at least reduced. There are lots of options for improvement, like choosing the right isolation level for (parts) of your transactions, using lock hints etc. This depends much more on your database system then on your ORM. Of course it helps if your ORM allows you to use stored procedures for some fine-tuned command etc.

If this doesn't help to avoid deadlocks completely, or you don't have the time to implement and test the real fix now, of course you could simply place a try/catch around your save/commit/persist or whatever call, check catched exceptions if they indicate that the failed transaction is a "deadlock victim", and then simply recall save/commit/persist after a few seconds sleeping. Waiting a few seconds is a good idea since deadlocks are often an indication that there is a temporary peak of transactions competing for the same resources, and rerunning the same transaction quickly again and again would probably make things even worse.

For the same reason you probably would wont to make sure that you only try once to rerun the same transaction.

In a real world scenario we once implemented this kind of workaround, and about 80% of the "deadlock victims" succeeded on the second go. But I strongly recommend to digg deeper to fix the actual reason for the deadlocking, because these problems usually increase exponentially with the number of users. Hope that helps.

like image 191
realMarkusSchmidt Avatar answered Oct 05 '22 03:10

realMarkusSchmidt