Example: Insert Operation Using Single-Writer Lock - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Example: Insert Operation Using Single-Writer Lock

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.