Handling of Deadlocks | Transaction Processing | Teradata Vantage - Deadlock - Advanced SQL Engine - Teradata Database

One problem that can occur with two-phase locking (see Two-Phased Locking Defined) is a situation in which two requests each lock a different database resource that the other request needs to continue processing its transaction. For example, consider the situation where the first transaction has locked table row B and the second transaction has locked table row A. If the first transaction now needs to lock table row A before it can continue, and the second transaction needs to lock table row B before it can continue, the two transactions have created a deadlock. The transactions that are neutralized by this outcome are in a simultaneous wait state, as each transaction waits for the other transaction to release a locked resource before it can continue.

This is a different situation than the case in which individual requests are waiting for a lock to be released. Note that Teradata lock requests do not time out, so a request waits in the lock queue until it is granted the lock it needs. This is true unless you specify the NO WAIT option in a LOCKING clause that modifies the request, in which case it aborts immediately and rolls back any updates made by the containing transaction.

For example, a local deadlock occurs when two transactions that concurrently hold READ locks for the same data on the same AMP attempt to enter an update request.

Also, because requests are processing in parallel on multiple AMPs, it is possible for a global deadlock to occur.

For example, suppose transaction_a places a WRITE lock on object_x (for example the rows sharing the same hash value of a table) on AMP1, and transaction_b places a WRITE lock on object_y on AMP2. Both of these lock requests are granted.

Now, if transaction_b attempts to place a write lock on object_x on AMP1, it is blocked. In a similar manner, if transaction_a places a WRITE lock on object_y on AMP2, it is also blocked. Neither transaction can complete.

If the DBQL Lock Logger is enabled, then whenever deadlocks occur, they are logged in the XML lock log table, DBQLXMLLockTbl, and in the main log table, DBQLogTbl. You can use the Viewpoint DBQL Lock Logger portlet to report deadlocks whenever they occur. For information about how to use this portlet, see Teradata® Viewpoint User Guide, B035-2206.

About Deadlocks

Each request in a transaction places its own locks as it runs and continues to hold the locks it acquires until the transaction either commits or rolls back.

The disadvantage of this is that it can result in deadlocks when multiple concurrent accesses against the same database objects occur. The application-level solution to this potential problem is to apply all the explicit database- and table-level locks you need in a single request at the beginning of a transaction.

Deadlock Detection

Use the Viewpoint Lock Logger portlet to detect blocked transactions and global deadlocks. For information about how to use this utility portlet, see Teradata® Viewpoint User Guide, B035-2206.

You can also use the XMLPLAN output from DBQL logging to investigate deadlock situations. For more information, see Database Administration.

When the system detects a deadlock, it terminates the transaction and then rolls back the most recently initiated transaction of the two.

Detecting and Handling Deadlock for Client Utility Locks

Because most client utilities use database locking, the global deadlock detector knows when they become trapped in a deadlock. (The global deadlock detector does not detect HUT deadlocks.) To resolve the deadlock, the Lock Manager aborts the deadlocked transaction, rolls back its updates, and returns a retryable 2631 error to the requesting application or utility.

The following table summarizes what happens to a transaction submitted from a client utility or application when a retryable 2631 error occurs, and then provides a very basic solution for the problem:

For more information about retryable error 2631, see Teradata Vantage™ - Database Messages, B035-1096.