Page and row locks

The following describes the types of page and row locks:

In general, read operations acquire shared locks, and write operations acquire exclusive locks. For operations that delete or update data, Adaptive Server applies page-level or row-level exclusive and update locks only if the column used in the search argument is part of an index. If no index exists on any of the search arguments, Adaptive Server must acquire a table-level lock.

The examples in Table 2-2 show what kind of page or row locks Adaptive Server uses for basic SQL statements. For these examples, there is an index acct_number, but no index on balance.

Table 2-2: Page locks and row locks

Statement

Allpages-Locked Table

Datarows-Locked Table

select balance 
from account 
where acct_number = 25 

Shared page lock

Shared row lock

insert account values
(34, 500)

Exclusive page lock on data page and exclusive page lock on leaf-level index page

Exclusive row lock

delete account 
where acct_number = 25

Update page locks followed by exclusive page locks on data pages and exclusive page locks on leaf-level index pages

Update row locks followed by exclusive row locks on each affected row

update account 
set balance = 0 
where acct_number = 25

Update page lock on data page and exclusive page lock on data page

Update row lock followed by exclusive row lock