Minimizing Deadlock - Advanced SQL Engine - Teradata Database

Minimizing Deadlock by Specifying the LOCKING Request Modifier

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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for details about this request modifier).

Using The LOCKING Modifier To Enhance Concurrency

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 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 ...;

Upgrading Locks Dynamically and Deadlock

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.

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

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.

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:

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;

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.

Using the LOCKING Request Modifier With the NOWAIT Option

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.

Specifying the LOCKING Request Modifier in View Definitions

The LOCKING request modifier can be specified in CREATE VIEW, REPLACE VIEW, CREATE RECURSIVE 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 accidentally impacting transaction processing, and users can modify base table data without impacting any existing report-oriented queries.

Preventing Deadlocks When You Use BTEQ

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.