Deadlock in SELECT / UPDATE

Question

I had a problem with a deadlock in SELECT / UPDATE on SQL Server 2008. I read the answers from this topic: SQL Server deadlocks between select / update or multiple selects , but I still don't understand why I got into a dead end.

I recovered the situation in the following test file.

I have a table:

CREATE TABLE [dbo].[SessionTest]( [SessionId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL, [ExpirationTime] DATETIME NOT NULL, CONSTRAINT [PK_SessionTest] PRIMARY KEY CLUSTERED ( [SessionId] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[SessionTest] ADD CONSTRAINT [DF_SessionTest_SessionId] DEFAULT (NEWID()) FOR [SessionId] GO 

I try to select the record from this table first, and if the record has a set expiration time to the current time plus some interval. This is done using the following code:

 protected Guid? GetSessionById(Guid sessionId, SqlConnection connection, SqlTransaction transaction) { Logger.LogInfo("Getting session by id"); using (SqlCommand command = new SqlCommand()) { command.CommandText = "SELECT * FROM SessionTest WHERE SessionId = @SessionId"; command.Connection = connection; command.Transaction = transaction; command.Parameters.Add(new SqlParameter("@SessionId", sessionId)); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { Logger.LogInfo("Got it"); return (Guid)reader["SessionId"]; } else { return null; } } } } protected int UpdateSession(Guid sessionId, SqlConnection connection, SqlTransaction transaction) { Logger.LogInfo("Updating session"); using (SqlCommand command = new SqlCommand()) { command.CommandText = "UPDATE SessionTest SET ExpirationTime = @ExpirationTime WHERE SessionId = @SessionId"; command.Connection = connection; command.Transaction = transaction; command.Parameters.Add(new SqlParameter("@ExpirationTime", DateTime.Now.AddMinutes(20))); command.Parameters.Add(new SqlParameter("@SessionId", sessionId)); int result = command.ExecuteNonQuery(); Logger.LogInfo("Updated"); return result; } } public void UpdateSessionTest(Guid sessionId) { using (SqlConnection connection = GetConnection()) { using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable)) { if (GetSessionById(sessionId, connection, transaction) != null) { Thread.Sleep(1000); UpdateSession(sessionId, connection, transaction); } transaction.Commit(); } } } 

Then, if I try to execute a test method from two threads, and they try to update the same record, I get the following output:

 [4] : Creating/updating session [3] : Creating/updating session [3] : Getting session by id [3] : Got it [4] : Getting session by id [4] : Got it [3] : Updating session [4] : Updating session [3] : Updated [4] : Exception: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

I cannot figure out how this can happen using the Serializable Isolation Level. I think select must block the row / table first and not let the other select to get any locks. The example is written using command objects, but it is intended for testing only. I originally use linq, but I wanted to show a simplified example. Sql Server Profiler shows that deadlock is key lock. I will update the question in a few minutes and send the graph from the sql server profiler. Any help would be greatly appreciated. I understand that solving this problem can create a critical section of code, but I'm trying to understand why the Serializable Isolation Level does not do the trick.

And here is the deadlock graph: deadlock http://img7.imageshack.us/img7/9970/deadlock.gif

Thanks in advance.

Answer 1

It is not enough to have a serializable transaction for which you need to hint at a lock in order for this to work.

A serializable isolation level usually still takes on the “weakest” type of lock that can ensure serializable conditions (repeated values, phantom strings, etc.)

So, you capture the general lock on your table, which you later (in your serializable transaction) try to switch to the update lock. The update will fail if another thread holds a common lock (it will work if none of them holds a common lock).

You probably want to change it to the following:

 SELECT * FROM SessionTest with (updlock) WHERE SessionId = @SessionId 

This will ensure that the update is committed when performing SELECT (so you do not need to update the lock).