Table locks

The following describes the types of table locks.

The examples in Table 2-3 show the respective page, row, and table locks of page or row locks Adaptive Server uses for basic SQL statements. For these examples, there is an index acct_num.

Table 2-3: Table locks applied during query processing

Statement

Allpages-Locked Table

Datarows-Locked Table

select balance from account where acct_number = 25

Intent shared table lock Shared page lock

Intent shared table lock Shared row lock

insert account values 
(34, 500)

Intent exclusive table lock Exclusive page lock on data page Exclusive page lock on leaf index pages

Intent exclusive table lock Exclusive row lock

delete account 
where acct_number = 25

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

Intent exclusive table lock Update row locks followed by exclusive row locks on data rows

update account
set balance = 0 
where acct_number = 25

With an index on acct_number, intent exclusive table lock Update page locks followed by exclusive page locks on data pages and leaf-level index pages

With no index, exclusive table lock

With an index on acct_number, intent exclusive table lock Update row locks followed by exclusive row locks on data rows

With no index, exclusive table lock

Exclusive table locks are also applied to tables during select into operations, including temporary tables created with tempdb..tablename syntax. Tables created with #tablename are restricted to the sole use of the process that created them, and are not locked.