15.10 - Deadlock - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

One problem that can occur with two‑phase locking (see “Two-Phase Locking Defined” on page 669) 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. See Teradata Viewpoint User Guide for information about how to use this portlet.

Teradata Database supports deadlock detection and handling at two different levels.

  • AMP-local
  • A LOCAL deadlock is contention between threads within an AMP, localized within one vproc. The AMP‑local deadlock detection software checks for deadlocks at a fixed 30 second interval.

  • Global
  • A GLOBAL deadlock is the contention between threads over two or more different AMPs. Global deadlock detection runs at a user-defined period set by the DBS Control utility using the DeadLockTimeOut parameter. The global deadlock detection software runs by default with a period of four minutes, but there are times when you might want to set it to a shorter interval.

    Use the Viewpoint Lock Logger portlet to detect blocked transactions and global deadlocks. See Teradata Viewpoint User Guide for information about how to use this utility portlet.

    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.

    The global deadlock detector does not detect HUT deadlocks.

    Because the other client utilities all use database locking, the global deadlock detector does know when they become trapped in a deadlock. 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.


    IF the client utility or application is …

    AND …

    THEN …


    .SET RETRY is active

    even though the entire transaction has been rolled back, BTEQ retries only the failed request and any subsequent requests in the transaction, not the entire transaction.

    If this occurs, the updates made by requests in the transaction prior to the failed request are lost, as is the contextual information indicating that a transaction is in progress.

    anything else


    you must code the application to.

    1 Check for error code 2631.

    2 If error code 2631 is detected, then resubmit the entire aborted transaction.

    See Messages for further information about retryable error 2631.

    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.