All about Locking in SQL 2000

full article coming soon...

Four aspects of locking

• Mode of locking
• Granularity of the lock
• Duration of the lock
• Ownership of the lock

Lock Modes

• Shared Locks
• Exclusive Locks
• Update Locks
• Intent Locks

Lock Granularity

• at the table, page, or row level. SQL Server also locks index keys and ranges of index keys

Lock Duration

• depends on the mode of the lock and the transaction isolation level

Lock Ownership

• Transactions
• Cursors
• Sessions

Other related terms


Few useful system tables for locks

• syslockinfo table

Few useful commands sp for locks

• sp_getapplock
• sp_releaseapplock
• sp_lock