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.