Как я могу заставить транзакции SQL Server использовать блокировки на уровне записи?

У нас есть приложение, которое изначально было написано как настольное приложение, много лет назад. Он запускает транзакцию всякий раз, когда вы открываете экран редактирования, и фиксирует, если вы нажимаете кнопку ОК, или откатывается, если вы нажимаете кнопку Отмена. Это хорошо работает для настольных приложений, но теперь мы пытаемся перейти на ADO.NET и SQL Server, и длительные транзакции проблематичны.

Я обнаружил, что у нас будет проблема, когда все пользователи одновременно пытаются редактировать (разные подмножества) одну и ту же таблицу. В нашей старой базе данных транзакция каждого пользователя получала блокировки на уровне записи для каждой записи, которую они изменили во время своей транзакции; так как разные пользователи редактировали разные записи, у каждого свои блокировки и все работает. Но в SQL Server, как только один пользователь редактирует запись внутри транзакции, SQL Server блокирует всю таблицу. Когда второй пользователь пытается отредактировать другую запись в той же таблице, приложение второго пользователя просто блокируется, потому что SqlConnection блокируется до тех пор, пока первый пользователь не выполнит фиксацию или откат.

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

Как получить транзакции двух разных пользователей в SQL Server для блокировки отдельных записей вместо всей таблицы?

Вот быстрое и грязное консольное приложение, которое иллюстрирует проблему. Я создал базу данных с именем "test1", с одной таблицей под названием "Значения", в которой есть только столбцы ID (int) и Value (nvarchar). Если вы запускаете приложение, оно запрашивает идентификатор для изменения, запускает транзакцию, изменяет эту запись, а затем оставляет транзакцию открытой, пока вы не нажмете ENTER. Я хочу быть в состоянии

  1. запустите программу и скажите ей обновить ID 1;
  2. позвольте ему получить свою транзакцию и изменить запись;
  3. запустите вторую копию программы и скажите ей обновить ID 2;
  4. иметь возможность обновлять (и фиксировать), пока транзакция первого приложения все еще открыта.

В настоящее время он останавливается на шаге 4, пока я не вернусь к первой копии приложения и не закрою его или не нажму ENTER, чтобы он зафиксировал. Вызов command.ExecuteNonQuery блокируется, пока не будет закрыто первое соединение.

public static void Main()
{
    Console.Write("ID to update: ");
    var id = int.Parse(Console.ReadLine());
    Console.WriteLine("Starting transaction");
    using (var scope = new TransactionScope())
    using (var connection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=test1;Integrated Security=True"))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "UPDATE [Values] SET Value = 'Value' WHERE ID = " + id;
        Console.WriteLine("Updating record");
        command.ExecuteNonQuery();
        Console.Write("Press ENTER to end transaction: ");
        Console.ReadLine();
        scope.Complete();
    }
}

Вот некоторые вещи, которые я уже пробовал, без изменений в поведении:

  • Изменение уровня изоляции транзакции на "чтение незафиксировано"
  • Указание "WITH (ROWLOCK)" в операторе UPDATE
Источник

3 ответа

Решение

Просто проверяю, но есть ли у вас первичный ключ или уникальный индекс в столбце идентификатора?

Источник

Вероятно, индекс был создан с блокировками строк, для которых установлено значение "off".
"WITH (ROWLOCK)" в запросе не будет иметь никакого эффекта в этом случае.

Вы можете включить их снова с помощью ALTER INDEX, например:

ALTER INDEX [PK_Values] ON [Values] SET (ALLOW_ROW_LOCKS = ON)
Источник

Посмотрите на оптимистическую и пессимистическую блокировку.

Изменить: Предыдущая статья связана с классической Ado... извините.

http://msdn.microsoft.com/en-us/library/cs6hb8k4(VS.71).aspx

Источник
Другие вопросы по тегам