Как избежать взаимоблокировки между операциями Insert/Delete из-за некластеризованных не уникальных индексов!

Недавно я столкнулся с тупиковым сценарием в OLTP-блоке (Sql server 2005) клиента и обнаружил, что это вызвано двумя хранимыми процедурами, вызываемыми двумя разными потоками.

1, Insert sp, который вставляет данные в таблицу X.

Insert Into X (col1 , col2  , col3  ) 
Values ('value 1' , 'value 2'  , 'value 3'  )

2, Удалить sp, который удаляет данные из таблицы X.

  DELETE X  
  FROM X T1 WITH (NOLOCK)   
  INNER JOIN Y T2 WITH (NOLOCK)
  ON T1.[col2] = T2.[col2]  
   WHERE t2.date < 'date time value'

В таблице X есть один уникальный кластерный первичный ключ и два некластеризованных, не уникальных индекса. Я проанализировал тупик, установив флаг t1222 tace, и результат суммируется ниже;

Вставка sp приобрела блокировку IX для Non-кластеризованного индекса для столбца 1. Delete sp ожидает блокировку X в том же некластеризованном индексе для столбца 1 в течение этого времени.

Delete sp приобрел блокировку U для индекса без кластеризации для столбца 2. Insert sp ожидает блокировку IX в том же некластеризованном индексе для столбца 2 в течение этого времени.

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

ИЗМЕНИТЬ

вывод флага трассировки t1222

deadlock-list  
deadlock victim=process3c77d68  
process-list  
process id=process3c12c58 taskpriority=0 logused=1044 waitresource=PAGE: 17:8:7726 waittime=1250 ownerId=5169682909 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.443 XDES=0xfe64d78b0 lockMode=IX schedulerid=2 kpid=9544 status=suspended spid=219 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:34:03.457 lastbatchcompleted=2011-02-03T03:34:03.453 clientapp=.Net SqlClient Data Provider hostname=HQMTSRV026 hostpid=3308 loginname=EASUser isolationlevel=read committed (2) xactid=5169682909 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056  
executionStack  
frame procname=adhoc line=1 stmtend=296 sqlhandle=0x0200000084ce2a1d0e95a5623fa3a9c0981d422e33cab999  
(@1 int<c/>@2 varchar(8000)<c/>@3 nvarchar(4000))INSERT INTO [VB_Audit_TransactionDetail]([ItemID]<c/>[TransactionID]<c/>[ItemValue]) values(@1<c/>@2<c/>@3)
frame procname=adhoc line=1 stmtend=296 sqlhandle=0x02000000afcb1733f435fb93e13556600acf32bb32e10020
Insert Into VB_Audit_TransactionDetail (ItemID  <c/> TransactionID  <c/> ItemValue  ) Values (4 <c/> '0255978c-f56e-477e-b361-8abe62433cff'  <c/> N'HQOLB006'  )  
frame procname=EAS.dbo.SP_Insert line=13 stmtstart=482 stmtend=522 sqlhandle=0x03001100805efa5997d69400719600000100000000000000  
exec (@CommandText)  
inputbuf  
Proc [Database Id = 17 Object Id = 1509580416]  
process id=process3c77d68 taskpriority=0 logused=364 waitresource=PAGE: 17:6:334008 waittime=1234 ownerId=5169682116 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.053 XDES=0xa8e297cd0 lockMode=X schedulerid=12 kpid=10300 status=suspended spid=327 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:33:41.137 lastbatchcompleted=2011-02-03T03:33:41.133 clientapp=Microsoft SQL Server hostname=HQSSISSRV002 hostpid=7632 loginname=NBKDOM\SQLCSRVC isolationlevel=read committed (2) xactid=5169682116 currentdb=17 lockTimeout=4294967295 clientoption1=671350816 clientoption2=128056  
executionStack  
frame procname=EAS.dbo.PurgeAuditTransactionTables line=59 stmtstart=4202 stmtend=4728 sqlhandle=0x030011006354a2313d11ae00979a00000100000000000000  
DELETE [dbo].[Audit_TransactionDetail]  
FROM [dbo].[Audit_TransactionDetail] T1 WITH (NOLOCK)  
INNER JOIN [dbo].[Audit_NBKTransaction] T2 WITH (NOLOCK)ON T1.[TransactionID] = T2.[TransactionID]  
WHERE TransactionPostedDateTime < @LastReplicationDateTime  
frame procname=adhoc line=1 sqlhandle=0x0100110096968c0560c430ff190000000000000000000000  
EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'  
inputbuf  
EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'  
resource-list  
pagelock fileid=8 pageid=7726 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock4f79500 mode=U associatedObjectId=886415243542528  
owner-list  
owner id=process3c77d68 mode=U  
waiter-list  
waiter id=process3c12c58 mode=IX requestType=wait  
pagelock fileid=6 pageid=334008 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock846afca00 mode=IX associatedObjectId=604940266831872  
owner-list  
owner id=process3c12c58 mode=IX  
waiter-list  
waiter id=process3c77d68 mode=X requestType=wait

Еще одна важная вещь; операторы удаления и вставки всегда касаются двух разных наборов данных.

Ответ 1

Вместо того, чтобы опубликовать описание вашего понимания графа взаимоблокировки, опубликуйте сам тупик. XML, а не растровое изображение графического рендеринга. На первый взгляд, тот факт, что есть конфликт в блокировке IX, предполагает, что происходит эскалация блокировки, что указывает на отсутствие индекса для обслуживания DELETE или попадание точки перехвата индекса в соединении. Но опять же, это просто спекуляция из-за недостаточной информации. Чтобы дать какой-либо значимый ответ, нужен был бы фактический тупиковый XML и точное определение схемы объекта.

После UPDATE

Вы по-прежнему не указали запрашиваемую информацию: точную схему всех задействованных объектов, включая кластерные и все некластеризованные определения индексов. До этого момента начальное подозрение сохраняется: DELETE выполняет сканирование таблицы и перерастает в гранулярность блокировки страниц. Это происходит из-за плохой индексации.

Ваша оценка, что "операторы удаления и вставки всегда касаются двух разных наборов данных" неверна в двух учетных записях:

Когда запрос выполняет сканирование таблицы, он автоматически подразумевает, что он коснется всех данных, не относящихся к тем, какие строки действительно соответствуют
даже в правильно настроенных базах данных, где все операции имеют охватывающие индексы, блокировки хэшируются и хешируют конфликт чаще, чем можно было бы ожидать. Большое сканирование будет конфликтовать с гораздо большим количеством с его собственными отсканированными строками из-за парадокса дня рождения > . См. %% lockres %% вероятностный магия вероятности: 16,777,215.

В качестве побочного примечания таблицы аудита почти всегда необходимо кластеризовать по дате/времени инцидента, потому что все запросы на них запрашивают определенные временные интервалы ( "что произошло между... и..." ) и запросы элементов могут быть удовлетворяется некластеризованным первичным ключом в ID. Очистка записей аудита даже при правильной кластеризации пронизана проблемами производительности и требует пакетной обработки, чтобы избежать взлома журнала. Лучшее решение - развернуть автоматическое скользящее окно с использованием разделов, но это связано с его собственными проблемами.

Ответ 2

Я бы предположил, что таблица T2 должна быть довольно большой. Обозначается ли индекс столбца t2.date? Если нет, то сканирование таблицы на большой таблице может вызвать проблемы. Индексирование этого столбца может оптимизировать удаление, избегая сканирования таблицы. В качестве альтернативы, если индексы на col1 или col2 на самом деле не используются (или используются достаточно), их сброс также может избежать проблемы.

Как часто происходят эти взаимоблокировки? Если они очень редки, может потребоваться многократная работа: оберните каждый оператор в блок try/catch, в случае проверки catch, если ошибка возникла из-за тупиковой ситуации, и если так, повторите команду. Вы также можете использовать SET DEADLOCK_PRIORITY, чтобы выбрать, какой запрос всегда будет выигрывать/проигрывать (но вы должны балансировать это со всеми вызовами в таблице).

О, и оставьте эти WITH (NOLOCK) s. NOLOCK игнорируется вставками, обновлениями и удалениями.