我在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.
我无法理解如何使用可序列化的隔离级别。我认为第一个select应该锁定行/表,并且不会让另一个select获得任何锁。该示例是使用命令对象编写的,但它只是用于测试目的。最初,我使用的是Linq,但我想展示简化的示例。SQL Server事件探查器显示死锁是键锁。我将在几分钟内更新这个问题,并从SQL Server事件探查器发布图表。任何帮助都将不胜感激。我理解这个问题的解决方案可能是在代码中创建关键的部分,但我试图理解为什么序列化隔离级别不能做到这一点。
下面是死锁图:
deadlock http://img7.imageshack.us/img7/9970/deadlock.gif
事先谢谢。