17.05 - Example: INSERT Operation Using Single-Writer Lock - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

This INSERT operation uses a single-writer lock. The single-writer lock is added for bulk DML operations that require index maintenance on a partition-locked table. The lock prevents concurrent write operations on the table, while allowing concurrent read operations on the partitions that are not locked.

The table definitions for this example are as follows:

  CREATE TABLE HLSDS.SLPPIT3 (PI INT, PC INT, USI INT, X INT, Y INT)
    PRIMARY INDEX (PI)
    PARTITION BY 
         (RANGE_N(PC BETWEEN 1 AND 1000 EACH 1, NO RANGE, UNKNOWN))
    UNIQUE INDEX (USI);
  CREATE TABLE HLSDS.SRCT3 (A INT, B INT, C INT, D INT, E INT);

An EXPLAIN of the INSERT operation shows the condition PC=100 as WHERE B=100:

  Explain INSERT HLSDS.SLPPIT3 SELECT * FROM HLSDS.SRCT3 WHERE B = 100;
  1) First, we lock HLSDS.srct3 for read on a reserved RowHash 
      to prevent global deadlock.
  2) Next, we lock HLSDS.slppit3 for single writer on a 
      reserved RowHash in all partitions to serialize concurrent 
      updates on the partition-locked table to 
      prevent global deadlock.
  3) We lock HLSDS.slppit3 for write on a reserved RowHash 
      in a single partition to prevent global deadlock.
  4) We lock HLSDS.srct3 for read, we lock HLSDS.slppit3 for single
      writer to serialize concurrent updates on the partition-locked
      table,   and we lock HLSDS.slppit3 for write on a single partition.
  5) We do an all-AMPs RETRIEVE step from HLSDS.srct3 by way of an
      all-rows scan with a condition of ("HLSDS.srct3.b = 100") into
      Spool 1 (all_amps), which is built locally on the AMPs.  
      Then we do a SORT to partition Spool 1 by rowkey.
      The size of Spool 1 is estimated 
      with no confidence to be 1 row (33 bytes).
      The estimated time for this step is 0.03 seconds.
  6) We do an all-AMPs MERGE into HLSDS.slppit3 from Spool 1
      (Last Use). The size is estimated with 
      no confidence to be 1 row.  
      The estimated time for this step is 1.73 seconds.
  7) Finally, we send out an END TRANSACTION step to all AMPs 
      involved in processing the request.