Я тестирую процесс, который удаляет очень много записей одновременно. Он не может TRUNCATE TABLE
, потому что там есть записи, которые необходимо сохранить.
Из-за большого объема я разбил удаление на цикл, подобный следующему:
-- Do not block if records are locked.
SET LOCK_TIMEOUT 0
-- This process should be chosen as a deadlock victim in the case of a deadlock.
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT ON
DECLARE @Count
SET @Count = 1
WHILE @Count > 0
BEGIN TRY
BEGIN TRANSACTION -- added per comment below
DELETE TOP (1000) FROM MyTable WITH (ROWLOCK, READPAST) WHERE MyField = SomeValue
SET @Count == @@ROWCOUNT
COMMIT
END TRY
BEGIN CATCH
exec sp_lock -- added to display the open locks after the timeout
exec sp_who2 -- shows the active processes
IF @@TRANCOUNT > 0
ROLLBACK
RETURN -- ignoring this error for brevity
END CATCH
MyTable - это кластерная таблица . MyField находится в первом столбце кластерного индекса. Он указывает на логическую группировку записей, поэтому MyField = SomeValue
часто выбирает много записей. Меня не волнует, в каком порядке они удаляются, если одновременно обрабатывается одна группа. В этой таблице нет других индексов.
Я добавил подсказку ROWLOCK
, чтобы попытаться избежать эскалации блокировки, которую мы наблюдали в производственной среде. Я добавил подсказку READPAST
, чтобы избежать удаления записей, заблокированных другими процессами. Этого никогда не должно происходить, но я стараюсь обезопасить себя.
Проблема: иногда этот цикл достигает тайм-аута блокировки 1222 «Превышен тайм-аут запроса блокировки», когда это единственное, что работает.
Я уверен, что есть никаких других действий в этой системе, пока я тестирую этот процесс, потому что это мой собственный ящик разработчика, никто другой не подключен, на нем нет других запущенных процессов, и профилировщик не проявляет активности.
Я могу повторно запустить тот же сценарий через секунду, и он продолжит работу с того места, на котором остановился, успешно удаляя записи - до тех пор, пока следующий тайм-аут блокировки.
Я пробовал BEGIN TRY
/ BEGIN CATCH
, чтобы проигнорировать ошибку 1222 и повторить удаление, но это сразу же не удается с той же ошибкой тайм-аута блокировки . Он также снова не работает, если я добавляю небольшую задержку перед повторной попыткой.
Я предполагаю, что тайм-ауты блокировки вызваны чем-то вроде разделения страницы, но я не уверен, почему это может конфликтовать с текущей итерацией цикла. Предыдущий оператор удаления должен был быть уже завершен, и я подумал, что это означает, что все разделы страницы также были завершены.
Почему цикл DELETE достигает тайм-аута блокировки самого себя?
Есть ли способ избежать этого тайм-аута блокировки или определить, что его можно возобновить безопасно?
Это на SQL Server 2005.
] - РЕДАКТИРОВАТЬ -
Я добавил событие Lock: Timeout в профилировщик. Время ожидания PAGELOCK истекает во время удаления:
Event Class: Lock:Timeout
TextData: 1:15634 (one example of several)
Mode: 7 - IU
Type: 6 - PAGE
DBCC PAGE сообщает, что эти страницы находятся вне диапазона основной базы данных (ID 1).
- EDIT 2 -
Я добавил BEGIN TRY
/ BEGIN CATCH
и запустил exec sp_lock
в блоке catch. Вот что я увидел:
spid dbid ObjId IndId Type Resource Mode Status
19 2 1401108082 1 PAG 1:52841 X GRANT (tempdb.dbo.MyTable)
19 2 1401108082 0 TAB IX GRANT (tempdb.dbo.MyTable)
Me 2 1401108082 0 TAB IX GRANT (tempdb.dbo.MyTable)
Me 1 1115151018 0 TAB IS GRANT (master..spt_values) (?)
SPID 19 - это МЕНЕДЖЕР ЗАДАЧ SQL Server. Зачем одному из этих диспетчеров задач требовать блокировки MyTable?