Table locks

This section describes the types of table locks.

• Intent lock – indicates that page-level or row-level locks are 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 acquiring conflicting table-level locks on the table containing the locked page. An intent lock is held as long as page or row locks are in effect for the transaction.

• Shared lock – similar to a shared page or row 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 – similar to an exclusive page or row lock, except that it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements on data-only-locked tables require exclusive table locks if their search arguments do not reference indexed columns of the object.

select balance from account where acct_number = 25

insert account values 
(34, 500)

delete account 
where acct_number = 25

update account
set balance = 0 
where acct_number = 25

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.