Microsoft® SQL Server 2008 R2 Unleashed

Shared Locks

By default, 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 time. In addition, shared locks are unique in that, by default, a process locks a resource only for the duration of the read on the resource (row, page, or table). For example, the query SELECT * from authors locks the first row in the authors table when the query starts. After the first row is read, the lock on that row is released, and a lock on the second row is acquired. After the second row is read, its lock is released, and a lock on the third row is acquired, and so on. In this fashion, a SELECT query allows other data rows ...