T-SQL Querying (2015)

---

The following sections discuss the details of the different isolation levels and demonstrate working under them.

Read Uncommitted isolation level

There are two sides to the Read Uncommitted isolation level—one that most people are familiar with and another that many people aren’t. The familiar side is that, unlike under the default Read Committed isolation level, under Read Uncommitted your session doesn’t acquire a shared lock to read data. This means that if one session modifies data in a transaction and keeps the transaction open, another session running under the Read Uncommitted isolation level can read the uncommitted changes made by the first session. Because the reader doesn’t acquire shared locks, there’s no conflict with the writer’s exclusive locks.

Before I demonstrate this behavior, first initialize the data by running the following code:

USE testdb;
UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

Open two connections. Run the following code from connection 1 to open a transaction, and modify a row with the value ‘Version 2’ instead of the current value ‘Version 1’:

BEGIN TRAN;
UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

Run the following code from connection 2 to set the session’s isolation level to Read Uncommitted and read the data:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 2

You got an uncommitted read, also known as a dirty read. Remember that another way to work under the Read Uncommitted isolation level is to use the NOLOCK hint in the query.

Run the following code from connection 1 to roll back the transaction:

ROLLBACK TRAN

Close both connections.

The less familiar, or dark, side of Read Uncommitted has to do with how the storage engine carries out the requests from the relational engine when SQL Server executes a query plan. The storage engine perceives the Read Uncommitted isolation level as a blanket approval to focus on speed, even at the cost of reduced consistency. As an example, when the storage engine processes an Index Scan operator (clustered or nonclustered) with an Ordered: False property, it tends to prefer an allocation-order scan of the data (using IAM pages) over an index-order scan (using the linked list). As a result, a reader can read the same row multiple times or skip rows if another writer causes page splits during that time. Under Read Committed, the storage engine prefers to use an index-order scan exactly to prevent such things from happening. I provide more details about such problems and demonstrate such behavior in Chapter 2.

As a result, using the Read Uncommitted isolation level is rarely a good thing. Sadly, many environments use it extensively, mainly in the form of NOLOCK hints in the queries, without realizing all the implications. Environments that suffer from performance problems that are related to locking and blocking should consider either using the row-versioning technology or the In-Memory OLTP engine.

As an example where using the Read Uncommitted isolation level could be a good thing, consider a data warehouse that absorbs changes only during the night in an Extract, Transform, and Load (ETL) process. Only queries that read data are submitted during the day. You want to remove the overhead of shared locks obtained by readers and allow the storage engine to use faster access methods. You can achieve this by setting the database to read-only mode, but then you won’t allow SQL Server to automatically create statistics when needed. The Read Uncommitted isolation level gives you the performance advantages without preventing SQL Server from automatically creating statistics when needed. As long as you know with certainty the changes cannot happen while users are reading data, there’s no risk.

Read Committed isolation level

Under the Read Committed isolation level, a session is required to obtain shared locks to read data. The session releases the shared lock as soon as it is done with the resource. It doesn’t need to wait until the statement finishes, and certainly not until the transaction finishes. As a result, under this isolation level you can read only committed changes—hence the name Read Committed.

To see a demonstration of this behavior, open two connections. Run the following code from connection 1 to open a transaction and modify a row:

BEGIN TRAN;
UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

Run the following code from connection 2 to set the session’s isolation level to Read Committed (also the default) and read the data:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

At this point, the reader is blocked.

Run the following code from connection 1 to commit the transaction:

COMMIT TRAN;

The session in connection 1 releases the exclusive lock from the row, allowing the session in connection 2 to obtain the shared lock it was waiting for and read the data. You get the following output in connection 2:

col2
----------
Version 2

In connection 2, you waited in order to read the committed state of the data.

Run the following code to set the value of col2 in the modified row back to ‘Version 1’.

UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

Close both connections.

The Read Committed isolation level does not try to prevent nonrepeatable reads. Suppose you query the same rows twice within the same transaction. Because you’re not holding any locks between reads, another session can modify the rows during that time. Therefore, two different queries can get different states of the same rows.

The Read Committed isolation level does not try to prevent lost updates. Suppose you have two transactions running in parallel, both with flow that includes reading data, making calculations, and later updating the data. Both transactions read the same initial state of the data. Each transaction, based on its calculations, tries to write its own values to the data. One transaction will apply the update first and commit. The other transaction will apply the update last, overwriting the first transaction’s update.

Repeatable Read isolation level

The Repeatable Read isolation level guarantees that once a query within a transaction reads rows, all subsequent queries in the same transaction will keep getting the same state of those rows. The way SQL Server achieves this behavior is by causing a reader to obtain shared locks on the target rows and to keep them until the end of the transaction. After you open a transaction and read some rows, any other session that tries to update those rows will be blocked until your transaction completes. Therefore, subsequent reads within your transaction are guaranteed to get the same state of those rows as in the first read.

To see a demonstration of a repeatable read, open two connections. Run the following code in connection 1 to set the session’s isolation level to Repeatable Read, open a transaction, and read a row:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 1

Try to update the same row from connection 2:

UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;

You get blocked because you need an exclusive lock on the row, and you cannot get one while the other session is holding a shared lock on that row.

Back in connection 1, run the following code to query the row again and commit the transaction:

SELECT col2 FROM dbo.T1 WHERE keycol = 2;
COMMIT TRAN;

You get the following output, which represents a repeatable read:

col2
----------
Version 1

Meanwhile, the writer obtained the exclusive lock it was waiting for, modified the row, and committed the transaction.

When you’re done, run the following code for cleanup:

UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

Close both connections.

The Repeatable Read isolation level doesn’t allow update conflicts. Suppose you have two transactions running in parallel, both with flow that includes reading data, making calculations, and later updating the data. Both sessions read the same initial state of the data after obtaining shared locks, and they keep the locks until the end of the transaction. Each transaction, based on its calculations, tries to update the data with its own values. To apply the update, both sessions request exclusive locks, each getting blocked by the other’s shared locks. That’s a deadlock. SQL Server detects the deadlock, typically within a few seconds. Once it does, it chooses one of the sessions as the deadlock victim and terminates its transaction, causing it to undo the work and release all locks. That session gets error 1205. Anticipating such errors, you can have error-handling code with retry logic. If your session is chosen as the deadlock victim, when you retry the transaction you start by reading the new state of the data and use it in your calculations before updating the data. This way, no update is lost.

If you get frequent deadlocks that are related to the prevention of lost updates, you should reevaluate your solution. An alternative solution is to use the Read Committed isolation level, and in those transactions where you want to prevent lost updates you add an explicit UPDLOCK hint in the query that reads the data. The session that reads the data first gets the update lock, and the session that gets there second is blocked already in the attempt to read the data. You cause a queuing of the transactions without the need for error-handling code with retry logic.

Serializable isolation level

The Serializable isolation level guarantees that once you query data within a transaction, you will keep working with the same state of the data, seeing only your own changes, until your transaction completes. In other words, transactions work with the data in a serializable fashion. Once a session opens a transaction and accesses data, it’s as if it’s the only one working with the data.

For example, if you issue the same query multiple times in the same transaction, all occurrences of the query get the exact same result sets (assuming the same transaction doesn’t modify the data between the queries). Not only that, you’re guaranteed to get repeatable reads like under the Repeatable Read isolation level. You’re also guaranteed not to get phantom reads. Under the Repeatable Read isolation level, phantom reads are possible. Often people are surprised by this fact, but remember, the Repeatable Read isolation level guarantees only that once you read rows, subsequent reads will get the same state of the rows that you read previously. It’s not supposed to guarantee you won’t get new rows that were added between the reads.

Suppose that working under the Serializable isolation level you submit a query against the table T1 with the filter col1 = 102 twice in the same transaction. You have an index on the filtered column. To guarantee serializable behavior, SQL Server uses key-range locks. This means that once you query data for the first time in a transaction, your session locks the range of keys in the index starting with the first qualifying key and ending with the key after the last qualifying one. Your session keeps the locks until the transaction completes. If another session tries to add, update, or delete rows that satisfy your query filter after you queried the data for the first time and before you complete the transaction, it gets blocked. This way, you get no phantom reads.

To see a demonstration of serializable behavior, first create an index on col1 in T1 by running the following code:

CREATE INDEX idx_col1 ON dbo.T1(col1);

Open two connections. Run the following code in connection 1 to set the isolation level to Serializable, open a transaction, and query the rows in T1 where col1 is equal to 102:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT *
FROM dbo.T1 WITH (INDEX(idx_col1))
WHERE col1 = 102;

The query uses a hint to force the use of the index because the table is so small. This code generates the following output:

keycol col1 col2
------- ----- ----------
2 102 Version 1

In connection 2, run the following code to attempt to insert a new row into T1 with the value 102 in col1:

INSERT INTO dbo.T1(keycol, col1, col2) VALUES(5, 102, 'D');

If the code in connection 1 was running under the Repeatable Read isolation level, your insert would have been successful. However, the code in connection 1 is running under the Serializable isolation level, so your insert is blocked.

Back in connection 1, run the following code to execute the same query a second time and to commit the transaction:

SELECT *
FROM dbo.T1 WITH (INDEX(idx_col1))
WHERE col1 = 102;
COMMIT TRAN;

You get the same output you got in the first execution of the query:

keycol col1 col2
------- ----- ----------
2 102 Version 1

Meanwhile, the INSERT statement in connection 2 completed.

When you’re done, run the following code for cleanup:

DELETE FROM dbo.T1 WHERE keycol = 5;
DROP INDEX dbo.T1.idx_col1;

Close both connections.

Snapshot and Read Committed Snapshot isolation levels

The Snapshot and Read Committed Snapshot isolation levels are based on a model that mixes the use of locking and row versioning. Writers keep using exclusive locks as usual. In addition, when you update or delete a row, SQL Server copies the version before the change to a version store in tempdb and creates a link from the current version of the row to the older one. If you apply multiple updates to a row, SQL Server will keep adding more versions to the chain, placing the newest one in front of the others. SQL Server adds 14 bytes of versioning information to new rows that you insert. Readers don’t take shared locks; rather, if the current version of the row is not the one they are supposed to see, they request an older committed version of the row from the version store.

The Snapshot isolation level gives you a transaction-level-consistent view of the data, plus it prevents lost updates by generating an “update conflict” error. The Read Committed Snapshot isolation level gives you a statement-level-consistent view of the data and doesn’t attempt to prevent update conflicts. With this in mind, and by examining Table 9-3 that I provided earlier, identify which locking-based isolation levels are the closest parallels to the row-versioning-based ones. You will notice that Snapshot is closest to Serializable, and Read Committed Snapshot is closest to Read Committed.

A cleanup thread wakes up every minute and removes row versions that aren’t needed anymore from the tail forward until getting to the first row version that is still needed. So, if you have long-running transactions, it increases the chances for long chains.

Under the row-versioning-based isolation levels, readers don’t need to wait. The fact that they don’t use shared locks reduces the overhead related to locking and tends to result in reduced blocking and deadlocking. The tradeoff is that readers might need to traverse linked lists to get to the correct row version, and writers need to write row versions to the version store. You need to make sure that you tune tempdb to cope with the increased load and that you do thorough testing before enabling row versioning in production.

Each of the row-versioning-based isolation levels requires you to turn on a respective database option to enable it. To allow the use of the Snapshot isolation level, you turn on the database flag ALLOW_SNAPSHOT_ISOLATION. This causes SQL Server to start versioning rows. To actually use the Snapshot isolation level, you also need to set the session’s isolation level to SNAPSHOT. To work with the Read Committed Snapshot isolation level, you turn on the database option READ_COMMITTED_SNAPSHOT. Once you do, you change the semantics of the default Read Committed isolation level from locking-only-based semantics to semantics based on mixed locking and row versioning. Because enabling this database flag changes the default isolation level, you need exclusive access to the database to apply it. New connections will use the Read Committed Snapshot isolation level. Then, in cases where you want a reader to use shared locks, you add the table hint READCOMMITTEDLOCK.

To enable the use of the Snapshot isolation level in the testdb database, turn on the database option ALLOW_SNAPSHOT_ISOLATION ON, like so:

ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON;

Open two connections. Run the following code from connection 1 to open a transaction, update a row, and read the row:

BEGIN TRAN;
UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

This code generates the following output:

col2
----------
Version 2

Query the view sys.dm_tran_version_store to see the row versions that currently reside in the version store:

SELECT * FROM sys.dm_tran_version_store;

You get one row version in the output.

Run the following code in connection 2 to set the session’s isolation level to Snapshot, open a transaction, and query the row that was modified by connection 1:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

Remember that under Snapshot you get a transaction-level-consistent view of the data. This means that throughout the transaction you will get the last committed state of the data at the point in time when you issued the first statement within the transaction. You get the following output:

col2
----------
Version 1

Then, in connection 1, run the following code to commit the transaction and read the row:

COMMIT TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 2

Back in connection 2, run the following code to read the row again, still in the open transaction:

SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the same state of the row you got in the previous query:

col2
----------
Version 1

If you were running under the Read Committed Snapshot isolation level, instead of getting ‘Version 1’, you would have got the new committed state ‘Version 2’.

Finally, run the following code in connection 2 to commit the transaction and query the row again:

COMMIT TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 2

Run the following code to update the row back to the value ‘Version 1’:

UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

As mentioned, the Snapshot isolation level prevents lost updates by detecting update conflicts. As an example, in connection 1 run the following code to set the isolation level to Snapshot, open a transaction, and query a row:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 1

In connection 2, run the following code to update the same row:

UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;

Back in connection 1, try to update the row too:

UPDATE dbo.T1 SET col2 = 'Version 3' WHERE keycol = 2;

SQL Server detects that someone else modified the row you’re trying to update, and it generates error 3960, indicating that it detected an update conflict:

Msg 3960, Level 16, State 2, Line 7
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation
to access table 'dbo.T1' directly or indirectly in database 'testdb' to update, delete, or
insert the row that has been modified or deleted by another transaction. Retry the transaction
or change the isolation level for the update/delete statement.

SQL Server terminates your transaction and undoes the work. Anticipating such conflicts, you can have error-handling code with retry logic. If you think about this, getting an “update conflict” error is better than getting a more generic deadlock error. Deadlocks can happen for all sorts of reasons, and perhaps you want to handle differently cases where an error is related to the prevention of lost updates and others.

Run the following code for cleanup:

UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

Close both connections.

Next, I’ll demonstrate using the Read Committed Snapshot isolation level. Assuming you have exclusive access to the database, turn on the database option READ_COMMITTED_SNAPSHOT in testdb to change the semantics of the Read Committed isolation level to Read Committed Snapshot:

ALTER DATABASE testdb SET READ_COMMITTED_SNAPSHOT ON;

Open two connections. Both are now running under the Read Committed Snapshot isolation level by default. Run the following code in connection 1 to open a transaction, update a row, and then read that row:

BEGIN TRAN;
UPDATE dbo.T1 SET col2 = 'Version 2' WHERE keycol = 2;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the following output:

col2
----------
Version 2

Run the following code in connection 2 to open a transaction and read the same row:

BEGIN TRAN;
SELECT col2 FROM dbo.T1 WHERE keycol = 2;

You get the last committed state that was available when your statement started:

col2
----------
Version 1

Go back to connection 1, and run the following code to commit the transaction:

COMMIT TRAN;

Then, in connection 2, run the following code to query the row again and commit the transaction:

SELECT col2 FROM dbo.T1 WHERE keycol = 2;
COMMIT TRAN;

You get the new state of the row that was last committed before you issued your statement:

col2
----------
Version 2

Remember that under the Snapshot isolation level in the same situation you got ‘Version 1’, because that isolation level gives you a transaction-level-consistent view of the data.

When you’re done, run the following code for cleanup:

UPDATE dbo.T1 SET col2 = 'Version 1' WHERE keycol = 2;

Close both connections.

Run the following code to restore the testdb database to its default settings:

ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE testdb SET READ_COMMITTED_SNAPSHOT OFF;

Deadlocks

A deadlock is a combination of blocking situations that end up in a cycle. It can involve two or more sessions. For example, suppose that session X blocks session Y, session Y blocks session Z, and session Z blocks session X. SQL Server actively searches for deadlocks normally every five seconds. When it detects a deadlock, it reduces the interval between searches to as low as 100 milliseconds, depending on the frequency of the deadlocks. After some time with no deadlocks, it increases the interval to five seconds. Once SQL Server detects a deadlock, it chooses one of the sessions involved as the deadlock victim and terminates its transaction. That session gets error 1205; all the work within the transaction is undone and all locks are released.

SQL Server chooses the deadlock victim based on two criteria. You can set an option in your session called DEADLOCK_PRIORITY to a value in the range –10 to 10. SQL Server chooses the session with the lowest deadlock priority as the deadlock victim. In the case of ties in the priority, SQL Server estimates the amount of work involved in rolling back each of the transactions based on the information recorded in the transaction log and chooses the transaction that involves the least work. In the case of ties in the priority and the estimated amount of work, SQL Server chooses the victim randomly.

SQL Server provides you with a number of tools to troubleshoot deadlocks. Trace flags 1222 and 1204 cause SQL Server to write information about the deadlock to the error log. The trace-event Deadlock graph and the Extended Events event xml_deadlock_report (part of the defaultsystem_health session) can be used to produce a deadlock graph as an XML instance. The deadlock graph can be viewed graphically in Profiler and SSMS. You can also run a trace or an Extended Events session with the following events: sql_statement_starting, sp_statement_starting, or both as needed; lock_deadlock; and lock_deadlock_chain. The deadlock-chain event reports which session IDs were involved. You analyze the statement-starting events of the sessions involved, from the moment their transactions started until the deadlock event. This way, you can identify the sequence of statements that lead to the deadlock. The problem with running such a trace or Extended Events session is that it can be expensive if you run it for long periods.

In the following sections, I demonstrate a simple deadlock example, and then I describe the measures you can take to reduce deadlock occurrences. After that, I describe a more complex deadlock example involving a single table and a single statement submitted by each session.

Simple deadlock example

To see a demonstration of a simple deadlock, open two connections. Run the following code in connection 1 to open a transaction and update a row in T1:

BEGIN TRAN;
UPDATE dbo.T1 SET col1 = col1 + 1 WHERE keycol = 2;

The session acquires an exclusive lock on the row.

Run the following code in connection 2 to open a transaction, and update a row in T2:

BEGIN TRAN;
UPDATE dbo.T2 SET col1 = col1 + 1 WHERE keycol = 2;

The session acquires an exclusive lock on the row.

Back in connection 1, run the following code to try and query the row from T2 that the other session is locking and to commit the transaction:

SELECT col1 FROM dbo.T2 WHERE keycol = 2;
COMMIT TRAN;

This session needs a shared lock to read the row but cannot obtain it while the other session is holding an exclusive lock on the same row. This session is blocked.

Run the following code in connection 2 to try and query the row from T1 that the other session is locking and to commit the transaction:

SELECT col1 FROM dbo.T1 WHERE keycol = 2;
COMMIT TRAN;

This session also gets blocked. At this point, you have a deadlock. Within a few seconds, SQL Server detects the deadlock, chooses a deadlock victim based on the logic I described earlier, and terminates the victim’s transaction. In my case, SQL Server chose the session in connection 2 as the victim. I got the following error in connection 2:

Msg 1205, Level 13, State 51, Line 5
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.

Close both connections.

Measures to reduce deadlock occurrences

When deadlock occurrences are infrequent, you can use error-handling code with retry logic to retry the task when you get error 1205. But when deadlocks are frequent in the system, they can become a performance problem. It’s not realistic to expect that you can eliminate all deadlock occurrences in the system; after all, they have a reason to exist. But you can certainly take measures to reduce their frequency.

A common cause of deadlocks that can be avoided is a lack of important indexes. If you don’t have an index to support a query filter, SQL Server has to scan the entire table to get the qualifying rows. As it scans the table, it acquires locks even on rows you don’t really need. As a result, you can run into conflicts that lead to a deadlock even though there’s no real logical conflict between the sessions involved.

To see a demonstration of such a deadlock, open two connections. Run the following code from connection 1 to open a transaction and update the rows in T1 where col1 is equal to 101:

BEGIN TRAN;
UPDATE dbo.T1 SET col2 = col2 + 'A' WHERE col1 = 101;

Run the following code from connection 2 to open a transaction and update the rows in T2 where col1 is equal to 203:

BEGIN TRAN;
UPDATE dbo.T2 SET col2 = col2 + 'B' WHERE col1 = 203;

Run the following code from connection 1 to query the rows from T2 where col1 is equal to 201:

SELECT col2 FROM dbo.T2 WHERE col1 = 201;
COMMIT TRAN;

You get blocked even though there’s no real logical conflict.

Run the following code from connection 2 to query the rows from T1 where col1 is equal to 103:

SELECT col2 FROM dbo.T1 WHERE col1 = 103;
COMMIT TRAN;

You get blocked again, even though there’s no real logical conflict. At this point, SQL Server detects a deadlock. In my test, SQL Server terminated the transaction in connection 2, generating the following error:

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.

Run the following code to create the missing indexes:

CREATE INDEX idx_col1 ON dbo.T1(col1);
CREATE INDEX idx_col1 ON dbo.T2(col1);

Rerun the test, but first add the hint WITH(INDEX(idx_col1)) after the table name in both SELECT queries to force SQL Server to use the index. Normally, you do not need to do this. However, because the tables in our example are so small, the chances are that SQL Server will scan the whole table even with the index in place. This time, the test should complete with no conflicts.

In addition to using indexing, you can take other measures to reduce deadlock occurrences in your system. The order in which you access physical resources in the different transactions is important. For a deadlock to happen, you must access physical resources in different transactions in reverse order. For example, consider the simple deadlock example I presented earlier. In one transaction, you first modify T1 and then query T2, and in another transaction you first modify T2 and then query T1. If it’s not critical to your application to perform the activities in a particular order, by accessing the objects in the same order in both transactions (say, T1 first and T2 second), you prevent the deadlock from happening.

Another aspect you should consider is the length of your transactions. The longer the transaction is, the greater likelihood you have of blocking, and therefore the greater likelihood you have for deadlocks. You should try to reduce the length of your transactions by pulling out activities that aren’t really integral parts of the transaction.

Finally, another measure you should consider is changing the isolation level you’re using. For example, suppose you’re currently using the Read Committed isolation level. You have lots of concurrency problems, including deadlocks, and many of them involve writers and readers. Switching to a row-versioning-based isolation level like Read Committed Snapshot eliminates the conflicts that involve readers because readers don’t acquire shared locks. This tends to result in an overall reduction of occurrences of blocking, and consequently a reduction in occurrences of deadlocks.

To recap, the four main measures you can take are adding supporting indexes, accessing objects in the same order, reducing the length of the transactions, and using a row-versioning-based isolation level.

Deadlock with a single table

When people think of deadlocks, they usually intuitively assume more than one table is involved and at least two different statements are submitted by each of the transactions. It’s true that for a deadlock to happen at least two different sessions need to access at least two different physical resources in reverse order. But it could be that each side submits only one statement against the same table, and that the query plans for the two statements access at least two physical resources like different indexes in reverse order. Figure 9-25 illustrates such a deadlock.

FIGURE 9-25 Deadlock with a single table.

There’s a clustered index defined with keycol as the key and a nonclustered index defined with col1 as the key. Connection 1 submits an UPDATE statement that modifies a row in T1 where keycol is equal to 2, setting col1 to some new value. At the same time, connection 2 submits a SELECT statement against T1, filtering the row where col1 is equal to 102 and returning col2 in the result. Observe that the UPDATE statement first updates the row in the clustered index (after acquiring an exclusive lock) and then tries to update the respective row in the nonclustered index (requesting an exclusive lock). The SELECT statement first finds the row in the nonclustered index (acquiring a shared lock) and, because col2 is not part of the index, it then tries to look up the respective row in the clustered index (requesting a shared lock). It’s a deadlock.

Obviously, it’s much trickier to figure out how such a deadlock happened compared to ones that involve explicit transactions with multiple activities in each transaction. But even if you manage to figure out how a deadlock happened, usually you also want to reproduce it. You need to submit the activities from both sides simultaneously. The easiest way to get the timing right is to run two infinite loops from the two connections. Usually, after a few seconds you will see it happening.

Before I demonstrate such a deadlock, first run the following code to initialize values in the row in T1 where keycol is equal to 2:

UPDATE dbo.T1 SET col1 = 102, col2 = 'B' WHERE keycol = 2;

Open two connections. Run the following code in connection 1 to execute an infinite loop that updates the row in each iteration, setting col1 to 203 minus the current value. (This will cause the current value to alternate between 101 and 102.)

SET NOCOUNT ON;
WHILE 1 = 1
UPDATE dbo.T1 SET col1 = 203 - col1 WHERE keycol = 2;

Run the following code in connection 2 to execute an infinite loop that queries the row in each iteration and returns the col2 value. (An index hint is used because the table is so small.)

SET NOCOUNT ON;

DECLARE @i AS VARCHAR(10);
WHILE 1 = 1
SET @i = (SELECT col2 FROM dbo.T1 WITH (index = idx_col1)
WHERE col1 = 102);

When I ran this test, after a few seconds SQL Server detected a deadlock and terminated the transaction in connection 2, generating the following error:

Msg 1205, Level 13, State 51, Line 5
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.

When thinking about what measures you can take to prevent this deadlock, remember there are four things to consider. Two of them are inapplicable here because each side submits only a single statement; you cannot reverse the order of access to the objects, and you cannot shorten the transaction. However, you still have two options. One option is to extend the index on col1 to include col2. This way, you prevent the need for the lookup in the SELECT statement, and therefore prevent the deadlock. The other option is to evaluate switching to a row-versioning-based isolation level. Under such an isolation level, readers don’t acquire shared locks, which eliminates the possibility of a deadlock in our case. Obviously, you will not consider switching to a row-versioning-based isolation level just for this reason, but if you were evaluating the option anyway, that’s one more reason that you add to the list.

When you’re done testing, run the following code for cleanup:

USE testdb;

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2', 'U') IS NOT NULL DROP TABLE dbo.T2;

Close both connections.

Error handling

This section covers error handling in T-SQL using the TRY-CATCH construct. It compares error handling with and without the construct. It describes the available error functions and the THROW command. It also covers how to perform error handling in transactions and how to apply retry logic.

In my examples, I will use a table called Employees, which you create by running the following code:

USE tempdb;

IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
/* other columns */
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid)
)

The TRY-CATCH construct

The TRY-CATCH construct is a classic error-handling construct used by most modern programming languages. The implementation in SQL Server is basic, but if you’re an experienced T-SQL developer, you really will appreciate it when you compare it to the legacy error-handling tools.

In old versions of SQL Server, before the introduction of the TRY-CATCH construct, the main tool you had to handle errors was the @@error function. This function tells you how the previous statement terminated. If the statement finished successfully, the function returns 0; if there was an error, the function returns the error number. There are a number of problems with handling errors based on this function. Almost every statement you run causes the value of the function to be overwritten. Therefore, you have to capture the function’s value in a variable after every suspect statement that can generate an error in order not to lose the error number. This forces you to mix the usual code and the error-handling code, which is considered a bad practice because it hurts the maintainability and reusability of your error-handling code. In addition, when you are not using the TRY-CATCH construct, there are cases where nonsevere errors cause your batch (for example, procedure) to terminate. In such cases, if you have error-handling code within that batch, it doesn’t have a chance to run and you have to deal with the error in the caller.

The TRY-CATCH construct addresses most of the inadequacies of the legacy error-handling tools. You specify your usual code in the TRY block and the error-handling code in the CATCH block. If code in the TRY block completes successfully, the CATCH block is skipped. If there’s an error, SQL Server passes control to the first line of code in the CATCH block. As an example, run the following code to insert a new row into the Employees table:

SET NOCOUNT ON;

BEGIN TRY
INSERT INTO dbo.Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL);
PRINT 'After INSERT';
END TRY
BEGIN CATCH
PRINT 'INSERT failed';
/* handle error */
END CATCH;

In the first execution of the code, the INSERT statement completes successfully. The next line of code in the TRY block is executed and the CATCH block is skipped. You get the following output:

After INSERT

Execute the code again. This time, the INSERT statement fails because of a primary-key violation error. SQL Server passes control to the CATCH block. You get the following output:

INSERT failed

In case you’re curious about the proper use of the semicolon with the TRY-CATCH construct, be aware that you’re supposed to place one only after the END CATCH keywords, like so:

BEGIN TRY
...
END TRY
BEGIN CATCH
...
END CATCH;

If you specify a semicolon after the BEGIN TRY or BEGIN CATCH clause, you won’t get an error. However, in such a case, the semicolon isn’t considered to be a terminator of these clauses; rather, it is a meaningless terminator of an empty statement. If you place a semicolon after END TRY, you will get an error. In short, the proper syntax is to place a semicolon only after the END CATCH keywords.

If an error is trapped by a TRY-CATCH construct, the error is not reported to the application. If an error happens within a stored procedure (or within any other module) but not within a TRY block, the error bubbles up until a TRY block is found. SQL Server passes control to the respective CATCH block. If no TRY block is found throughout the call stack, the error is reported to the application. SQL Server supports nesting TRY-CATCH constructs, so if you anticipate errors in the CATCH block, you can place the code within a nested TRY-CATCH construct. If an error happens in a CATCH block without a nested TRY-CATCH construct, the error is treated the same as when you are not using TRY-CATCH.

Most nonsevere errors that terminate the batch when you are not using TRY-CATCH don’t terminate it and are trappable when you do use the construct. A conversion error is an example. However, there are still cases in which an error is not trappable with TRY-CATCH in the same batch. For example, compilation and binding errors—like referring to a table or column that doesn’t exist—cannot be trappable in the same batch. However, such errors are trappable in the calling batch. So, if you’re expecting such errors, make sure you encapsulate the code in a stored procedure and invoke the procedure from a TRY block. Also, some errors with severity 20 and up cause the SQL Server engine to stop processing the session and terminate the connection. Such errors obviously cannot be captured. Errors with severity 20 and up that don’t disrupt the connection are trappable.

SQL Server supports a number of functions that provide error information and that you can query in the CATCH block:

ERROR_NUMBER

ERROR_MESSAGE

ERROR_SEVERITY

ERROR_STATE

ERROR_LINE

ERROR_PROCEDURE

Mostly, the functions are self-explanatory, returning the error number, message, severity, state, line number, and procedure name where the error happened. The procedure name is interesting, especially in cases where the error bubbles up. If the error doesn’t happen in a procedure, the ERROR_PROCEDURE function returns a NULL. As for the ERROR_NUMBER function, unlike @@error it preserves its value throughout the CATCH block. That’s unless you nest TRY-CATCH constructs—in which case, the function returns the error number of the innermost one.

A common practice with error handling is that you want to deal with certain errors in the current procedure but let the upper level in the call stack deal with the rest. Prior to SQL Server 2012, there was no tool you could use to rethrow the original error. The closest tool you had was the RAISERROR command, which raises a user-defined error based on the inputs you provide to it. The typical use of this function is to construct a message containing elements you obtain from the error functions, and then raise a user-defined error with that message. But then you need to add logic to the upper level to anticipate such a user-defined error and to extract the error information from that message. This sort of handling is quite awkward.

SQL Server 2012 introduced the THROW command to address this need. This command has two modes: one with parameters and one without. The former is just an alternative to using the RAISERROR command to raise a user-defined error. The latter is the more critical addition. Without parameters, the THROW command simply rethrows the original error.

As an example of typical error handling, the following code demonstrates a TRY-CATCH construct that issues an INSERT statement, traps errors, handles some of the errors, and rethrows the rest:

BEGIN TRY

INSERT INTO dbo.Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1);
-- Also try with empid = 0, 'A', NULL, 1/0
PRINT 'After INSERT';

END TRY
BEGIN CATCH

IF ERROR_NUMBER() = 2627
BEGIN
PRINT 'Handling PK violation...';
END;
ELSE IF ERROR_NUMBER() = 547
BEGIN
PRINT 'Handling CHECK/FK constraint violation...';
END;
ELSE IF ERROR_NUMBER() = 515
BEGIN
PRINT 'Handling NULL violation...';
END;
ELSE IF ERROR_NUMBER() = 245
BEGIN
PRINT 'Handling conversion error...';
END;
ELSE
BEGIN
PRINT 'Re-throwing error...';
THROW;
END;

PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message : ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT 'Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10));
PRINT 'Error Proc : ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');

END CATCH;

I use this code for illustration purposes. I issue PRINT statements so that you can see which parts of the code are activated.

When you execute this code for the first time, the INSERT statement finishes successfully and the CATCH block is skipped. You get the following output:

After INSERT

When you execute the code for the second time, the INSERT statement fails because of a primary-key violation and SQL Server passes control to the CATCH block. The error-handling code identifies the error as a primary-key violation error. You get the following output:

Handling PK violation...
Error Number : 2627
Error Message : Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key
in object 'dbo.Employees'. The duplicate key value is (2).
Error Severity: 14
Error State : 1
Error Line : 4
Error Proc : Not within proc

Try the code a few more times after changing the employee ID value you pass to the following options: 0, ‘A’, NULL, 10/0. The last one will cause a “divide by zero” error. This error isn’t one of the errors you want to deal with in the current level; therefore, the code ends up rethrowing the error.

Errors in transactions

When an error happens in a transaction but not in a TRY block, there are two possible consequences as far as the transaction is concerned: either the error is minor enough (for example, a primary-key violation) that SQL Server leaves the transaction open and committable, or the error is severe enough (for example, a conversion error) that SQL Server aborts the transaction. In such a case, it’s enough for you to query the @@trancount function to know what the transaction state is. If the function returns zero, there’s no transaction open; if it returns a value greater than zero, the transaction is open and committable. In the latter case, you can decide what you want to do; you can either roll the transaction back or commit it. If you want all errors to cause the transaction to abort, turn on the session option XACT_ABORT.

When an error happens in a transaction within a TRY block, things are handled differently. An error that would normally cause a transaction to end outside of a TRY block causes it to enter an open but uncommittable state when it happens inside a TRY block. This state is also known as the failed, or doomed, state. When a transaction is in this state, you’re not allowed to make any changes, but you are allowed to read data. You’re not allowed to commit the transaction; rather, you eventually will have to roll it back before you can start applying changes in a new transaction. The advantage of this state compared to SQL Server ending your transaction is that you can read data that your transaction generated before you roll the transaction back.

Recall that when you turn on the session option XACT_ABORT, all errors that happen outside of a TRY block cause the transaction to abort. However, turning on this option has a different effect on errors that happen inside a TRY block; all errors cause the transaction to enter the open and uncommittable state.

When an error happens in a transaction within a TRY block, there are three possible consequences as far as the transaction is concerned. If the error is minor enough (for example, a primary-key violation), the transaction remains open and committable. If the error is more severe (for example, a conversion error) but not too severe, the transaction enters the open and uncommittable state. If the error is very severe, SQL Server might end the transaction. As mentioned, some errors with severity 20 and up cause the SQL Server engine to stop processing the session and terminate the connection, so you won’t be able to trap those with a TRY-CATCH construct. You will have to deal with such errors in the caller. However, some errors with severity 20 and up don’t disrupt the connection, and you will be able to trap them.

If you need to know the state of the transaction after trapping an error with a TRY-CATCH construct, it’s not sufficient to query the @@trancount function. This function tells you only whether the transaction is open or not, not whether it’s open and committable or open and uncommittable. To get this information, you query the XACT_STATE function. This function has three possible returned values: 0 means no open transaction, 1 means open and committable, and –1 means open and uncommittable.

The following code demonstrates how you might want to handle errors in transactions:

-- SET XACT_ABORT ON

BEGIN TRY

BEGIN TRAN;
INSERT INTO dbo.Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1);
/* other activity */
COMMIT TRAN;

PRINT 'Code completed successfully.';

END TRY
BEGIN CATCH

PRINT 'Error ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.';

IF (XACT_STATE()) = -1
BEGIN
PRINT 'Transaction is open but uncommittable.';
/* ...investigate data... */
ROLLBACK TRAN; -- can only ROLLBACK
/* ...handle the error... */
END;
ELSE IF (XACT_STATE()) = 1
BEGIN
PRINT 'Transaction is open and committable.';
/* ...handle error... */
COMMIT TRAN; -- or ROLLBACK
END;
ELSE
BEGIN
PRINT 'No open transaction.';
/* ...handle error... */
END;

END CATCH;

-- SET XACT_ABORT OFF

In the CATCH block, you query the XACT_STATE function. If the function returns –1, you can read data to investigate what you need, but then you have to roll the transaction back. If it returns 1, you can roll back or commit the transaction as you wish. If it returns 0, there’s no transaction open, so there’s no point in trying to commit or roll back the transaction. Such an attempt would result in an error. You can open a new transaction and apply changes if you like.

Run this code for the first time. The INSERT statement completes successfully, and you get the following output:

Code completed successfully.

Run this code for the second time. The INSERT statement fails because of a primary-key violation error, but it leaves the transaction open and committable. You get the following output:

Error 2627 found.
Transaction is open and committable.

Change the employee ID from 3 to ‘abc’, and run the code again. The INSERT statement fails because of a conversion error and causes the transaction to enter the open and uncommittable state. You get the following output:

Error 245 found.
Transaction is open but uncommittable.

Change the employee ID back to 3, uncomment the SET XACT_ABORT commands, and run the code again. The INSERT statement fails because of a primary-key violation error and causes the transaction to enter the open and uncommittable state. You get the following output:

Error 2627 found.
Transaction is open but uncommittable.

Retry logic

With certain kinds of errors, like deadlock and “update conflict” errors, a classic treatment involves applying retry logic. If your session is the one that got the error, once you end the transaction, the other session can continue work. There’s a likelihood that if you retry the task, you will be successful. You can create a wrapper procedure that implements the retry logic and calls the procedure that handles the original task. The following code demonstrates what such a wrapper procedure might look like (MyProc is the procedure that handles the original task):

CREATE PROC dbo.MyProcWrapper(<parameters>)
AS
BEGIN
DECLARE @retry INT = 10;

WHILE (@retry > 0)
BEGIN
BEGIN TRY
EXEC dbo.MyProc <parameters>;

SET @retry = 0; -- finished successfully
END TRY
BEGIN CATCH
SET @retry -= 1;

IF (@retry > 0 AND ERROR_NUMBER() IN (1205, 3960)) -- errors for retry
BEGIN
IF XACT_STATE() <> 0
ROLLBACK TRAN;
END;
ELSE
BEGIN
THROW; -- max # of retries reached or other error
END;
END CATCH;
END;
END;
GO

In this example, the code sets the retry counter (@retry variable) to 10. If you want, you can parameterize this counter. The code enters a loop that keeps iterating while the retry counter is greater than zero. The code executes MyProc within a TRY block. If the procedure completes successfully, the code sets the retry counter to zero to prevent the loop from executing again. Otherwise, the code reduces the retry counter by one. If, after reducing the counter, there are retries left and the error is a deadlock or “update conflict” error, the code checks whether a transaction is open. If a transaction is open, the code rolls it back. If the retry counter drops to zero or the error is not a deadlock or “update conflict” error, the code rethrows the error to let the upper level deal with it.

This example demonstrates how to handle retry logic for deadlock and “update conflict” errors involving disk-based tables. The same pattern can be used to handle errors with memory-optimized tables. However, instead of looking for errors 1205 and 3960, you will look for error numbers 41302, 41305, 41325, and 41301, which represent update conflict, repeatable read validation, serializable validation, and dependent transaction aborted errors, respectively.

Conclusion

This chapter focused on some programmability constructs SQL Server supports, mainly the ones that involve queries. It covered dynamic SQL, user-defined functions, stored procedures, triggers, SQLCLR, transactions and concurrency, and error handling. The discussions in many cases focused on robust and efficient use of the code.