15.10 - Minimizing Deadlock - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

For particular types of transactions, or for very large or urgent applications, you can reduce or prevent the chance of a deadlock by specifying the LOCKING request modifier with your SQL DML requests (see “LOCKING Request Modifier” in SQL Data Manipulation Language for details about this request modifier).

You can use the LOCKING request modifier to improve performance and reduce conflicts in the following ways.

  • Using the NOWAIT option to abort a transaction if a lock cannot be granted immediately.
  • Using the LOCKING ROW FOR WRITE syntax to reduce the chance of a deadlock during an update when multiple transactions select and then update the same row.
  • Applying a higher or lower severity of lock than that normally applied.
  • To make more efficient use of system resources, you can use the LOCKING request modifier in a transaction to decrease or increase the restrictiveness of locks that would otherwise be placed by the system during the processing of transaction requests.

    The following list contains some important facts about the LOCKING request modifier.

  • The LOCKING request modifier is not ANSI SQL-2008-compliant.
  • The LOCKING request modifier can precede any SQL request or it can be used alone, without modifying an SQL request. The LOCKING clause is typically used as a modifier, in which case it precedes the request. For example, the following request is valid.
  •    LOCKING TABLE tablename FOR READ;
  • More than one LOCKING request modifier can be specified in the same request.
  • The CREATE VIEW, REPLACE VIEW, CREATE RECURSIVE VIEW, and REPLACE RECURSIVE VIEW statements allow the LOCKING request modifier to be specified as part of the view definition.
  • You can use the LOCKING request modifier to specify the mode of lock to be placed on a database, table, or rowhash before a request is processed. You can specify LOCKING with the NOWAIT option to abort a transaction if a lock cannot be granted immediately.
  • The LOCKING request modifier cannot prevent a lock of a higher mode from being imposed, and it does not affect objects that are already locked. You can precede your request with EXPLAIN to see the locks that will be set as each request is executed. Note that locks for rowhash operations are not documented by EXPLAIN reports.
  • If you have load-isolated tables, you can use the LOAD COMMITTED locking modifier to read committed rows without being blocked and without blocking the concurrent isolated modifications. For more information about load-isolated tables, see “Load Isolation” on page 676.
  • The following example uses the LOCKING request modifier to maximize concurrency.

         LOCKING TABLE table_a FOR READ
         LOCKING TABLE table_b FOR READ
         SELECT ...
         FROM table_a, table_b
         WHERE ...;
         LOCKING TABLE table_name FOR WRITE
         ;	
     
         UPDATE ...;

    When the Optimizer decides to use a primary or unique secondary index to process a SELECT request, the system applies a READ lock on the row hash value.

    If the same transaction contains a subsequent DML request based on the same index value, the system upgrades the READ lock to a WRITE or EXCLUSIVE lock.

    If concurrent transactions simultaneously require this type of upgrade on the same row hash value, a deadlock can result.

    For example, assume that two concurrent transactions use the same primary index value to perform a SELECT request followed by an UPDATE request, as follows. This example assumes the user is operating in Teradata session mode.

     

           User

                                                                         SQL Text

              A

    BEGIN TRANSACTION;
       SELECT y 
       FROM table_a 
       WHERE pi = 1;
     
       UPDATE table_a 
       SET y = 0 
       WHERE pi = 1;
    END TRANSACTION;

              B

    BEGIN TRANSACTION;
       SELECT z 
       FROM table_a 
       WHERE pi = 1;
     
       UPDATE table_a 
       SET z = 0 
       WHERE pi = 1;
    END TRANSACTION;

    In this case, user_a and user_b are allowed to access the rows sharing the same row hash value simultaneously for READ during SELECT processing.

    When the user_a UPDATE request requires a WRITE lock on the table_a row hash value, the upgrade request is queued, waiting for the user_b READ lock on table_a to be released.

    However, the user_b READ lock cannot be released because the user_b UPDATE request also requires a WRITE lock on the row hash value, and that request is queued waiting for the user_a Read lock to be released.

    You can avoid such deadlocks by preceding the transaction with a LOCKING ROW FOR WRITE or LOCKING ROW FOR EXCLUSIVE phrase as appropriate.

    Note: This phrase does not override any locks already held on the target table.

    LOCKING ROW is appropriate only for single‑table select requests that use a primary index or unique secondary index constraint, as shown in the following example.

     

           User

                                                                        SQL Text

              A

    BEGIN TRANSACTION;                                                    
       LOCKING ROW FOR WRITE
       SELECT y 
       FROM table_a
       WHERE USI = 1;
     
       UPDATE table_a
       SET y = 0 
       WHERE USI = 1;
    END TRANSACTION;

              B

    BEGIN TRANSACTION;                                                    
       LOCKING ROW FOR WRITE
       SELECT z 
       FROM table_a
       WHERE USI = 1;
     
       UPDATE table_a
       SET z = 0
       WHERE USI = 1;
    END TRANSACTION;

    In this example, the user_a request for a rowhash‑level WRITE lock is granted, which blocks the user_b request for a WRITE lock on that row hash value. The user_b transaction is queued until the user_a lock is released.

    The user_a lock is held until the entire transaction is complete. Thus, the user_b LOCKING ROW FOR … request is granted only after the user_a END TRANSACTION request has been processed.

    If your request cannot wait in a lock queue, you can specify the LOCKING request modifier with the NOWAIT option.

    NOWAIT specifies that the entire transaction, even in ANSI session mode, is to be aborted if the system cannot place the necessary lock on the target object immediately upon receipt of a request.

    This situation is treated as a failure. The user is informed that the transaction was aborted, and any processing performed up to the point at which NOWAIT took effect is rolled back.

    The LOCKING request modifier can be specified in CREATE VIEW, REPLACE VIEW, CREATE RECUSRIVE VIEW, and REPLACE RECURSIVE VIEW definitions. For example, the view can downgrade READ locks to ACCESS locks. Thus, the ad hoc user need not worry about specifying a locking request modifier and accidently impacting transaction processing, and users can modify base table data without impacting any existing report-oriented queries.

    If you use BTEQ to submit a transaction, the database reports the deadlock abort to BTEQ. BTEQ resubmits only the request that caused the failure (the default behavior), not the complete transaction. Because this can result in partially committed transactions, you must take care when writing a BTEQ script to ensure that the transaction is one request. For example, a statement in BTEQ ends with a semicolon (;) as the last non-blank character in the line. Thus, BTEQ sees the following example as two requests:

         sel * from x;
         sel * from y;

    However, if you write these same statements in the following way, BTEQ sees them as only one request:

         sel * from x
         ; sel * from y;

    Suppose you use BEGIN TRANSACTION and submit separate requests, as shown by the following:

    BEGIN TRANSACTION
    INSERT x ( 1, 2 ) ;
    INSERT x ( 3, 4 ) ;
    INSERT x ( 5, 6 ) ;

    If deadlock occurs on the third insert, the transaction is rolled back and, if retry is enabled for BTEQ, the third insert is resubmitted as an implicit transaction. If the third insert is then followed by END TRANSACTION, a failure occurs, but the third insert was already committed.