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.