Deadlocks

A deadlock occurs when two or more database tasks permanently block each other by maintaining a lock on a resource that the other tasks are attempting to lock. An example of this is when each task has obtained a lock on data that the other task needs to complete it work.

  • Transaction 1 has a lock on the CustTable and wants a lock on the VendTable but is blocked by Transaction 2.

  • Transaction 2 has a lock on the VendTable and wants a lock on the CustTable but is blocked by Transaction 1.

Transaction 1 is waiting for Transaction 2 to complete but it has a lock on the resources that Transaction 2 needs to complete and Transaction 2 is waiting for Transaction 1 to complete but it has a lock on the resources that Transaction 1 needs to complete creating a circular dependency.

Each database engine monitors for deadlocked transactions and follows its own rules for handling deadlocks. Typically the database engine resolves the deadlock by selecting one of the transactions as a deadlock victim, terminating that transaction based on certain rules and returning an error. This allows the successful transaction to complete.

Use the following coding best practices to minimize the occurrence of deadlocks:

  • Access server objects in the same order each time. If two separate database tasks always request access to the CustTable first and then the VendTable, a task may be temporarily blocked but is less likely to be deadlocked. Requesting access to the CustTable and then the VendTable in one task and requesting access to the VendTable and then the CustTable in another is likely to lead to a deadlock.

  • Ensure that the database design is normalized.

  • Reduce lock time by grabbing locks at the latest possible time and releasing locks as early as possible.

  • Don't allow user input during a transaction. Collect all user input before a database transaction begins to avoid blocking other transactions indefinitely. If you must get user input during a transaction, implement a time out facility in your code so that the blocking transaction is either rolled back or committed.

  • Keep transactions as short as possible.

Deadlocks cannot always be avoided so be sure to put database transaction code within a try/catch block. You can then test for a deadlock exception and retry the operation. For more information about handling deadlock exceptions, see X++ standards: try/catch Statements

Community Additions

ADD
Show: