MERGE Locks and Concurrency - Teradata Vantage

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.

The following cases show 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 not coded correctly. 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 Deadlock.

This case is an example of how improper coding can cause deadlocks. 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. Session 1026 runs 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 stop.

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