Тупик при ВЫБОР / ОБНОВЛЕНИЕ

Question

У меня проблема с тупиком при SELECT/UPDATE на SQL Server 2008. Я читаю ответы из этой темы: взаимоблокировки SQL Server между выбором / обновлением или множественным выбором, но я до сих пор не понимаю, почему я получаю взаимоблокировку.

Я воссоздал ситуацию в следующем тестовом примере.

У меня есть стол:

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 Isolation Level. Я думаю, что первый выбор должен заблокировать строку / таблицу и не позволит другому выбрать для получения каких-либо блокировок. Пример написан с использованием командных объектов, но он только для целей тестирования. Первоначально я использую linq, но я хотел показать упрощенный пример. Sql Server Profiler показывает, что взаимоблокировка - это блокировка ключа. Я обновлю вопрос через несколько минут и опубликую график с сервера профилирования SQL. Любая помощь будет оценена. Я понимаю, что решением этой проблемы может быть создание критического раздела в коде, но я пытаюсь понять, почему Serializable Isolation Level не работает.

А вот график взаимоблокировки: http://img7.imageshack.us/img7/9970/deadlock.gif

Заранее спасибо.

Answer 1

Недостаточно иметь сериализуемую транзакцию, вам нужно намечь на блокировку, чтобы это работало.

Сериализуемый уровень изоляции по-прежнему обычно приобретает "самый слабый" тип блокировки, который он может обеспечить, что обеспечивает выполнение условий сериализации (повторяющиеся чтения, отсутствие фантомных строк и т. Д.)

Итак, вы захватываете общую блокировку на своей таблице, которую вы позже (в своей сериализуемой транзакции) пытаетесь обновить до блокировки обновления. Обновление не будет выполнено, если другой поток удерживает общую блокировку (это будет работать, если нет тела, в котором он удерживал общую блокировку).

Вы, вероятно, хотите изменить его на следующее:

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

Это обеспечит получение блокировки обновления при выполнении SELECT (поэтому вам не нужно будет обновлять блокировку).