This is an example of deleting rows with an equality constraint on partitioning column. With partition level locking, an all-AMPs partition range lock is placed. The partition range has a single partition pair.
The table definition for this example is as follows:
CREATE TABLE HLSDS.SLPPIT1 (PI INT, PC INT, X INT, Y INT) PRIMARY INDEX (PI) PARTITION BY (RANGE_N(PC BETWEEN 1 AND 10 EACH 1))
An EXPLAIN of the DELETE statement shows the partition lock:
Explain DELETE HLSDS.SLPPIT1 WHERE PC = 10; 1) First, we lock HLSDS.SLPPIT1 for write on a reserved rowHash in a single partition to prevent global deadlock. 2) Next, we lock HLSDS.SLPPIT1 for write on a single partition. 3) We do an all-AMPs DELETE of a single partition from HLSDS.SLPPIT1 with a condition of ("HLSDS.SLPPIT1.PC = 10"). The size is estimated with no confidence to be 1 row. The estimated time for this step is 0.03 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.