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

问题描述

我在SQL Server 2008的SELECT/UPDATE上遇到死锁问题. 我从以下线程中读取了答案:选择/更新之间的SQL Server死锁或多项选择，但我仍然不明白为什么会陷入僵局.

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发布图表.任何帮助，将不胜感激.我了解解决此问题的方法可能是在代码中创建关键部分，但我试图理解为什么可序列化隔离级别无法解决问题.

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屋！