Блог

У меня проблема с SQL-сервером, где я меняю запросы на многопоточные, и он выполняет эту хранимую процедуру при нескольких подключениях к базе данных и приводит к взаимоблокировке. Я не могу себе представить, как это приводит к взаимоблокировке. Если я делаю это однопоточным, он работает нормально. В какой-то момент (когда я впервые написал это) он работал, вставляя 22 000 записей без взаимоблокировки (таблица была пустой, поэтому никаких обновлений в этой ситуации не было) Код выглядит следующим образом:

 Create Procedure InsertUpdateRecord
  @Email varchar(255),
  @State Varchar(50)   -- Other parameters, shouldn't matter
as
 Begin
 if (not exists (select * from [MyTable] WITH (NOLOCK) where email=@email))
     begin
     insert into [MyTable] (email, state) values (@email, @state)
     end
 else
     begin
     update [MyTable] set state=@state where email=@email
     end
 end
 end
  

Довольно просто, и я не вижу никакого способа, которым это могло бы быть сделано, кроме простой блокировки строк в уникальном столбце «email», и я не вижу никакого способа, которым это могло бы привести к тупиковой ситуации. Может кто-нибудь объяснить это и предложить решение?

Измените эту строку кода с

 if exists (select * from table where key = @key)
  

Для

 if exists (select 1 from table where key = @key)
  

Это более эффективно

Также следуйте этим распространенным методам, которые помогают минимизировать взаимоблокировки: доступ к объектам в том же порядке. Избегайте взаимодействия с пользователем в транзакциях. Держите транзакции короткими и в одном пакете. Используйте более низкий уровень изоляции. Используйте уровень изоляции на основе управления версиями строк. Используйте связанные соединения.

Как подробно описано здесь: минимизировать взаимоблокировки

Это типичный случай, который вы должны учитывать UPDLOCK в своем запросе:

 begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran
  

Уже UPDLOCK будет требовать блокировки, если вы нажмете на свою запись с помощью первого выбора и избежите случая, когда несколько методов пытаются получить одну и ту же блокировку.
Используется serializable для:

• Операторы не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями.
• Никакие другие транзакции не могут изменять данные, которые были прочитаны текущей транзакцией, пока текущая транзакция не завершится.
• Другие транзакции не могут вставлять новые строки со значениями ключей, которые попадают в диапазон ключей, считываемых любыми операторами в текущей транзакции, до завершения текущей транзакции.

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

Кроме того, если нет, попробуйте добавить индекс в свой MyTable :

 CREATE NONCLUSTERED INDEX [IX_MyTable_email] ON [dbo].[MyTable]
(
    [email] ASC
)
  

Вы также можете продолжить и включить следующие флаги трассировки:

 DBCC TRACEON (1204,-1) 

DBCC TRACEON (1222,-1) 
  

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