The following describes the types of table locks.
Intent lock
An intent lock indicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.
Shared lock
This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table 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 table lock.
Exclusive lock
This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.
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.
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.
