Partition Locks

Partition locks are an attribute of a partitioned table and are applicable to all types of partitioning.

Intent partition lock – indicates that page-level or row-level locks are held on a partition of table. SAP ASE applies an intent partition lock with each shared or exclusive partition lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from acquiring conflicting partition-level locks on the table as the partition locks are in effect for the transaction.
Shared partition lock – similar to a shared page or row lock, except that it affects the entire partition. For example, SAP ASE applies a shared partition lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared partition lock.
Exclusive partition lock – similar to an exclusive page or row lock, except that it affects the entire partition. For example, SAP ASE applies an exclusive partition lock during a create clustered index command. update and delete statements on data-only-locked partitions require exclusive partition locks if their search arguments do not reference indexed columns of the object.
Covering partition lock – these locks are required when DDLs and DMLs do not know the partitionid while acquiring the fine-grained lock.

For information about other types of locks, see Performance and Tuning Series: Locking and Concurrency Control.

This example table shows the respective partition locks of page or row locks SAP ASE uses for basic SQL statements. For these examples, there is an index on acct_number.

Statement	Allpages-Locked Table	Datarows-Locked Table
select balance from account where acct_number = 25	Intent shared partition lock Shared page lock	Intent shared partition lock Shared row lock
insert account values (34, 500)	Intent exclusive partition lock Exclusive page lock on data page Exclusive page lock on leaf index pages	Intent exclusive partition lock Exclusive row lock
delete account where acct_number = 25	Intent exclusive partition lock Update page locks followed by exclusive page locks on data pages and leaf-level index pages	Intent exclusive partition lock Update row locks followed by exclusive row locks on data rows
update account set balance = 0 where acct_number = 25	Intent exclusive partition lock Update page locks followed by exclusive page locks on data pages and leaf-level index pages	Intent exclusive partition lock Update row locks followed by exclusive row locks on data rows.

Statement

Allpages-Locked Table

Datarows-Locked Table

select balance from account where acct_number = 25

Intent shared partition lock

Shared page lock

Intent shared partition lock

Shared row lock

insert account values 
(34, 500)