Microsoft® SQL Server™ 2000 Unleashed, Second Edition

SQL Server Lock Types

Locking is handled automatically within SQL Server. The Lock Manager chooses the type of locks based on the type of transaction (such as select, insert, update, and delete). The various types of locks used by Lock Manager are as follows:

• Shared

• Update

• Exclusive

• Intent

• Schema Locks

• Bulk Update Locks

As in version 7.0, the Lock Manager in SQL Server 2000 automatically adjusts the granularity of the locks (row, page, table, and so on) based on the nature of the statement that is executed and the number of rows that are affected.

Shared Locks

SQL Server uses shared locks for all read operations. A shared lock is, by definition, not exclusive. Theoretically, an unlimited number of shared locks can be held on a resource at any given ...