SELECT/UPDATE死锁

Question

SELECT/UPDATE死锁

sql-server-2008deadlocktransactionsisolation

7

我在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 提前感谢您的帮助。

- empi

一个好的问题需要有良好的文档记录！ - Sam Saffron

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sam Saffron · Accepted Answer

仅有可序列化事务是不够的，您需要在锁定上进行提示才能使其正常工作。

可序列化隔离级别通常仍会获取它可以保证满足可序列化条件（可重复读取、无幻影行等）的“最弱”类型的锁。

因此，您正在获取表格的共享锁，稍后（在可序列化事务中）尝试将其升级为更新锁定。如果另一个线程持有共享锁，则升级将失败（如果没有其他人持有共享锁，则会成功）。

您可能希望更改为以下内容：

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

这将确保在执行SELECT时获取更新锁（因此您不需要升级锁定）。