This example shows an update of a nonpartitioned column with an equality constraint on the partitioning column. With partition level locking, an all-AMP PartitionRange lock is used. The partition list contains 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 UPDATE statement shows the partition lock:
Explain UPDATE HLSDS.SLPPIT1 SET X = 3 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 UPDATE from a single partition of HLSDS.SLPPIT1
with a condition of ("HLSDS.SLPPIT1.PC = 10") with a residual
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.05 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.