Managing Transaction Isolation


To comply with the ACID rules, transactions have to be isolated from each other. This means that data that is used within a transaction has to be separated from other transactions. To accomplish this separation, every transaction locks the data it uses to prevent other transactions from using it. A lock is defined for a locked resource, which can be an index key, a data row, or a table. SQL Server always tries to lock resources as granularly as possible. It starts locking on a row-level basis in most cases, but if too many rows are locked, it can decide to escalate the lock to a table-level basis. This is done automatically. The most common lock resources that exist in SQL Server to lock data are:

  • RID A row identifier is used to lock a specific row in a heap, where no clustered index exists.

  • KEY An index key of an index is locked. When a clustered index exists for a table, this type of lock is also used to lock a row of the table because, in a clustered index, the data is part of the index. To learn more about index internals, see Chapter 6.

  • PAGE An 8Kb page in the database is locked, which can be an Index or a Data page.

  • TABLE A table lock is used to lock the whole table for an operation.

In addition, every lock has a specific lock type that defines how the lock should behave. For example, it is possible in some situations to lock data only for write access if the transaction wants to prevent other transactions from updating data, but allows other transactions to read data. In other situations, it is essential that the data is locked exclusively for the transaction, preventing any access to the data by other transactions. This behavior is implemented through lock compatibility. Each lock type by definition is compatible with certain kinds of locks from other transactions on the same resource. Because a specific lock type has to be granted for all data access operations in SQL Server, you can use lock compatibility to manage whether two or more operations can use the same data at the same time. The most common lock types used by SQL Server are:

  • Shared (S) Shared locks are used to lock data for read access. They prevent other transactions from changing the data, but not from reading the data. Shared locks are compatible with other shared locks, which allows more than one transaction to have a shared lock on a lock resource. Therefore, transactions can read the same data side by side.

  • Exclusive (X) Exclusive locks are used for every data change. They prevent other transactions from accessing the data. Therefore, an exclusive lock is not compatible with other locks.

  • Update (U) Update locks are a special case of shared locks. They are mainly used for supporting UPDATE statements. In an UPDATE statement, the data has to be read before it can be changed. Therefore it requires a lock type that doesn't prevent other transactions from reading the data while it reads the data on its own. Yet when SQL Server starts to change the data, it has to escalate the lock type to exclusive. For this read operation, SQL Server uses update locks which are compatible with shared locks but are not compatible with other update locks. Therefore, it is possible for other transactions to read data while the data is read for the UPDATE statement, but other UPDATE statements have to wait until the update lock is released.

  • Intent (I) Intent locks exist as variations of the preceding lock types, including intent shared locks, intent exclusive locks, and so on. They are used to protect locks on lower hierarchies from disallowed locks on higher hierarchies. Consider a situation when a transaction has an exclusive lock on a row in a table. In this case, it is not allowable for another transaction to get an exclusive lock on the whole table. To manage this kind of situation, intent locks are used on higher hierarchies to let other transactions know that some resources are locked on a lower hierarchy. In this case, the transaction holding the exclusive lock on the row would also lock the page and the table with an exclusive intent lock.

For a full list of lock types and lock resources in SQL Server 2005, see the SQL Server Books Online topic "Lock Modes."

Monitoring locks

To monitor which locks exists in a database, the Dynamic Management view sys.dm_tran_locks can be queried. This view provides a row for every single lock existing in the database at the moment.

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following statements to start a transaction and query the Person.Contact table. The locking hint HOLDLOCK is used in this transaction to tell SQL Server to not release the lock after the SELECT statement. Also notice that the transaction is not committed since the locks would automatically be released with a commit. How long locks are held in a transaction will be explained later in this chapter. The code for this example is included in the sample files as MonitoringLocks.sql.

USE AdventureWorks; GO BEGIN TRAN SELECT FirstName,LastName,EmailAddress     FROM Person.Contact WITH (HOLDLOCK)     WHERE ContactID = 15


3.

To examine which locks are used by this transaction, you can use the Dynamic Management view sys_dm_tran_locks. To query only the locks that belong to your transaction you can join the view with another Dynamic Management view called sys.dm_tran_current_transaction, which retrieves information about the current transaction running in that connection. Enter and execute the following SELECT statement in the query window to get the locking information and commit the transaction.

SELECT resource_type, resource_associated_entity_id,     request_mode,request_status FROM sys.dm_tran_locks dml INNER JOIN         sys.dm_tran_current_transaction dmt         ON dml.request_owner_id = dmt.transaction_id; COMMIT TRAN


4.

Below, you can see the result of the query. It shows us that a shared lock (request_mode = S) exists on a key that represents the row in the clustered index and an intent shared lock (request_mode = IS) exists on the corresponding page and on the table Person.Contact. The value GRANT in the request_status column means that all requested locks have been granted to the transaction.

5.

Now check what happens when you change the WHERE clause to retrieve more rows. Change the WHERE clause as follows and execute the whole transaction.

BEGIN TRAN SELECT FirstName,LastName,EmailAddress     FROM Person.Contact WITH (HOLDLOCK)     WHERE ContactID <7000;


6.

Now examine the locks by executing this code:

SELECT resource_type, resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks dml INNER JOIN     sys.dm_tran_current_transaction dmt     ON dml.request_owner_id = dmt.transaction_id; COMMIT TRAN


You can see that the shared lock is defined on an object resource type, in this case, the table Person.Contact. SQL Server decided that holding a table-level lock for this transaction is easier and faster than holding about 7000 key locks with all the dependent intent locks. Because SQL Server used a table-level lock, it did not need to use intent locks. That's because the table is the highest level in the locking hierarchy for data. To find out which object is locked, you can use the OBJECT_NAME function. OBJECT_NAME takes the Object ID as an argument and returns the object's name. (The column resource_associated_entity_id holds the Object ID of the locked object if OBJECT is the resource_type.)

7.

To see how SQL Server locks data while it is being changed, enter and execute the following transaction to UPDATE data on the Person.Contact table and query the associated locks. At the end, a ROLLBACK TRAN is issued to discard the change.

USE AdventureWorks; GO BEGIN TRAN UPDATE Person.Contact     SET Phone ='+43 555 333 222'     WHERE ContactID =25; SELECT resource_type, resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks dml INNER JOIN     sys.dm_tran_current_transaction dmt     ON dml.request_owner_id = dmt.transaction_id; ROLLBACK TRAN


The result is displayed below. You can see that SQL Server locked the key with an exclusive lock (request_mode = X). Whenever data is changed, SQL Server uses an exclusive lock and holds it until the end of the transaction. As stated earlier, SQL Server also uses update locks in the first step when executing UPDATE statements. Because you queried the locks after the UPDATE statement, the lock on the row had already been escalated to an exclusive lock. What you also see is that there are two intent exclusive locks (request_mode = IX) on the page and the table again, plus a lock called Sch-S on resource type METADATA. A Sch-S lock is a schema stability lock that is issued to prevent other transactions from altering the schema of the table while the data is updated because such a change is not allowed while changing data.

Transaction Isolation Levels

You have seen now that SQL Server isolates a transaction by using different lock types on lock resources. To develop safe transactions, it is crucial to not only define the contents of the transaction and the cases in which it should be rolled back, but also howand how longlocks should be held in a transaction. This is determined through isolation levels. With different isolation levels, SQL Server gives the developer the ability to define for every single transaction how strongly it should be isolated from other transactions. Transaction isolation levels define:

  • Whether locks are used when reading data

  • How long read locks are held

  • Which types of locks are used to read data

  • What happens when a read operation wants to read data that is exclusively locked by another transaction. In that case SQL Server can:

    • Wait until the other transaction releases the lock

    • Read the uncommitted data

    • Read the last committed version of the data

ANSI 99 defines four transaction isolation levels that are all supported by SQL Server 2005:

  • READ UNCOMMITTED doesn't use or check locks while reading data. Therefore, it is possible to read uncommitted data at this isolation level.

  • READ COMMITTED reads only committed data and waits until the other transaction releases the exclusive lock. Shared locks used for reading data are released right after the read operation finishes. READ COMMITTED is the default isolation level of SQL Server.

  • REPEATABLE READ reads data like READ COMMITTED, but holds shared locks until the end of the transaction.

  • SERIALIZABLE works like REPEATABLE READ. It locks not only the data affected, but also locks the whole range. This prevents new data from being inserted into a range affected by a query, which can lead to phantom reads (see the SQL Server Books Online topic "Concurrency Effects").

In addition, SQL Server has two more transaction levels, which use row versioning to read data. (We'll examine all of these levels in detail later in this chapter.) Row versioning enables a transaction to read the last committed version of data when the data is exclusively locked. This can provide significant query performance enhancements since read operations don't have to wait until locks are released. These two levels are as follows:

  • READ COMMITTED SNAPHOT is a new implementation of the READ COMMITTED level. Unlike the normal READ COMMITED level, SQL Server reads the last committed versions and therefore doesn't have to wait until locks are released when performing read operations. This level can be used as a substitute for READ COMMITTED.

  • SNAPSHOT isolation uses row versioning to provide transactional read consistency. This means that within a transaction, the same data is always read as with the SERIALIZABLE level, but data doesn't have to be locked to prevent changes from other transactions, since the read consistency is provided through row versioning.

Regardless of which transaction isolation level is defined, data changes are always locked with exclusive locks that are held until the end of the transaction.

To define the right isolation level is not always an easy decision. As a general rule, choose the isolation level that locks as little data for as short a time as possible, but still gives the transaction the required degree of safety. In the next sections, we will see some scenarios that show how these levels works in detail and how to choose the right level.

Reading Only Committed Data

In SQL Server 2005, the READ COMMITTED isolation level is the default level when a new connection is established. This level exists in two types: the READ COMMITTED and the READ COMMITTED SNAPSHOT isolation levels. The type that is applied is defined through a database option. The READ COMMITTED level waits until blocking locks are released before reading the data, while the READ COMMITTED SNAPSHOT level uses row versioning and reads the last committed version of the data when data is blocked by other transactions.

Using the READ COMMITTED Level

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following statements to read the Name and EmailAddress fields of the Person.Contact table where ContactID = 1. The code for this example is included in the sample files as ReadCommitted1.sql and ReadCommitted2.sql.

USE AdventureWorks; BEGIN TRAN SELECT FirstName, LastName, EmailAddress FROM Person.Contact WHERE ContactID = 1;


The EmailAddress gustavo0@adventure-works.com of contact Gustavo Achong is returned.

3.

Now consider that another transaction changes the EmailAddress while your transaction is still open. Open a second query window and execute the following batch to UPDATE the EmailAddress without committing the transaction.

USE AdventureWorks; BEGIN TRAN UPDATE Person.Contact SET EmailAddress = 'uncommitted@email.at' WHERE ContactID = 1;


4.

The UPDATE statement runs without any problems. One row was affected, even though the data was read first by the transaction in Query Window 1 and this transaction hasn't finished yet. This happens because the READ COMMITTED level doesn't hold shared locks, which are used for the SELECT statement, until the end of the transaction. The locks are released right after the data is read by SQL Server. This can be a problem when you need consistent read operation throughout your transaction. We will see how to accomplish this in the section "Getting Consistent Repeatable Read Operations" later in this chapter.

5.

Now switch back to Query Window 1 and try to read the data again.

SELECT FirstName, LastName, EmailAddress FROM Person.Contact WHERE ContactID = 1;


The query doesn't end because the SELECT statement is blocked. SQL Server tries to get a shared lock on the key of ContactID 1, but this is not possible since the UPDATE transaction in Query Window 2 has an exclusive lock on it. Although Query Window 2 is in READ COMMITTED level (because you haven't changed the default level), the exclusive lock is still there. This lock exists because exclusive locks for data changes are always held until the end of the transaction.

6.

Switch to Query Window 2, but leave the query in Query Window 1 running. Enter and execute the following SELECT statement to examine the granted and waiting locks in the database.

SELECT resource_type, resource_associated_entity_id,     request_mode, request_status FROM sys.dm_tran_locks


You can see that one shared lock has a request status of WAIT. This is the query running in Query Window 1. It is waiting for the query in Query Window 2, which has an exclusive lock granted on the same resource.

7.

Issue a ROLLBACK TRAN in Query Window 2 to roll back the UPDATE statement and switch to Query Window 1. You can see that the query in Query Window 1 has finished and the result is the same as before. When the transaction in Query Window 2 finished, the locks were released and the query in Query Window 1 wasn't blocked anymore. Since the transaction in Query Window 2 was rolled back, you got the original data as the result in Query Window 1. If the transaction in Query Window 2 had been committed, you would have received the new data as a result in Query Window 1.

8.

Execute a COMMIT TRAN statement in Query Window 1 and close all Query Windows.

You have seen now that in the (default) READ COMMITTED level SQL Server waits until exclusive locks are released to retrieve only actual and committed data. You have also seen that shared locks are only held until the data is read but exclusive locks are always held until the transaction has been committed. This behavior can be a problem when a lot of transactions are changing data almost constantly. In these situations reading data can be very slow because of blocking by the exclusive locks. But in some situations it would be adequate to use the last committed version of the data. In these situations it is possible to change the READ COMMITTED level to the READ COMMITTED SNAPSHOT level.

Using the READ COMMITTED SNAPSHOT Level

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following statements to activate the READ COMMITTED SNAPSHOT level. The code for this example is included in the sample files as ReadCommittedSnapshot1.sql and ReadCommittedSnapshot2.sql.

USE master; ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON


3.

Now start a transaction and change the EmailAddress as in the procedure before (but leave the transaction open) by executing the following code:

USE AdventureWorks; BEGIN TRAN UPDATE Person.Contact SET EmailAddress = 'uncommitted@email.at' WHERE ContactID = 1;


4.

Open a second query window and execute the following statements to read the Name and EmailAddress of ContactID 1.

USE AdventureWorks; BEGIN TRAN SELECT FirstName, LastName, EmailAddress FROM Person.Contact WHERE ContactID = 1;


The EmailAddress gustavo0@adventure-works.com of contact Gustavo Achong is returned since it is the last committed version of this row. Unlike the READ COMMITTED level without SNAPSHOT, the query is not blocked anymore.

5.

Close Query Window 2 and switch to Query Window 1.

6.

Execute the following statements to roll back the transaction and switch back to the READ COMMITED level. (This query will wait until you have closed Query Window 2.)

ROLLBACK TRAN GO USE master; ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT OFF


Important

This isolation level can be used to reduce blocking, but be aware that it is a database option. When it is changed, all transactions using the READ COMMITTED level in that database change their behavior as well. Therefore, it is only advisable to use it when all of these transactions are also logically correct when they read the last committed version of data instead of the actual committed version of data.


Getting Consistent Repeatable Read Operations

One disadvantage of the READ COMMITTED level is that it is possible for data that is read by one transaction to be changed by another transaction while the first transaction runs. Therefore, with both versions of the READ COMMITTED level, you aren't guaranteed consistent reads. Getting consistent reads means that, within a transaction, the same data is always read.

  • READ COMMITTED uses shared locks while reading data, but releases the locks right after the read operation. Therefore other transactions can change it.

  • READ COMMITTED SNAPSHOT reads the last committed version of the data. When it reads the data a second time, the last committed version can be a newer version than before if a second transaction has committed a change to the data.

This inconsistency can lead to problems when consistent reads are needed, such as for reporting. Imagine that your transaction calculates some business values out of your data. While doing this calculation in the READ COMMITTED level, it is possible that these values are calculated incorrectly because the base data changes while your transaction is calculating. To successfully perform this calculation, the SNAPSHOT isolation level can be used. It uses row versioning to provide a committed version of the data, but unlike the READ COMMITTED SNAPSHOT level, it always provides the last committed version of the data from the beginning of the transaction. Therefore, SQL Server always reads the same data throughout the whole transaction.

Using the SNAPSHOT Isolation Level

1.

Start SQL Server Management Studio and open a New Query window.

2.

SNAPSHOT isolation needs to be activated for the database once. After it is activated, every connection can use it when it is needed. To allow SNAPSHOT isolation in the AdventureWorks database, execute the following statement. The code for this example is included in the sample files as SnapshotIsolation1.sql and SnapshotIsolation2.sql.

USE master; ALTER DATABASE AdventureWorks     SET ALLOW_SNAPSHOT_ISOLATION ON;


3.

Now imagine that you want to run some reports against the Sales.SalesOrderDetail table, but you need consistent read operations. Execute the following statements to activate SNAPSHOT isolation for the transaction and start a transaction that returns the sum of line totals for an order. Remember the OrderTotal value.

USE AdventureWorks; SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT SUM(LineTotal) as OrderTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


4.

Open a second query window and update the SalesOrderDetail table to change the base data for the query in Query Window 1. (If you want to repeat this example, change the OrderQty from 5 to some other number so that the following code actually changes the data in the database.)

USE AdventureWorks; UPDATE Sales.SalesOrderDetail SET OrderQty = 5 WHERE SalesOrderID = 43659 AND ProductID = 777


5.

Close Query Window 2, switch back to Query Window 1, and repeat the SELECT statement.

SELECT SUM(LineTotal) as OrderTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


As you can see, the result is the same as before since SNAPSHOT isolation ignores data changes while the transaction is running. It always provides the last committed values from the beginning of the transaction.

6.

Commit the transaction and repeat the query again by executing the following code. Now you will see that the result has changed because the transaction has ended.

COMMIT TRAN SELECT SUM(LineTotal) as OrderTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


7.

Execute the following code to turn off SNAPSHOT isolation in the AdventureWorks database.

ALTER DATABASE AdventureWorks     SET ALLOW_SNAPSHOT_ISOLATION OFF;


Avoiding Simultaneous Updates to the Data

As you have seen, SNAPSHOT isolation doesn't lock the data while reading data, but provides us with a consistent view throughout the transaction. But in some situations, it is essential to lock the data for the whole transaction to avoid updates from other transactions. Suppose that you want to invoice an order. You first need to retrieve the data and check it, and then afterwards produce the invoice out of it. In such a transaction, you need to lock the data from the beginning to avoid changes made by other transactions. In this case, neither SNAPSHOT isolation nor READ COMMITTED isolation is a good choice. In such situations, the REPEATABLE READ isolation level can be used. This level works like the READ COMMITTED level without SNAPSHOT, but holds shared locks until the end of the transaction. Therefore, it prevents updates to the data.

Using the REPEATABLE READ Isolation Level

1.

Start SQL Server Management Studio and open a New Query window.

2.

Suppose that you want to process the order with OrderID 43659. First, the data has to be selected. To prevent other transactions from changing the data you are reading, use the REPEATABLE READ isolation. Execute the following code. (The code for this example is included in the sample files as RepeatableReadIsolation1.sql and RepeatableReadIsolation2.sql.)

USE AdventureWorks; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


3.

Open a second query window and try to update the SalesOrderDetail table to change the base data for the query in Query Window 1 by executing this code:

USE AdventureWorks; UPDATE Sales.SalesOrderDetail SET OrderQty = 5 WHERE SalesOrderID = 43659 AND ProductID = 777


The query waits. Unlike with SNAPSHOT isolation, it is not possible to update the data since shared locks are held to prevent data changes from other transactions. The locks can be seen through the sys.dm_tran_locks management view you have used before.

4.

Click the Cancel Executing Query button on the toolbar (shown below) to cancel the query in Query Window 2 and instead execute the following INSERT statement to add a new line item to the order:

INSERT INTO Sales.SalesOrderDetail     (SalesOrderID,CarrierTrackingNumber,     OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount) VALUES(43659,'4911-403C-98',1,758,1,874,0)


5.

Note that this statement executes successfully, even though you are in the REPEATABLE READ isolation level. This is because REPEATABLE READ locks data to prevent updates on the data, but the INSERT statement inserts new data in the database, and this is allowed. Because the new row falls into the range of the SELECT statement of the transaction in Window 1, it will be read the next time the transaction retrieves the same data. These types of rows are called phantom reads.

6.

Repeat the SELECT statement and COMMIT the transaction, as shown below.

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 COMMIT TRAN


Observe that the new row was read by the SELECT statement because it falls into the range of the statement. The REPEATABLE READ level prevents existing data from being changed, but does not prevent new data from being inserted into the range of a SELECT statement.

7.

Close SQL Server Management Studio.

Blocking and Deadlocks

To ensure that your transaction doesn't read phantom data, you can block the data by locking the range of data you are examining. This can lead to problems with deadlocks, however.

Locking Consistent Blocks of Related Data

To prevent phantom reads, the SERIALIZABLE isolation level can be used. It is stricter than the REPEATABLE READ level and locks not only the data read by a transaction, but also the ranges the transaction reads. This is done by using special lock types, called range locks, on indexes. Range locks lock the ranges defined in the WHERE clause of the SELECT statement. These locks can be used only if a related index exists. If there is no related index, SQL Server has to use a table-level lock to prevent inserts into the range. The SERIALIZABLE isolation level should therefore be used only when absolutely necessary.

Using the SERIALIZABLE Isolation Level

1.

Start SQL Server Management Studio and open a New Query window.

2.

Suppose that you want to look at the same SalesOrderID as before. Now you are using the SERIALIZABLE isolation level to prevent not only changes to the data, but also phantom reads. Enter and execute the following statement. The code for this example is included in the sample files as SerializableIsolation1.sql and SerializableIsolation2.sql.

USE AdventureWorks; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


3.

Open a second query window and try to insert a new line item for the order processed by Query Window 1.

INSERT INTO Sales.SalesOrderDetail     (SalesOrderID,CarrierTrackingNumber,     OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount) VALUES(43659,'4911-403C-98',1,758,1,874,0)


4.

Close Query Window 2 because you can see that the query is blocked. As you have now seen, SERIALIZABLE isolation also prevents the INSERT of new rows into the range of data read by the transaction.

5.

Execute the COMMIT TRAN statement, and then close all query windows.

Dealing with Blocking

As you can tell from the previous example, blocking can be a big issue on multi-user database systems. Minimizing blocking should be a primary concern in transaction design. To minimize blocking, a few rules should be always followed:

  • Keep the transaction as short as possible.

  • Never request user input during a transaction.

  • Consider using row versioning when reading data.

  • Access the least amount of data possible while in a transaction.

  • Use lower isolating transaction levels whenever possible.

If your application doesn't perform as expected and you think that blocking issues could be the cause, it is possible to monitor blocking through Dynamic Management Views (DMVs).

Monitoring Blocking with DMV

Let's use the same example as we used for the REPEATABLE READ isolation level to monitor blocking.

1.

Start SQL Server Management Studio and open a New Query window.

2.

Execute the following batch to start a transaction in REPEATABLE READ mode. The code for this example is included in the sample files as MonitoringBlocking1.sql, MonitoringBlocking2.sql, and MonitoringBlocking3.sql.

USE AdventureWorks; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659


3.

Open a second query window and execute the following UPDATE statement. It will be blocked by the transaction in Query Window 1.

USE AdventureWorks; UPDATE Sales.SalesOrderDetail SET OrderQty = 5 WHERE SalesOrderID = 43659 AND ProductID = 777


4.

Keep the UPDATE statement running and open a third query window.

5.

Enter and execute the following statement in the third query window to retrieve all user processes that have waited for more than five seconds.

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id > 49 AND wait_duration_ms > 5000


With this statement you get information about all user processes with session id's greater than 49 that have been blocked for more than five seconds for any reason. The blocking_session_id column contains the session id of the blocking session. Note the session id in your results for use in step 7.

6.

To get information about what statement the blocked process is trying to execute, the following statement can be used. This statement joins sys.dm_os_waiting_tasks with a view called sys.dm_exec_requests that gives back information about running requests. The statement also uses a function called sys.dm_exec_sql_text() that can retrieve the SQL statement through a handle that is provided in the sys.dm_exec_requests view. Execute the following code:

SELECT (select SUBSTRING(text,statement_start_offset/2,     (case when statement_end_offset = -1 then     len(convert(nvarchar(max), text)) * 2 else     statement_end_offset end -statement_start_offset)/2)     from sys.dm_exec_sql_text(sql_handle)) as query_text FROM     sys.dm_os_waiting_tasks wt JOIN     sys.dm_exec_requests r ON r.session_id = wt.session_id WHERE r.session_id > 50 AND wait_duration_ms > 5000


7.

More information about the connections involved in the blocking scenario can be retrieved using the sys.dm_exec_connections view. Replace the session id in the query (51 in the code below) with the actual session id of your blocking or blocked session.

SELECT * FROM sys.dm_exec_connections WHERE session_id = 51  --Replace with your id


8.

Execute a COMMIT TRAN statement in Query Window 1 and close all query windows.

More Info

For more information about Dynamic Management Views, see the SQL Server Books Online topic "Dynamic Management Views and Functions."


Dealing with Deadlocks

Deadlocks are special blocking scenarios that lead to infinite blocking if not resolved automatically. This can happen when two or more transactions block each other. If this situation happens, every transaction waits for the others to release their locks, but this will not happen because the others are waiting as well. Such a situation is called a deadlock because the transactions will never unlock. In order to prevent this, SQL Server resolves such situations on its own by rolling back one of the transactions and returning an error to the connection in order to let the other transactions end their work.

Producing a Simple Deadlock Scenario

Let us produce a simple deadlock scenario to see how SQL Server manages deadlocks.

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following code to create a small table and insert data in it without closing the transaction. The code for this example is included in the sample files as Deadlock1.sql and Deadlock2.sql.

USE tempdb; CREATE TABLE t1 (i int) BEGIN TRAN INSERT INTO t1 Values(1)


3.

Open a second query window and execute the following statements to create a small table, insert data into it, and try to update table t1 of Query Window 1. The transaction will be blocked because the transaction in Query Window 1 is not yet committed.

USE tempdb; CREATE TABLE t2 (i int) BEGIN TRAN INSERT INTO t2 Values(1) UPDATE t1 SET i = 2


4.

Let the query run and switch back to Query Window 1. Execute the following UPDATE statement to update table t2 and examine what is happening.

UPDATE t2 SET i = 2


After a few seconds, one of the transactions is canceled and an error message is returned, as shown below.

This happened because the transactions blocked each other. The transaction in Query Window 1 had a lock on table t1 and tried to update table t2 and the transaction in Query Window 2 had a lock on table t2 and tried to update table t1. Therefore, both transactions would wait forever for the other to unlock. Such situations are detected by SQL Server and solved by rolling back one of the involved transactions and firing error 1205 to the appropriate connection.

5.

Close all query windows.

Some rules should be followed to prevent and handle deadlocks:

  • Follow the rules to minimize blocking. With less blocking, there is less chance of deadlocking.

  • Always access objects in the same order within your transactions. If both transactions in the example above had accessed the tables in the same order, there would have been no chance for a deadlock. Therefore, an access order list should be defined for all tables in the database.

  • Check for error 1205 within your error handler and resubmit the transaction when the error occurs.

  • Add a procedure to your error handler to log the error details.

When you follow these rules, you have a good chance of preventing deadlocks. When they happen, it is unknown to the users because the transaction is automatically reissued, but it is still possible for you to monitor deadlocking through logging.




Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net