Handling of Deadlocks | Transaction Processing | VantageCloud Lake - Deadlock - Teradata Vantage

One problem that can occur with two-phase locking (see Two-Phased Locking Defined) is that two requests can 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 must lock table row A before continuing, and the second transaction must lock table row B before continuing, 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 continuing.

This situation differs 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 granted the necessary lock. This is true unless you specify the NO WAIT option in a LOCKING clause that modifies the request, in which case 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 try to enter an update request.

Also, because requests are processing in parallel on multiple AMPs, a global deadlock may 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 tries to place a write lock on object_x on AMP1, transaction_b is blocked. In a similar manner, 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, any deadlocks are logged in the XML lock log table, DBQLXMLLockTbl, and in the main log table, DBQLogTbl.

Deadlocks

Each request in a transaction places its own locks while running and holds those locks until the transaction commits or rolls back.

This can cause 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

You can also use the XMLPLAN output from DBQL logging to investigate deadlock situations. For more information, see Shredding the Lock Plan Information in the XML Lock Log Table, DBQLXMLLockTbl .

When the system detects a deadlock, the system terminates the transaction and 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 the utilities become trapped in a deadlock. To resolve the deadlock, the Lock Manager ends 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 provides a basic solution for the problem.

For more information about retryable error 2631, see Database Messages.