我在SQL Server 2008上的SELECT/UPDATE操作中遇到了死锁问题。
我阅读了这个线程的答案:SQL Server deadlocks between select/update or multiple selects,但我仍然不明白为什么我会遇到死锁。
我已经在以下测试用例中重新创建了这种情况。
我有一个表:
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
我首先尝试从这个表中选择一条记录,如果该记录存在,则将过期时间设置为当前时间加上一定的间隔。可以使用以下代码来完成此操作:
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();
}
}
}
如果我尝试从两个线程执行测试方法并且它们尝试更新相同的记录,那么我会得到以下输出:
[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.
我无法理解在使用Serializable隔离级别时如何发生死锁。我认为第一个选择应该锁定行/表,并且不允许另一个选择获得任何锁。示例是使用命令对象编写的,但仅用于测试目的。最初,我使用linq,但我想展示简化的示例。 Sql Server Profiler显示死锁是关键锁。我将在几分钟内更新问题并发布来自sql server profiler的图形。任何帮助都将不胜感激。我知道解决此问题的方法可能是在代码中创建临界区,但我试图了解为什么Serializable隔离级别行不通。
以下是死锁图: deadlock http://img7.imageshack.us/img7/9970/deadlock.gif 提前感谢您的帮助。