Locks and Concurrency - Teradata Database - Teradata Vantage NewSQL Engine

The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT operation or within a MERGE request.

Transaction Isolation Level	DBS Control AccessLockForUncomRead Field Setting	Default Locking Severity for Outer SELECT and Ordinary SELECT Subquery Operations	Default Locking Severity for SELECT Operations Embedded Within a MERGE Request
SERIALIZABLE	FALSE	READ	READ
SERIALIZABLE	TRUE		READ
READ UNCOMMITTED	FALSE		READ
READ UNCOMMITTED	TRUE		ACCESS

MERGE requests are also affected by the locking levels set by you or the system. The default locking for MERGE requests is as follows.

Table-level WRITE locks on the target table. For a nonconcurrent isolated merge on a load isolated table, the merge operation sets an EXCLUSIVE lock on the target table.
READ or ACCESS locks on the source table depending on the situation and whether you specify a LOCKING request modifier.

The following cases illustrate the effect of these locking levels.

Case 1

The query plan includes a table-level WRITE lock on target table t1 in steps 1 and 2.

     MERGE INTO t1
     USING (SELECT a2, b2, c2
            FROM t2
            WHERE a2 = 1) AS source (a2, b2, c2)
       ON a1 = a2
     WHEN MATCHED THEN
       UPDATE SET b1 = b2
     WHEN NOT MATCHED THEN
       INSERT (a2, b2, c2);

An EXPLAIN shows a write lock on t1.

Case 2

Locking considerations become critical when you submit MERGE requests in an array-processing environment because those requests can cause transaction deadlocks if they are not coded correctly. For details, see Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418. See also Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.

This case is an example of how improper coding can cause deadlocks. In this case, the same request is repeated once each time through two different sessions as specified by the BTEQ command .REPEAT 2 PACK 100. Because the sessions are running under ANSI transaction semantics, the lock on t1 cannot be released until the COMMIT request is processed successfully.

Suppose there are two sessions, numbers 1025 and 1026. Assume further that session 1026 executes first. Session 1026 places a table-level WRITE lock on target table t1 and completes the execution of the MERGE request. However, session 1026 cannot release the lock on target table t1 until session 1025 completes because its transaction is not committed until both sessions have completed.

Session 1025 cannot complete its transaction because session 1026 has a table-level WRITE lock on t1. This is a classic case of deadlock where both sessions are waiting on one other for the lock to be released, causing both requests to hang.

     .SET SESSION TRANSACTION ANSI 
     .SET SESSIONS 2
     .LOGON nbmps05/ob,ob
     .IMPORT INDICDATA file = ./recind.data
     .REPEAT 2 
     USING (c3 INTEGER)
     MERGE INTO t1
     USING (SELECT a2, b2, c2
            FROM t2
            WHERE a2 = 1) AS source (a2, b2, c2)
       ON a1 = a2 AND c1 =:c3
     WHEN MATCHED THEN
       UPDATE SET b1 = b2
     WHEN NOT MATCHED THEN
       INSERT (a2, b2, c2);
     .REPEAT 20
     COMMIT;

There are two ways to avoid the deadlock that results from this case:

Redesign the application.
Run the existing application in Teradata session mode to avoid the problem with the transaction-terminating COMMIT request.

Locks and Concurrency - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Case 1

Case 2

Related Topics