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