Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
Коллеги, столкнулся со странным (как мне кажется) поведением блокировок. Вкратце задача звучит примерно так:
Есть таблица элементов License и есть таблица тикетов LicenseTicket. Некий алгоритм обрабатывает элементы таблицы License. Когда элемент "взят" алгоритмом на обработку (обработка сложная и может быть довольно длительная) в таблице LicenseTicket делается запись о том, что элемент "зарезервирован" обработчиком. Сделано это для того, чтобы при запуске алгоритма в несколько потоков не было ситуации, когда два обработчика "ухватят" один и тот же элемент. Но это в теории. На практике "резервирование" элементов на обработку делалось так:
INSERT INTO [dbo].[LicenseTicket] (
	[Status]
	,[StartDate]
	,[LicenseId]
	,[Source]
	)
OUTPUT INSERTED.[Id]
	,INSERTED.[Status]
	,INSERTED.[LicenseId]
SELECT TOP 1 @Status
	,GETDATE()
	,l.[Id]
FROM [dbo].[License] l
LEFT JOIN [dbo].[LicenseTicket] m ON m.[LicenseId] = l.[Id]
WHERE m.[Title] IS NULL
	AND l.[Status] <> 4
ORDER BY l.[Parent] ASC


Как показала эта практика, если запускать такой скрипт резервирования из нескольких потоков параллельно то нет никакой гарантии, что один и тот же элемент таблицы License не будет "ухвачен" одновременно двумя потоками. После этого пришла мысль использовать блокировки записей таблицы, уж не знаю верна ли реализация, но сделано было так:

INSERT INTO [dbo].[LicenseTicket] (
	[Status]
	,[StartDate]
	,[LicenseId]
	,[Source]
	)
OUTPUT INSERTED.[Id]
	,INSERTED.[Status]
	,INSERTED.[LicenseId]
SELECT TOP 1 @Status
	,GETDATE()
	,l.[Id]
FROM [dbo].[License] l
WITH (XLOCK, ROWLOCK) -- <-- ключевая строка
LEFT JOIN [dbo].[LicenseTicket] m ON m.[LicenseId] = l.[Id]
WHERE m.[Title] IS NULL
	AND l.[Status] <> 4
ORDER BY l.[Parent] ASC


Суть решения в том, что блокирование выбираемой записи не дает ей "попасться" в лапы параллельных потоков. Написал синтетический тест, запустил из 10 параллельных потоков и все нормально заработало в тестовых условиях. В боевых же условиях таблица License, из которой и делается выборка элементов в нашей системе довольно популярна и различные функциональные блоки делают к ней запросы. И вот оказалось, что при одновременном выполнении вышеуказанного запроса и вот такого запроса:

SELECT TOP 1 
	 [L1].* -- <-- тут на самом деле длинный список полей но я для краткости его убрал
		,CAST(CASE 
		WHEN EXISTS (
				SELECT [L2].[Id]
				FROM [dbo].[License] L2
				WHERE [L2].[Parent] = [L1].[Id]
				)
			THEN 1
		ELSE 0
		END AS BIT) AS [HasAnyChilds]
FROM [dbo].[License] L1
WHERE [L1].[SignatureBinary] IS NULL AND -- empty signature
	  [L1].[STATUS] <> 4 -- no drafts

... мы тут же получаем deadlock. Второй запрос ничего революционного делает а лишь выбирает первую попавшуюся запись с пустым значением SignatureBinary (с некоторыми условиями как видно). После этого в голове совсем все перемешалось (и без того понимание блокировок не идеальное), не могу понять откуда здесь может быть взаимоблокировка. Прошу сообщество помочь советом почему возникает взаимоблокировка и как лучше поступить (догадываюсь, что решение резервирования элементов основанное на блокировках далеко от идеала).
14 авг 15, 23:34    [18020858]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Давайте граф дедлока.
14 авг 15, 23:51    [18020939]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
Сергей с удовольствием, а как его получить?
14 авг 15, 23:55    [18020961]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
Я включал на сервере логгирование по флагу 1222, но разобрать эту информацию квалифицированно не смог.
14 авг 15, 23:56    [18020964]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
delphinchik
не могу понять откуда здесь может быть взаимоблокировка
Первый запрос накладывает X для каждой считываемой строки из License и удерживает ее до конца транзакции.
Второй запрос накладывает S на текущую считываемую строку из License и на все считываемые строки из нее же в предикате exists. Будут ли они удерживаться до конца транзакции или будут освобождены перед чтением следующей строки, зависит от текущего TIL.
S и X несовместимы и порядок считывания строк из таблицы в этих запросах разный. Отсюда и deadlock.
Лечится заменой xlock на updlock.
15 авг 15, 00:38    [18021165]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
invm
delphinchik
не могу понять откуда здесь может быть взаимоблокировка
Первый запрос накладывает X для каждой считываемой строки из License и удерживает ее до конца транзакции ...

А X накладывается ведь только на top 1 в первом запросе?

И еще, если вот в этой части первого запроса:
SELECT TOP 1 @Status
	,GETDATE()
	,l.[Id]
FROM [dbo].[License] l
WITH (XLOCK, ROWLOCK)
LEFT JOIN [dbo].[LicenseTicket] m ON m.[LicenseId] = l.[Id]
WHERE m.[Title] IS NULL
	AND l.[Status] <> 4
ORDER BY l.[Parent] ASC

после левого соединения в наборе записей не остается, то как будет себя вести XLOCK? Спрашиваю потому, что в данном конкретном случае (так совпали обстоятельства) этот подзапрос не возвращает ни одной записи. Это еще больше сбило с толку в понимании природы взаимоблокировки.
Спасибо.
15 авг 15, 01:35    [18021379]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
delphinchik
Сергей с удовольствием, а как его получить?
Например, профайлером.
15 авг 15, 01:45    [18021400]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
delphinchik
А X накладывается ведь только на top 1 в первом запросе?
Почему вы так решили?
Блокировки накладываются на все считываемые в процессе обработки запроса строки таблицы, независимо от того сколько из них попадет в результирующий набор.
А вот сниматься они могут в разное время. Это зависит от текущего уровня изоляции и плана запроса.
15 авг 15, 02:19    [18021439]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
Слушайте, а зачем тут блокировки как таковые? Заведите поле "поюзанности" лицензии и пусть каждый поток пытается инкрементировать его (поле) в момент попытки получить это "ключ"... Инкрементировали, проверили,- если больше одного, то всё- берём другой ключ, а этот ключ "высвобождаем" и декрементируем счётчик. Если же всё нормально, то объявляем лицензию занятой, и работаем с ней.
Чо, не?, - глупость сказал, да?
15 авг 15, 02:38    [18021446]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать выборки одной и той же записи одновременно двумя и более потоками  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
invm,
Спасибо, все решилось именно заменой XLOCK на UPDLOCK.
18 авг 15, 12:44    [18033293]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить