Locking is a necessary part of transaction processing when working in a multi-user Online Transaction Processing (OLTP) environment. You use locks to prevent update conflicts. While one user is updating data, locks prevent other users from accessing the data being updated. Locks help to prevent:

• Lost updates: Occurs when two transactions update the same data at the same time. Changes are saved for the last transaction that writes to the database, overwriting the other transaction’s changes.
• Dirty reads: Occurs when a transaction reads another transaction’s uncommitted data. This can lead to making inaccurate changes to the data. This is also known as an uncommitted dependency.
• Nonrepeatable reads: Row data changes between reads. This is also referred to as inconsistent analysis.
• Phantoms: A record appears when a transaction reads back through data after making a change.

You can serialize transactions with locks, meaning that only one person can change a data element, such as a specific row, at one time. SQL Server can issue a lock for:

• A row identifier (RID), locking a single row in a table.
• A key, which is a row lock within an index.
• A table, which locks all data rows and indexes.
• A database, which is used when restoring a database.
• A page, locking an 8-KB data or index page.
• An extent, locking a contiguous group of pages during space allocation.

SQL Server selects a locking level appropriate to the current data manipulation or definition action. For example, SQL Server uses a row lock when updating a single data row in a table. SQL Server uses dynamic lock management, which means that the locking level can be adjusted automatically as needed. You can use the sys.dm_tran_locks dynamic management view to obtain information about active locks.

Basic locks

SQL Server supports the following types of locks. Shared and exclusive locks are the basic locks supported by SQL Server.

• Shared locks (S): Used when performing read-only operations against database. Resources locked with a shared lock are available for SELECT, but not for modification.
• Exclusive locks (X): Used for operations that modifies data. INSERT, UPDATE, and DELETE require exclusive locks. No more than one transaction can have an exclusive lock on a resource. If an exclusive lock exists on a resource, no other transaction can access that resource.
• Intent lock: Establishes a locking hierarchy. For example, if a transaction has an exclusive lock on a row, SQL Server places an intent lock on the table. When another transaction requests a lock on a row in the table, SQL Server knows to check the rows to see if they have locks. If a table does not have intent lock, it can issue the requested lock without checking each row for a lock.
• Update lock (U): Placed on a page before performing an update. When SQL Server is ready to update the page, the lock will be promoted to an exclusive page lock.
• Schema lock: Used to prevent a table or index that is being used by another session from being dropped or its schema being modified. When a resource is locked with a schema lock, the object cannot be accessed.
• Bulk update locks (BU): Used to prevent other processes from accessing a table while a bulk load procedure is being processed. It will, however, allow processing of concurrent bulk load processes, which allows you to run parallel loads. A bulk load procedure is one performed by using bulk copy program (bcp) or BULK INSERT.

Optimistic and pessimistic locking

Two terms are commonly used to describe locking methods:

• Pessimistic locking locks resources as they are acquired, holding the locks for the duration of the transaction. Pessimistic locking is more likely to cause deadlocks. A deadlock occurs with two transactions when each blocks access to resources needed by the other.
• Optimistic locking assumes that conflicts between transactions are unlikely but might occur. Transactions are allowed to execute without locking resources. The only time resources are checked for a conflict is when data changes are made. If a conflict occurs, the transaction is rolled back.

Row versioning

Microsoft introduced row versioning as an alternative to shared locks in SQL Server 2005. With row versioning, rows are read into tempdb at the beginning of a transaction and the transaction uses that copy of those rows throughout the duration of the transaction. Row versioning protects the transaction from:

• Dirty reads
• Nonrepeatable reads
• Phantoms

Even when row versioning is used, SQL Server still takes an exclusive lock on a row before updating it.

Row versioning allows for optimum concurrency (multiple users accessing data at the same time), while providing good protection. However, you must ensure that tempdb has sufficient disk space available. Using row versioning might also degrade performance because of the resources required to move data in and out of tempdb.

For more information, see Lock Modes.