Common Lock Design

Partitioned tables use table locks and partition locks. Eight common locks of different levels are applied to tables and partitions to ensure proper behavior control during concurrent DQL, DML, and DDL operations. The following table lists the lock conflicts at different levels. Every two types of common locks marked with √ do not block each other and can be executed concurrently.

**Table 1** Common lock conflict description
-	ACCESS_SHARE	ROW_SHARE	ROW_EXCLUSIVE	SHARE_UPDATE_EXCLUSIVE	SHARE	SHARE_ROW_EXCLUSIVE	EXCLUSIVE	ACCESS_EXCLUSIVE
ACCESS_SHARE	√	√	√	√	√	√	√	×
ROW_SHARE	√	√	√	√	√	√	×	×
ROW_EXCLUSIVE	√	√	√	√	×	×	×	×
SHARE_UPDATE_EXCLUSIVE	√	√	√	×	×	×	×	×
SHARE	√	√	×	×	√	×	×	×
SHARE_ROW_EXCLUSIVE	√	√	×	×	×	×	×	×
EXCLUSIVE	√	×	×	×	×	×	×	×
ACCESS_EXCLUSIVE	×	×	×	×	×	×	×	×

Different statements of a partitioned table are applied to the same target partition. The database applies different levels of table locks and partition locks to the target partitioned table and partition to control the concurrency behavior. Table 2 specifies the lock control level of different partitioned table statements. The numbers 1 to 8 indicate the eight levels of common locks specified by Table 1, which are ACCESS_SHARE, ROW_SHARE, ROW_EXCLUSIVE, SHARE_UPDATE_EXCLUSIVE, SHARE, SHARE_ROW_EXCLUSIVE, EXCLUSIVE, and ACCESS_EXCLUSIVE, respectively.

**Table 2** Lock control level of different partitioned table statements
Statement	Partitioned Table Lock (Table Lock+Partition Lock)
SELECT	1-1
SELECT FOR UPDATE	2-2
DML statements, including INSERT, UPDATE, DELETE, UPSERT, MERGE INTO, and COPY	3-3
Most partition-level DDL statements, including ADD, DROP, EXCHANGE, TRUNCATE, MOVE and RENAME	4-8
CREATE INDEX (non-sparsely-partitioned index) and REBUILD INDEX	5-5
CREATE INDEX (sparsely partitioned index)	3-5
REBUILD INDEX PARTITION	1-5
ANALYZE and VACUUM	4-4
Other DDL statements on partitioned tables, including partition-level SPLIT and MERGE	8-8

If both the table lock and partition lock applied during service execution meet the lock control requirements specified in Table 1, concurrent service operations are supported. If either of the table lock and partition lock is not supported, concurrent service operations are not allowed.

The table-level lock applied by the DDL operations (ADD/DROP/TRUNCATE/EXCHANGE/MOVE/RENAME) to a partitioned table is controlled by the GUC parameter enable_partition_ddl_lowlevel_lock. If enable_partition_ddl_lowlevel_lock is set to on, a level-4 lock is applied to the table; if it is set to off, a level-8 lock is applied.

Parent topic: Partition Concurrency Control

Previous topic: Partition Concurrency Control

Next topic: DQL/DML-DQL/DML Concurrency