Locking in the Database Engine (Locks)

Locks

Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

Transactions must comply ACID (Atomicity Consistency Isolation Durability) rule.

Types of Concurrency Control

When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.

Classifications:

• Pessimistic concurrency control: info gets locked to avoid conflicts.
• Optimistic concurrency control: No locks, just rollbacks when required.

These options are configured though transaction isolation levels for connections or concurrency options on cursors.

Lock modes

Locking hierarchy

Hierarchy (up to down): database - table - page - row

Also check the official Lock Compatibility Matrix

Recommended readings

• Locking in the Database Engine in MS SQL Server documentation.