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 in parallel.

**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)	Level-2 Partitioned Table Lock (Table Lock + Partition Lock + Level-2 Partition Lock)
SELECT	1-1	1-1-1
SELECT FOR UPDATE	2-2	2-2-2
DML statements, including INSERT, UPDATE, DELETE, UPSERT, MERGE INTO, and COPY	3-3	3-3-3
Partition-level DDL statements, including ADD, DROP, EXCHANGE, TRUNCATE, SPLIT, MERGE, MOVE and RENAME; enabling/disabling automatic partitioning	4-8	4-8-8 (used for partitions in a level-2 partitioned table) 4-4-8 (used for level-2 partitions in a level-2 partitioned table)
CREATE INDEX (non-sparsely-partitioned index) and REBUILD INDEX	5-5	5-5-5
CREATE INDEX (sparsely partitioned index)	3-5	3-3-5
REBUILD INDEX PARTITION	1-5	1-5-5
ANALYZE and VACUUM	4-4	4-4-4
Other partitioned table-level DDL statements	8-8	8-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.

Parent topic: Partition Concurrency Control

Previous topic: Partition Concurrency Control

Next topic: DQL/DML-DQL/DML Concurrency