Why does Microsoft SQL server ( and possibly other SQL implementations ) default to failure when a deadlock is detected?

Wouldn't it be more useful, at least as a default, for queries to be retried? For the systems I make for small businesses this would seem to be more useful. Even if it was not the default, it would be useful to have at least a "do not fail" option.

Perhaps there is a good reason why this cannot be an option, but I don't see it.

  • I'd say that's the responsibility of the client and not the RDBMS engine. Just retrying often might not the correct action applied in the application at all. – πάντα ῥεῖ Aug 27 '20 at 13:48
  • 1
    Why would it be the responsibility of the client? Shouldn't it be the responsibility of the RDBMS to sort out any concurrency issues that arise, at least in the first instance, or as an option. – George Barwood Aug 27 '20 at 14:45
  • As mentioned, a simple retry might be the wrong action as intended by the application. Deadlocks may come in a way that is dependent of former actions made during the transaction, these could be different when the other blocking transaction is commited. Results may not be what they should be. Also a retry would imply an implicit rollback, such the other transaction can finish in either way. A retry might need to replay the whole action sequence. – πάντα ῥεῖ Aug 27 '20 at 14:51

In general, the database doesn't have enough information to realistically retry the operation. It needs the application to retry.

Imagine you have a banking application transferring $100 from A to B. A initially has $150 and B has $100.

begin transaction
  Query A's balance and acquire a lock
  Application checks that A's balance exceeds the amount being transferred
  Application calculates A's new balance as $50 and performs an update
  Update B's balance to a value of balance + 100

Now imagine that the last update of B deadlocks and the transaction rolls back. But now other transactions have changed A's balance to $25 and B's balance to $225.

If the database simply rolled back and retried the operations the database was aware of that were part of the transaction, there is a good chance that the results would be incorrect. A's balance is no longer be above $100 but the database has no way of telling the application that it needs to re-run its logic. The update of A's balance is no longer be correct because it was the result of a calculation the application did that the database wasn't aware of.

If the database simply re-ran the same statements without involving the application, A would be allowed to transfer $100 when his account didn't have that money, would end up gaining $25 as a result of the transfer, and the system would be left in an inconsistent state where A $125 got created out of thin air. It would be woefully difficult to figure out from looking at the code how that inconsistent state came into being. The application's code would look to be transactionally consistent. There wouldn't even be a deadlock error in the application log that could point someone at the source of the error.

A database engine could look to see whether the transaction was solely composed of a single database call with no opportunity for the client application to involve any additional logic and retry those transactions where the database could determine it was safe to do so. That is not a particularly common thing particularly in the world of large enterprises where companies generally want to put business logic in the middle tier and compose different web service calls in a single transaction. And those single database call transactions are not a particularly large source of deadlocks which get much more likely the more moving pieces transactions have. So it would be a feature that would require a decent amount of work to implement in order to resolve a relatively small source of deadlocks in applications with a relatively unusual architecture that doesn't appeal to the large customers spending big bucks on license fees. It would be hard to justify that cost/ benefit calculation.

Additionally, if you happen to have one of those unusual architectures where every transaction is made up of a single database call, it's relatively easy to add an exception handler that simply retries the transaction in the event of a deadlock. Sure, it's a bit of boilerplate code that you have to add to your stored procedures but it's pretty straightforward code.

  • I don't find this example convincing. Where you say "the database has no way of telling the application that it needs to re-run its logic" the "not enough money" logic needs to be part of the transaction, or consistency cannot be guaranteed. – George Barwood Aug 28 '20 at 4:38
  • @GeorgeBarwood It doesn't threaten consistency because the multiple queries are performed within one transaction and therefore see the same consistent DB state. However, application logic determines which queries should be issued and the DB can't know that. You're right that if the entire transaction is performed in a single query (without external application logic involved), then retry could be performed safely. – amon Aug 28 '20 at 8:22
  • Right, and isn't it normal, even essential, for a transaction to be a single query? – George Barwood Aug 28 '20 at 10:59
  • @GeorgeBarwood So the client can send commands to the database but it can't read any data until after the transaction finishes (because another transaction might make the data wrong)? It's a valid approach, but it's not how transactions actually work in reality. – user253751 Aug 28 '20 at 11:00

Why does Microsoft SQL server ( and possibly other SQL implementations ) default to failure when a deadlock is detected?

What else can it do?

Neither of the competing processes can ever complete because of the "Deadly Embrace" of locking that they've got themselves into.

One of those processes has to "go" or neither of them is ever going anywhere ...

Dealing with the failure is the Application's problem.

  • I don't think this is correct, once the deadlock is broken, the other processes can complete, and then the victim process can proceed ( be retried and can succeed ). – George Barwood Aug 28 '20 at 16:51
  • The whole /point/ of a Deadlock is that it cannot be broken without pre-emptive action by the DBMS itself. Neither process can proceed because they both "have" something that the other wants and "want" something else that the other already has. Both processes would be permanently blocked by one another so /one/ of them is arbitrarily chosen as "victim" and killed off, allowing the other to continue and complete. Take a look at: sqlshack.com/what-is-a-sql-server-deadlock – Phill W. Sep 1 '20 at 7:17
  • 1
    As far as "retrying" goes, just /what/ should the DBMS retry? The entire Transaction is killed, not the individual query. Any decision points in the application logic may well now produce different results, based on the new state of the database after the other process has completed. Simply repeating what was done before, even if the database had some "record" of what it tried to do before, might well be incorrect. That's why SQL Server leaves it up to the Application to handle it. – Phill W. Sep 1 '20 at 7:21
  • I would think the entire transaction would be retried ( having been completely rolled back ). The way I am thinking about it is an over-optimistic optimisation was attempted ( rather than running one job at a time ), but the RDMS should recover from this over-optimism, or not try to be so clever in the first place. Perhaps there are some details which make this problematic. – George Barwood Sep 1 '20 at 19:56
  • @GeorgeBarwood: That would require the database to retain a record of every SQL statement issued in every transaction up until each transaction is committed. Each transaction might be many hundreds of individual SQL statements, every one of which the DBMS would have to "replay". This is simply impractical. Furthermore, the decisions made by the Application to arrive at the SQL statements to run may well be different now that the other transaction has completed, making their blind repetition invalid anyway. – Phill W. Sep 2 '20 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.