SQL Server Locking Hints

                                                                SQL Server Locks - Part I, Part II, Part III, Part IV

We have been discussing locking mechanism in SQL Server since last three posts. In previous posts we covered locks, deadlocks and isolation levels. Today we will discuss ways to control how SQL Server locks the data by passing locking hints.

Generally leaving SQL Server engine to handle locks works as expected, but there are circumstances where you may want to use locking hints to lock or not lock certain transaction.

Locking Hints

• HOLDLOCK- When you pass this hint to your select queries, shared locks are held until the entire transaction completes instead of releasing the lock as soon as the read is complete and the required lock (table, row or page) is no longer needed. HOLDLOCK is same as SERIALIZABLE isolation level.
• NOLOCK - When you pass this hint, SQL doesn't issue shared locks for reading data and doesn't honor exclusive locks. In other words, if there is an exclusive lock on row, page or table, query with NOLOCK hint will still execute instead of waiting for exclusive lock to be released. With this hint, dirty reads are possible. This hint only applies to SELECT statements.
• PAGLOCK - Forces SQL Server to issue page lock instead of a default table lock that may be taken.
• READCOMMITTED -Same as READ COMMITTED isolation level, i.e. only read committed data.
• READPAST - When reading data, if a row is exclusively locked, read past that row, i.e. any locked rows are skipped. Beware, your results may not have all the data if you use this hint. Also, you can only use this hint if isolation level is set at READ COMMITTED. It also only applies to SELECT.
• READUNCOMMITTED - Same as NOLOCK hint.
• REPEATABLEREAD - Same as REPEATABLE READ isolation level, i.e. once a shared lock is acquired, it isn't released until the entire transaction completes. 
• ROWLOCK - Forces SQL to issue row level locks instead of page and table level locks.
• SERIALIZABLE - Same as SERIALIZABLE isolation level and same as HOLDLOCK. All transactions are blocked until the current transaction completes.
• TABLOCK - Forces SQL to issue a table lock instead of row or page level lock. This lock is only held until the executing statement completes. However if you are running under SERIALIZABLE isolation level or also pass HOLDLOCK, the lock will be held until the end of the transaction.
• TABLOCKX - Issues an exclusive lock on the table, preventing others from reading or updating the table. 
• UPDLOCK - This is a special lock used when you are updating data. When you are updating data, data must be first read and then updated. Ordinarily when data is read, a shared lock is issued which will then be converted to exclusive lock to update the data. But what if a transaction acquires a shared lock and another transaction acquires an exclusive lock on the same data at the same time? The data just read could get out of sync. To prevent this, SQL Server issues an update lock instead of shared lock. This allows other transactions to read the data while this transaction with UPDLOCK is reading the data but no other transaction can obtain an exclusive lock on this data. The UPDLOCK is then converted to exclusive lock to update the data. This ensures data integrity since you can be sure that the data hasn't been updated since this transaction read it.
• XLOCK - A exclusive lock which will be held until the end of the transaction. You can specify it with PAGLOCK or TABLOCK, indicating whether to exclusively lock page or a table.

Let's see a lock in action...

SELECT FirstName, LastName FROM CUSTOMERS WITH (NOLOCK)

SELECT FirstName,LastName FROM CUSTOMERS WITH (ROWLOCK)

Hope this helps you understand how you can use various hints available to you. This was a long series but hopefully it will be a good refresher to understand how locking mechanism works in SQL Server.

Thank you.

SQL Server Isolation Levels

                                                                    SQL Server Locks - Part I, Part II, Part III, Part IV

In previous two posts we have been discussing SQL Server locking mechanism and how deadlocks can occur. Today we will talk about Isolation Levels. Different Isolation levels allow for various levels of concurrent operations that can be performed on the same data.

Isolation is the level to which one operation / transaction should be isolated from the other operation.

Serialization is an important concept in database management systems. The serialization allows concurrent transactions to run one after another serially. For example, if one transaction is running a read operation on a set of data, and another concurrent operation is running an update on the same data, both operations will be serialized in order the requests were received by the database management engine. For example, if read operation was requested first, a shared lock will be issued to this transaction and any other update/delete operations will be blocked or queued. Once the read operation is complete, an update lock will be issued to the update operation next in line which will read the data and then receive an exclusive lock to update the data.

But what if you don't really need this level of isolation? For example, you want to read the data even though the data may be exclusively locked by another transaction? You can achieve this by using a lower level transaction or by using locking hints (we will discuss locking hints in future post).

There are following types of isolation levels according to SQL-92 specifications.

Read Uncommitted - This is the lowest level of isolation. In this level, transactions are isolated only enough to ensure that corrupt data is not read. This level allows dirty reads, i.e. the data not yet committed.

Read Committed - This is the default level. Only committed data will be read. When a transaction has an exclusive lock, all other transactions requesting shared lock will be blocked

Repeatable Read - Repeatable read locks all the rows that it touches unlike read committed. Let's explain this with an example.

Let's say you are running a cursor where you are fetching one row at a time and then adding them to a temporary table (see below)

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                            Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE   CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

If you run this transaction at read committed level, only one row will be locked for reading at a time and then the lock will be released. Imagine you read the first row where address was "123, Roswell Road, Atlanta, GA, 30330" and insert this row in tmpTable. Now you lock the second row to read and insert into @tmpTable. Now imagine another concurrent transaction locked the first row and updated the address to "567, Peachtree Road, Atlanta, GA 30101". The record in your temp table is now incorrect.

If you run this transaction under repeatable read, this isolation level will lock the 1st row and then the second row and then the third row and so on, but will not release any lock until the entire transaction is complete. This ensures that the data read cannot be updated while the transaction is still running. While this promotes better data integrity, concurrency suffers because all other transactions must now wait for the resources to be released, resulting in timeouts.

Although repeatable read prevents data integrity, phantom inserts/updates/deletes are still possible. If one transaction reads the data more than once, then the rows are locked as they are read first time and will then be released. But, before the data is read second time, another transaction may insert a new row or update/delete rows that were read previously, resulting in phantom data.  

Serialization Level - Serialization level works by using key range locks. Using the same example as above, when this isolation is read, entire key range (in this case entire table since we are reading all records in customer table) will be locked until the entire transaction completes, which will prevent phantom rows since the other transactions must wait until this transaction ends. Serialization is the highest isolation level and while it promotes data integrity, it reduces concurrency. To know more about key range locks, refer back to my post about SQL Server Locks.

In addition to these isolation levels, SQL Server supports two additional isolation levels.

READ_COMMITTED_SNAPSHOT - This isolation level uses row versioning instead of shared lock when reading the data. Read operations only require SCH-S table level locks (i.e. to prevent DDL modifications) but no page or row level locks are needed. When reading rows modifed by another transaction, only the version of the row that existed when the transaction started is read. This only works when another isolation level "ALLOW_SNAPSHOT_ISOLATION" option is ON. 

You can set isolation level by using the following syntax...

    SET TRANSACTION ISOLATION LEVEL

    {
        READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ
        | SNAPSHOT |  SERIALIZABLE

     }

For Example,

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    GO

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                           Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

    GO

You can set ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT option at the database level by using the following script...

    ALTER DATABASE <DBName>

    SET

    {
      ALLOW SNAPSHOT_ISOLATION {ON | OFF}

       | READ_COMMITTED_SNAPSHOT {ON | OFF}

    }

Hopefully this post will help you determine the best isolation environment if you must change it. Although, SQL Server defaults work in most cases.

Thank you!

SQL Server Deadlocks

                                                                   SQL Server Locks - Part I, Part II, Part III, Part IV

Deadlocks
In previous post we discussed locking mechanism in SQL Server and what are different types of locks and how you can control default locking mechanism by using various hints with your queries.

Today, we will discuss deadlocks. Locking is a necessity for any DBMS to work in a multi-user environment and to maintain data consistency. Deadlocks occur when two transactions are waiting on each other to release the lock. Eventually SQL Server chooses one of the transaction as victim and kills it.

Let's explain this using an example -

Suppose you have a Customers table and you run the following queries in two separate transactions.

Transaction A
--statement 1
SELECT * FROM Customers WHERE Customer ID = 1
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 2

Transaction B
--statement 1
SELECT * FROM Customers WHERE Customer ID = 2
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 1

Let's assume one user calls a routine that executes transaction A and another use calls a routine that executes transaction B concurrently.

Transaction A will issue a shared lock while executing statement 1 on row 1 and transaction 2 will issue a shared lock while executing statement 1 on row 2.

Now  transaction  A will request an exclusive lock on row 2, while transaction B will request an exclusive lock on row 1.

Transaction A cannot complete because it is waiting on transaction B while transaction B cannot complete because it waiting on transaction A.

This condition is also called cyclic dependency. SQL Server engine deadlock monitor periodically checks for cyclic dependency and when it detects a dependency, it chooses one of the transaction as a victim and terminates it. The transaction chosen as a deadlock victim will terminate but the other transaction will complete successfully.

Remember, deadlocking is different than blocking. Blocking occurs when a transaction requests a lock on a resource which is exclusively locked by another transaction, in that case the requesting transaction will wait for the lock to be released.

Minimizing Deadlocks
Deadlocks are a fact of life, but you can minimize them by following some basic steps...

• Objects should be accessed in the same order
• All concurrent transactions should access the objects in the same order. For example, If there are two tables - Orders and Order Details, all concurrent transactions should request locks in the same order - For example first on Orders table and then on Order Details table. If all lock requests flow in the same direction, the subsequent requests will be blocked until a lock is released from the previous request, but deadlock should not occur.
• Avoid prompting for User input when a transaction is running
• A transaction should be completed as fast as possible so that the locks are acquired and released as quickly as possible. Requiring user input delays the transaction, hence resources will be locked for longer period of time.
• Keep Transactions Short and in one batch
• Keeping transactions short and in one batch will minimize resources, resulting in faster transaction completion.
• Use a lower Isolation Level
• We will discuss isolation levels in more detail in subsequent post but lower the isolation level, less locking. For example if you implement read committed isolation level, it allows a transaction to read data previously read by another transaction without waiting for that transaction to complete.
• Set READ_COMMITTED_SNAPSHOT to ON
• When this database option is ON, a transaction that is running under read committed isolation level uses row versioning instead of shared lock during read operations. Row versioning does not acquire shared lock for read operations. You must also set ALLOW_SNAPSHOT_ISOLATION to ON in addition to read committed snapshot for it to work.
• Use bound connections
• If your application opens two or more connections, you can bound them together such that one is a primary connection while other is a secondary connection. Under this scenario, any locks that are acquired by the primary connection are treated as they are also acquired by the secondary connection and vice versa.

This concludes our topic for today. There are some other aspects of locking such as providing locking hints with your queries or setting up different isolation levels. We will discuss them in subsequent posts.

Thank you.

SQL Server Locks

                                                                    SQL Server Locks - Part I, Part II, Part III, Part IV

Locking is a major part of any relational database, including SQL Server. The concept of Locking and how database engine issues them and how can you troubleshoot to determine some of the problems your DBMS may be facing is quite large and complex. As a result, we will be covering it in multiple parts. In this part, I will explain the types of locks available to SQL Server and how they are used. Subsequent posts will cover them in more details.

Types of Locks 


Shared Locks
Shared locks are issued at the database level when you connect to a database. They are also issued when you run a SELECT statement against a table i.e. when you read data. Shared locks are issued when you are using the pessimistic concurrency model. When a shared lock is issued, more than one transaction can read the data but the data cannot be modified. After the data has been read, the lock is released, unless you run your queries with READCOMMITTED OR READCOMMITTEDLOCK locking hint (more on these later) or you have a more restrictive isolation level.

Update Locks
They are a combination of shared and exclusive locks. When you issue an update statement, SQL server first searches for the data to be modified. Since SQL engine has to first read the data to be modified and then modify it, it can in theory issue a shared lock when reading the data and then convert it to exclusive lock when updating this data, but this could result in a deadlock, hence instead of using a shared lock then upgrading it to exclusive lock, SQL uses an Update Lock. It is very similar to an exclusive lock in that only one update lock can be held on the data at one time. The difference between an update lock and exclusive lock is that the before the data is modified, update lock has to be converted to an exclusive lock, since an update lock cannot modify the data. You can also provide an UPDLOCK hint in your statements to force an update lock.

Exclusive Locks
These locks are issued to lock the data that is being modified by one transaction to prevent modifications by other concurrent transactions. You cannot even read the data held by exclusive locks, unless you provide "NOLOCK" hint to your select query or you are using read uncommitted isolation level. Since data must be first read before it can be modified, exclusive locks are accompanied by shared or update locks on the same data. NOLOCK hint allows you to read data locked by an exclusive lock, but you may end up reading dirty data i.e. before the data is committed.

Intent Locks
Intent Locks are a mechanism for one transaction to notify others that it is intending to lock the data. Generally a transaction will issue an intent to lock the object that is higher in lock hierarchy than what is currently locked by this transaction. For example, if a transaction has exclusive lock at the row level, it may issue an intent lock on the page level or table level. This prevents others transactions from locking the higher objects. The way this works is before a transaction obtains a lock at the row or page level, it first sets an intent lock on the table. Thus, you get a more granular lock (update lock or exclusive lock) at the lower level after setting an intent level lock on its parent.

Schema Locks
When database engine is generating execution plans, it issues Schema Stability Lock. It doesn't block access to the underlying data. Schema modification lock is used when a DDL statement (Data Definition Language) is being executed i.e. schema is being modified. This lock blocks the access to the underlying data.

Bulk Update Locks
These locks are issues when performing bulk operations and you use TABLOCK hint. They allows for multiple inserts concurrently and prevent data read by other transactions. In essence this is a table lock.

Conversion Locks
Converting one type of lock to other lock results in a conversion lock. There are three types of conversion locks.

• Shared With Intent Exclusive - A transaction with a shared lock also has exclusive intent lock on some pages/rows.
• Shared with Intent Update - A transaction with a shared lock also has an update intent lock on some pages/rows.
• Update with Intent Exclusive- A transaction with an update lock also has an exclusive intent lock on some pages/rows.

Key - Range Locks
When using serializable transaction isolation level, any query that is executed more than once in a transaction must obtain the same set of rows. For example, if a query when executed first time returns 10 rows, any subsequent execution of the same query within the same transaction must return the same 10 set of rows. If this query tries to fetch a row that doesn't exist, it cannot be inserted by other transactions until the first transaction reading the row completes, because if the second transaction was allowed to insert a row, it would appear as a phantom to the first transaction. A key-range lock locks the index rows and the ranges between those index rows. Any attempt to insert/update or delete any row within this range by a second transaction would modify the index, the key-range lock blocks the second transaction until the first transaction completes.

There are two types of Key-Range Locks

• RangeX-X - Exclusive lock is issued on the interval between the keys and an exclusive lock is issued on the last key in the range.
• RangeS-U - Shared lock is issued on the interval between the keys and update lock is issued on the last key in the range.

Spin Locks

A light-weight locking in which data is not locked but the transaction waits for a short period of time for a lock to be free if data is already locked. It is a mutual exclusivity mechanism to reduce the context switching between multiple threads.

Lock Granularity

Locks can be issued against a table, page or a row. If a table has a clustered index, there is also a Key lock. When you lock at the lower level with intent to lock at the higher level, it increases concurrency since multiple operations can get the locks they need. It also depends on the isolation level we choose. More restricted the isolation level, more higher level locks to keep data intact. SQL Server allows you to pass hints such as NOLOCK, ROWLOCK, PAGLOCK or TABLOCK to override the default locking based on the isolation level. Although I would discourage using locking hints except in very rare situations.

As I mentioned at the beginning of this post, Locking is a complex topic and we will be covering it in multiple posts. So, watch out for the next post.

Thank you and your comments are welcome!