Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
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.
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
commit
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.
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.
