Эксперимент по блокировке записей UPDLOCK в MS SQL Server

Дано: MS SQL Server 2014.
Цель эксперимента: выяснить реакцию sql-сервера на попытку одновременного доступа на редактирование записи от конкурентных запросов без использования транзакций.
Эксперимент проводился в связи со следующей задачей: есть таблица ваучеров на доступ к сети WiFi. Эти ваучеры выдаются определенным людям по запросу (с предъявлением документа, удостоверяющего личность). Оператор (сотрудник, непосредственно выдающий ваучеры на руки) делает запрос к базе через веб-приложение на выдачу ваучера. Информация о самом ваучере и о том, что он выдан находится в одной таблице. Данный запрос реализуется при помощи следующего алгоритма, состоящего из 2х этапов:

1) SELECT-запрос - запрос на наличие свободного ваучера и получения его идентификатора (ID):

SELECT TOP 1 intID FROM tblVouchers WHERE blnIsGiven = 0

2) UPDATE-запрос - обновление информации о ваучере (указание, что он выдан и когда выдан на базе полученного в предыдущем запросе идентификатора).

UPDATE tblVouchers 
SET blnIsGiven = 1, dtGivenDate = GetDate() 
WHERE intID = @IntID

Проблема: если два оператора одновременно сделают подобный запрос на выдачу ваучера, то может случиться такое, что пока не выполнен UPDATE-запрос ваучер остается условно свободным и может быть случайно выдан два раза (см. эксперимент 1).


Имеется следующая таблица ваучеров:

CREATE TABLE [tblVouchers](
 [intID] [int] IDENTITY(1,1) NOT NULL, -- идентификатор ваучера
 [strNumber] [nvarchar](13) NULL, -- номер ваучера для выдачи
 [dtGivenDate] [smalldatetime] NULL, -- когда выдан
 [blnIsGiven] [bit] NULL CONSTRAINT [DF_tblVauchers_blnIsGiven] 
 DEFAULT ((0)), -- отметка о выдаче
 [test] [nvarchar](250) NULL, -- столбец для тестов
 CONSTRAINT [PK_tblVauchers] PRIMARY KEY CLUSTERED 
([intID] ASC) WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
) ON [PRIMARY]

Для тестов сгенерируем 20 тысяч ваучеров:

declare @xCounter int
select @xCounter = 0
while @xCounter < 20000
begin
Select @xCounter = @xCounter + 1
INSERT INTO tblVouchers
           (strNumber
           , test)
     VALUES
           ('t' + STR(@xCounter, 12, 0)
           ,'')
end

Для эксперимента буду запускать два цикла одновременно, имитируя конкурентные запросы.

Эксперимент 1

Проверочный цикл с отдельным запросом на выборку и последующим обновлением (без принудительных блокировок):

declare @xCounter int
select @xCounter = 0
while @xCounter < 10000
begin
Select @xCounter = @xCounter + 1
declare @xIntID int
SET @xIntID = (SELECT TOP 1 intID 
FROM tblVouchers WHERE blnIsGiven = 0)
WAITFOR DELAY '00:00:00.001'  -- имитация задержки
UPDATE tblVouchers 
SET blnIsGiven = 1, dtGivenDate = GetDate(), test = test + 'A' 
WHERE intID = @xIntID
End

У параллельного цикла вместо A прибавляем B

test = test + 'B'

Результат выполнения обоих циклов:

В итоге видим (по колонке test) , что в большинстве случаев один и тот же ваучер условно был бы выдан два раза (цикл "А" и цикл "Б" получали к нему доступ), причем отметку о выдаче ставил цикл, фигурирующий в колонке test последним.

Эксперимент 2.

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

declare @xCounter int
select @xCounter = 0
while @xCounter < 10000
begin
Select @xCounter = @xCounter + 1
UPDATE tblVouchers
SET    blnIsGiven = 1, dtGivenDate = GetDate(), test = test + 'A'
WHERE  intID =
(
    SELECT TOP 1 intID FROM tblVouchers
    WHERE blnIsGiven = 0
)
End

У параллельного цикла также вместо A прибавляем B

test = test + 'B'

Результат одновременного запуска двух циклов:

Хотя двойных выдач не наблюдалось, однако один из запросов закончился с ошибкой взаимной блокировки deadlock и часть ваучеров оказалась вообще не выдана (второй цикл выдал только 873 ваучера):

Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Эксперимент 3.

Пробую добавить принудительную блокировку записи до ее обновления.
Проверочный цикл с единым запросом на выборку и обновлением с блокировкой UPDLOCK

declare @xCounter int
select @xCounter = 0
while @xCounter < 10000
begin
Select @xCounter = @xCounter + 1
UPDATE tblVouchers
SET    blnIsGiven = 1, dtGivenDate = GetDate(), test = test + 'A'
WHERE  intID =
(
    SELECT TOP 1 intID 
    FROM tblVouchers 
    WITH (UPDLOCK) -- блокировка до обновления
    WHERE blnIsGiven = 0
)
End

У параллельного цикла также вместо A прибавляем B

test = test + 'B'

Результат - ваучеров выдано ровно пополам:

Таким образом блокировка WITH (UPDLOCK) при запросе на выборку заблокировала запись с "условно забронированным" ваучером и не позволила другому запросу получить его.

В итоге для генерации и выдачи ваучера использую следующий запрос с блокировкой:

UPDATE tblVouchers
SET    blnIsGiven = 1, dtGivenDate = GetDate()
OUTPUT INSERTED.intID, INSERTED.strNumber
WHERE  intID =
(
    SELECT TOP 1 intID FROM tblVouchers WITH (UPDLOCK)
    WHERE blnIsGiven = 0
)

(с) Ella S.

Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.