SELECT/UPDATE 上的死锁 [英] Deadlock on SELECT/UPDATE

问题描述

我在 SQL Server 2008 上的 SELECT/UPDATE 上遇到死锁问题.我从这个线程中阅读了答案:SQL Server deadlocks between select/update或多选，但我仍然不明白为什么我会陷入僵局.

I'm having a problem with deadlock on SELECT/UPDATE on SQL Server 2008. I read answers from this thread: SQL Server deadlocks between select/update or multiple selects but I still don't understand why I get deadlock.

我在以下测试用例中重现了这种情况.

I have recreated the situation in the following testcase.

我有一张桌子:

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'm trying first to select a record from this table and if the record exists set expiration time to current time plus some interval. It is accomplished using 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 test method from two threads and they try to update same record I get 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.

我无法理解使用可序列化隔离级别是如何发生的.我认为第一个选择应该锁定行/表并且不会让另一个选择获得任何锁定.该示例是使用命令对象编写的，但仅用于测试目的.最初，我使用的是 linq，但我想展示一个简化的例子.Sql Server Profiler 显示死锁是键锁.我将在几分钟内更新问题并从 sql server profiler 发布图表.任何帮助，将不胜感激.我知道这个问题的解决方案可能是在代码中创建临界区，但我试图理解为什么 Serializable Isolation Level 不起作用.

I can't understand how it can happen using Serializable Isolation Level. I think first select should lock row/table and won't let another select to obtain any locks. The example is written using command objects but it's just for test purposes. Originally, i'm using linq but I wanted to show simplified example. Sql Server Profiler shows that deadlock is key lock. I will update the question in few minutes and post graph from sql server profiler. Any help would be appreciated. I understand that solution for this problem may be creating critical section in code but I'm trying to understand why Serializable Isolation Level doesn't do the trick.

这是死锁图:死锁 http://img7.imageshack.us/img7/9970/deadlock.gif

提前致谢.

推荐答案

仅仅有一个可序列化的事务是不够的，您需要提示锁定才能使其工作.

Its not enough to have a serializable transaction you need to hint on the locking for this to work.

可序列化隔离级别通常仍会获取它所能获得的最弱"类型的锁，以确保满足可序列化条件(可重复读取、无幻像行等)

The serializable isolation level will still usually acquire the "weakest" type of lock it can which ensures the serializable conditions are met (repeatable reads, no phantom rows etc)

因此，您正在获取表上的共享锁，稍后(在您的可序列化事务中)尝试升级到 更新锁. 如果另一个线程持有共享锁，升级将失败(如果没有其他主体持有共享锁，它将工作).

So, you are grabbing a shared lock on your table which you are later (in your serializable transaction) trying to upgrade to an update lock. The upgrade will fail if another thread is holding the shared lock (it will work if no body else it holding a shared lock).

您可能希望将其更改为以下内容:

You probably want to change it to the following:

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

这将确保在执行 SELECT 时获取更新锁(因此您无需升级锁).

That will ensure an update lock is acquired when the SELECT is performed (so you will not need to upgrade the lock).

这篇关于SELECT/UPDATE 上的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！