Example: INSERT Operation Using Single-Writer Lock - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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;

Result:

  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.