In the first part of SQL Server Transactions and Locking article series, you learned the basics of SQL Server transactions, how they work and how to handle them, and how to write procedures that use transactions. In this part, I will give an overview of SQL Server locking and transaction isolation levels, how to set the transaction isolation level, and how some isolation levels use locking, while others use row versioning. You also learn about what type of locks data update requires.
Locking overview
The locking is a necessary part of the transaction process when working in multi-user online transaction processing (OLTP) environment. SQL Server use locks to prevent update conflicts. For example, when one user is updating the data in the table, the SQL Server locks prevent other users from accessing the data that are being updated. Locks help to prevent:
- Lost updates: Occurs when two transactions are updating the same data simultaneously. The changes are saved to the last transaction that writes to the database, losing changes of another transaction.
- Dirty reads: Occurs when a transaction reads uncommitted data from another transaction. This may lead to changes to making inaccurate changes to the data. This is also known as an uncommitted dependency.
- Nonrepeatable reads: Occurs when row data changes between data reads. This is also referred to as inconsistent analysis.
- Phantoms: A record appears when a transaction rereads data through after making a change
You can serialize transactions with locks, which means that only one person can change a data item, such as a particular row, all at once. 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 for current data manipulation or definition action. For example, SQL Server uses a row lock to update a single row of data in a table. SQL Server uses dynamic lock management, meaning that the level of locking can be automatically adjusted as needed. You can use the dynamic management view sys.dm_tran_locks for information about active locks.
Basic locks
SQL Server supports the following types of locks:
- Shared locks (S): It is used when performing read-only operations against the database. Resources locked with a shared lock are available for SELECT, but not for modification.
- Exclusive locks (X): Used for operations that modifies data, such as, INSERT, UPDATE and DELETE statements require exclusive locks. No more than one transaction can have an exclusive lock on a resource. If there is an exclusive lock on a resource, no other transaction can access that resource.
- Intent lock: Sets a lock 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 has no intent lock, it can issue the requested lock without checking each row for a lock.
- Update lock (U): This type of lock usually 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: It is used to prevent a table or index that is being used in 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): It is used to prevent other processes from accessing a table while bulk load procedure is being processed. It will, however, allow treatment of concurrent bulk load processes, allowing you to execute parallel loads. A bulk load procedure is one performed by using bulk copy program (bcp) or BULK INSERT.
Optimistic and pessimistic locking
They are the two terms that are commonly used to describe locking methods:
- Pessimistic locking locks resources as they are acquired, holding locks on the duration of the transaction. It is more likely to cause deadlocks. A deadlock occurs when two transactions each blocks access to the resources needed for the other transaction.
- Optimistic locking assumes that conflicts between transactions are not likely, but it could happen. Transactions can be executed without blocking resources. The only time that the resources are controlled by a conflict is when changes are made to the data. If a conflict occurs, the transaction is rolled back.
Row versioning
Since the release of SQL Server 2005, Microsoft introduced row versioning as an alternative to shared locks. 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 length of the transaction. Row versioning protects the transaction from:
- Dirty reads
- Nonrepeatable reads
- Phantoms
Even when using row versioning, SQL Server still has an exclusive lock on a row before updating it.
Row versioning allows for optimum concurrency (multiple users access to data at the same time), while providing good protection. However, you must ensure that tempdb has enough disk space available. Using row versions can also degrade performance due to the resources required to move data in and out of tempdb.
Transaction isolation
Transaction Isolation protects transactions activities performed outside the transaction, meeting the isolation requirements of an ACID transaction. You manage transaction isolation as a session-level setting that affects all operations in their sessions. You can override the isolation level by using locking options individual accounts.
To set the transaction isolation level, you run:
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED | READ UNCOMMITTED
| REPEATABLE READ | SNAPSHOT | SERIALIZABLE
Isolation levels are defined as follows:
- READ UNCOMMITTED: Dirty reads are possible. A shared lock is not acquired and no exclusive locks are honored.
- READ COMMITTED: Prevents dirty reads by using shared locks or row versioning. The method used depends on the configuration of the database option READ_COMMITED_SNAPSHOT. If ON, SQL Server uses row versioning. If OFF, SQL Server uses shared locks. Using Row Versioning improve concurrency because SQL Server manages row versioning at a statement level, nonrepeatable which are caused by data changes between reads, might occur.
- REPEATABLE READ: Dirty reads and nonrepeatable reads cannot occur. Read locks are held until the transaction is committed or rolled back.
- SNAPSHOT: Data changes made outside the transaction after the transaction begins are not visible within the transaction. Uses row versioning. No shared locks are held. When you try to update the data, SQL Server compares the current data with those stored in tempdb. If they are different, the update fails and the transaction is rolled back. Only be used if the database option is ON ALLOW_SNAPSHOT_ISOLATION.
- SERIALIZABLE: Other transactions cannot update or insert any new rows that were read by the transaction until after the current transaction commits.
After changing the isolation level, you can run DBCC USEROPTIONS to verify that the change was made in the current connection.
Table hints
You can also use table hints to override the locking used for a single SELECT, UPDATE, INSERT, or DELETE statement. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause. The following is the general syntax for specifying table hints:
WITH ( <table_hint> [ [, ]...n ] )
<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_value [ ,...n ] )
| INDEX = ( index_value )
| FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ]
| FORCESCAN
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
<table_hint_limited> ::=
{
KEEPIDENTITY
| KEEPDEFAULTS
| HOLDLOCK
| IGNORE_CONSTRAINTS
| IGNORE_TRIGGERS
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
For more information about table hints and the hints related to locking, see Table Hints (Transact-SQL).
Summary
In this two part article series on SQL Server Transactions and Locking, you learned how to manage transactions. You learned how to set the transaction isolation level. You learned that some isolation levels use locking, while others use row versioning. You also learned that a data update requires an exclusive lock.
]]>