Database Deadlock Avoidance

[Moved here from DeadlockAvoidance]

In theory, applications should respond to DeadLock errors on database operations by retrying the transaction/unit-of-work. In practice, most applications have problems with error processing, and just outright fail.

DeadlockAvoidancePatterns would be valuable, or list mere RulesOfThumb? on this page.

Context: A database scheme that is used for both high-load querying/retrieval of information as well as low/medium load transaction processing.

Commonly, one of the transactions participating in a DatabaseDeadlock will be selected as a "victim," and forcefully rolled back. That client is given an error indication/code/message telling them that they were selected as a victim of a deadlock and that their transaction was aborted.

One would hope that applications, upon receiving the "deadlock victim / your transaction was rolled back" message, that the application would automatically retry the transaction from the top. However, few applications do this.

DB2 2.x has a unique behavior of killing off all 'deadlocked' processes, rolling back all processes that were deadlocked. The application will need to re-establish a connection with the database.

In MnesiaDatabase, you make queries by passing the database a closure to run in a transaction. If the transaction is chosen as a "victim", then it is automatically restarted after a short delay without the application even being aware. This has the advantage of simplicity, but also means you have to avoid side-effects which can't be undone in your transactions.

See: SynchronizationStrategies

CategoryDatabase CategoryConcurrency CategoryConcurrencyPatterns