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:
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.
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 ...