Locking and blocking

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 can become logically incorrect, and queries performed against that data can produce unexpected results. SQL Server enforces locking automatically. Locking can occur at record, page, or table level.

Blocking occurs when one user holds a lock and a second user requires a conflicting lock type. This forces the second user to wait, blocked by the first. Typically, the second user sees an hourglass while trying to process or save records. Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes. A design goal is to minimize the amount of time a record is locked to reduce the potential blocking of another user.

A deadlock arises when two processes have data locked, and each process cannot release its lock until the other process has released its lock. SQL Server rolls back one of the transactions and then allows the other transaction to continue.