Handling of Deadlocks | Transaction Processing | Teradata Vantage - Deadlock - Analytics Database - Teradata Vantage

One problem that can occur with two-phase locking (see Two-Phased Locking Defined) is that two requests lock a different database resource that the other request needs to continue processing its transaction. For example, the first transaction may lock table row B and the second transaction may lock table row A. If the first transaction now needs to lock table row A to continue, and the second transaction needs to lock table row B to continue, the transactions have created a deadlock. Each transaction waits for the other transaction to release a locked resource before continuing.

This situation is different from the case where individual requests are waiting for a lock to be released. Database lock requests do not time out, so a request waits in the lock queue until being granted the necessary lock, unless you specify the NO WAIT option in a LOCKING clause that modifies the request. If you do that, the request ends 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, a global deadlock can 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, transaction_b is blocked. Similarly, if transaction_a places a WRITE lock on object_y on AMP2, transaction_a is also blocked. Neither transaction can complete.

If the DBQL Lock Logger is enabled, deadlocks are logged in the XML lock log table, DBQLXMLLockTbl, and main log table, DBQLogTbl. You can use the Viewpoint DBQL Lock Logger portlet to report deadlocks. See Teradata® Viewpoint User Guide, B035-2206.

Deadlocks

Each request in a transaction places its own locks while running and continues to hold the locks until the transaction either commits or rolls back.

This 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

Vantage supports deadlock detection and handling at the following levels:

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 Teradata Vantage™ - Database Administration, B035-1093.

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

Detecting and Handling Deadlock for Client Utility Locks

Because most client utilities use database locking, the global deadlock detector knows when client utilities 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 basic solution for the problem.

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