Concept of Locking

A lock is an object used to indicate that a user/process has some dependency on a resource. Placing a lock on a resource is called locking.
· Locking is used to ensure transactional integrity and database consistency.
· Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time.
· If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.
· SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases.
· SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query.