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 mutually exclusive behavior of locks 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 behavior
-	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. The following table lists the lock control level for different statements. Numbers 1 to 8 indicate the eight common locks listed in the preceding table.

**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