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

Откуда: БОМЖ
Сообщений: 591
Здравствуйте.

Хотелось бы, чтобы мне растолковали, почему возникает deadlock (изредка). Ситуация следующая: в TransactionDocuments валят записи простыми инсертами (данные из формы без каких-либо подзапросов и т.п.). Так вот временами так случается, что не менее простой селект, содержащий
LEFT JOIN TransactionDocuments td ON t.TransactionID = td.TransactionID (TransactionID - первичный ключ для данной таблицы он же кластерный индекс), выбирается жертвой дедлока.

2011-09-22 09:39:52.450	spid17s	    inputbuf
2011-09-22 09:39:52.450	spid17s	Proc [Database Id = 11 Object Id = 1832497707]    
2011-09-22 09:39:52.450	spid17s	   process id=processcfe06748 taskpriority=0 logused=10244 waitresource=PAGE: 21:1:60507 waittime=26027 ownerId=1120280914 transactionname=user_transaction lasttranstarted=2011-09-22T09:39:26.417 XDES=0x22eada3b0 lockMode=IX schedulerid=7 kpid=26240 status=suspended spid=105 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-09-22T09:39:26.423 lastbatchcompleted=2011-09-22T09:39:26.420 clientapp=.Net SqlClient Data Provider hostname=FL2EGREBILLVM01 hostpid=6680 loginname=saveology isolationlevel=read committed (2) xactid=1120280914 currentdb=21 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2011-09-22 09:39:52.450	spid17s	    executionStack
2011-09-22 09:39:52.450	spid17s	     frame procname=TxSupportSquad.dbo.SpSaveTransactionDocument line=12 stmtstart=398 stmtend=1018 sqlhandle=0x03001500fe602a2b9e75b500829d00000100000000000000
2011-09-22 09:39:52.450	spid17s	INSERT INTO [dbo].[TransactionDocuments]
2011-09-22 09:39:52.450	spid17s	           ([TransactionID]
2011-09-22 09:39:52.450	spid17s	           ,[DocumentType]
2011-09-22 09:39:52.450	spid17s	           ,[Version]
2011-09-22 09:39:52.450	spid17s	           ,[Content]
2011-09-22 09:39:52.450	spid17s	           ,[TimeStamp])
2011-09-22 09:39:52.450	spid17s	     VALUES
2011-09-22 09:39:52.450	spid17s	           (@TransactionID
2011-09-22 09:39:52.450	spid17s	           ,@DocumentType
2011-09-22 09:39:52.450	spid17s	           ,@Version
2011-09-22 09:39:52.450	spid17s	           ,@Content
2011-09-22 09:39:52.450	spid17s	           ,@TimeStamp)     
2011-09-22 09:39:52.450	spid17s	    inputbuf
2011-09-22 09:39:52.450	spid17s	Proc [Database Id = 21 Object Id = 724197630]    
2011-09-22 09:39:52.450	spid17s	  resource-list
2011-09-22 09:39:52.450	spid17s	   pagelock fileid=1 pageid=285326 dbid=21 objectname=TxSupportSquad.dbo.TransactionDocuments id=lock23a708b80 mode=IX associatedObjectId=72057594045530112
2011-09-22 09:39:52.450	spid17s	    owner-list
2011-09-22 09:39:52.450	spid17s	     owner id=processcfe06748 mode=IX
2011-09-22 09:39:52.450	spid17s	    waiter-list
2011-09-22 09:39:52.450	spid17s	     waiter id=processe128ce08 mode=S requestType=wait
2011-09-22 09:39:52.450	spid17s	   pagelock fileid=1 pageid=285326 dbid=21 objectname=TxSupportSquad.dbo.TransactionDocuments id=lock23a708b80 mode=IX associatedObjectId=72057594045530112
2011-09-22 09:39:52.450	spid17s	    owner-list
2011-09-22 09:39:52.450	spid17s	    waiter-list
2011-09-22 09:39:52.450	spid17s	     waiter id=processf0405b88 mode=S requestType=wait
2011-09-22 09:39:52.450	spid17s	   pagelock fileid=1 pageid=285326 dbid=21 objectname=TxSupportSquad.dbo.TransactionDocuments id=lock23a708b80 mode=IX associatedObjectId=72057594045530112
2011-09-22 09:39:52.450	spid17s	    owner-list
2011-09-22 09:39:52.450	spid17s	    waiter-list
2011-09-22 09:39:52.450	spid17s	     waiter id=processf0405048 mode=S requestType=wait
2011-09-22 09:39:52.450	spid17s	   pagelock fileid=1 pageid=285326 dbid=21 objectname=TxSupportSquad.dbo.TransactionDocuments id=lock23a708b80 mode=IX associatedObjectId=72057594045530112
2011-09-22 09:39:52.450	spid17s	    owner-list
2011-09-22 09:39:52.450	spid17s	    waiter-list
2011-09-22 09:39:52.450	spid17s	     waiter id=process6821b88 mode=S requestType=wait
2011-09-22 09:39:52.450	spid17s	   pagelock fileid=1 pageid=60507 dbid=21 objectname=TxSupportSquad.dbo.TransactionDocuments id=lock1d3429c80 mode=S associatedObjectId=72057594045530112
2011-09-22 09:39:52.450	spid17s	    owner-list
2011-09-22 09:39:52.450	spid17s	     owner id=processf0405048 mode=S
2011-09-22 09:39:52.450	spid17s	    waiter-list
2011-09-22 09:39:52.450	spid17s	     waiter id=processcfe06748 mode=IX requestType=wait
2011-09-22 09:39:57.450	spid26s	deadlock-list
2011-09-22 09:39:57.450	spid26s	 deadlock victim=processf0405b88
2011-09-22 09:39:57.450	spid26s	  process-list
2011-09-22 09:39:57.450	spid26s	   process id=processf0405b88 taskpriority=0 logused=0 waitresource=PAGE: 21:1:285326 waittime=8295 ownerId=1120270248 transactionname=INSERT lasttranstarted=2011-09-22T09:39:00.250 XDES=0x9b906b40 lockMode=S schedulerid=4 kpid=29744 status=suspended spid=98 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-09-22T09:20:55.247 lastbatchcompleted=2011-09-22T09:20:55.247 clientapp=.Net SqlClient Data Provider hostname=FL2EGREBILLVM04 hostpid=4904 loginname=saveology isolationlevel=read committed (2) xactid=1120270248 currentdb=11 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2011-09-22 09:39:57.450	spid26s	    executionStack
2011-09-22 09:39:57.450	spid26s	     frame procname=Reports.dbo.SpReport_GetCCTransactionsGeneric line=351 stmtstart=26788 stmtend=65790 sqlhandle=0x03000b002bb2396dc3d53500669f00000100000000000000
2011-09-22 09:39:57.450	spid26s	INSERT INTO #tempcctxdata
2011-09-22 09:39:57.450	spid26s	 (
2011-09-22 09:39:57.450	spid26s	  TransactionDate,
2011-09-22 09:39:57.450	spid26s	  GroupTxDate,
2011-09-22 09:39:57.450	spid26s	  AccountId,
2011-09-22 09:39:57.450	spid26s	  SubAccountId,
2011-09-22 09:39:57.450	spid26s	  TransactionId,
2011-09-22 09:39:57.450	spid26s	  TxGroupId,
2011-09-22 09:39:57.450	spid26s	  OperationTypeID,
2011-09-22 09:39:57.450	spid26s	  OperationTypeEnum,
2011-09-22 09:39:57.450	spid26s	  PaymentTypeID,
2011-09-22 09:39:57.450	spid26s	  PaymentTypeEnum,
2011-09-22 09:39:57.450	spid26s	  RequestTypeID,
2011-09-22 09:39:57.450	spid26s	  RequestTypeEnum,
2011-09-22 09:39:57.450	spid26s	  InstructionTypeID,
2011-09-22 09:39:57.450	spid26s	  InstructionTypeEnum,
2011-09-22 09:39:57.450	spid26s	  PaymentMethodID,
2011-09-22 09:39:57.450	spid26s	  PaymentMethodIDEnum,
2011-09-22 09:39:57.450	spid26s	  Amount,
2011-09-22 09:39:57.450	spid26s	  InitialPreAuthAmount,
2011-09-22 09:39:57.450	spid26s	  CurrencyTypeEnum,
2011-09-22 09:39:57.450	spid26s	  CHFirstName,
2011-09-22 09:39:57.450	spid26s	  CHLastName,
2011-09-22 09:39:57.450	spid26s	  CountryCode,
2011-09-22 09:39:57.450	spid26s	  CountryName,
2011-09-22 09:39:57.450	spid26s	  CCExpirationDate,
2011-09-22 09:39:57.450	spid26s	  PublicCCNumber,
2011-09-22 09:39:57.450	spid26s	  ClearingAuthorizationNumber,
2011-09-22 09:39:57.450	spid19s	deadlock-list
2011-09-22 09:39:57.450	spid19s	 deadlock victim=processf0405048
2011-09-22 09:39:57.450	spid19s	  process-list
2011-09-22 09:39:57.450	spid19s	   process id=processf0405b88 taskpriority=0 logused=0 waitresource=PAGE: 21:1:285326 waittime=8295 ownerId=1120270248 transactionname=INSERT lasttranstarted=2011-09-22T09:39:00.250 XDES=0x9b906b40 lockMode=S schedulerid=4 kpid=29744 status=suspended spid=98 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-09-22T09:20:55.247 lastbatchcompleted=2011-09-22T09:20:55.247 clientapp=.Net SqlClient Data Provider hostname=FL2EGREBILLVM04 hostpid=4904 loginname=saveology isolationlevel=read committed (2) xactid=1120270248 currentdb=11 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2011-09-22 09:39:57.450	spid19s	    executionStack
6 окт 11, 01:28    [11392163]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
aleks2
Guest
Ну и чо там непонятного?

Insert последовательно блокирует страницы под вставку: X-блокировка.
JOIN последовательно блокирует страницы под чтение: S-блокировка.

Insert блокировал
pageid=285326
и хочет блокировать (ждет)
pageid=60507

JOIN блокировал
pageid=60507
и хочет блокировать (ждет)
pageid=285326


Варианты борьбы:
1. Тривиальный: блокировать всю таблицу в Insert WITH(tablock).
2. Простой и незатейливый: SHAPSHOT ISOLATION LEVEL для JOIN.
3. Экономный: уменьшить размер данных Insert, чтоб блокировалось не более одной страницы за раз и выполнять не более одного Insert в транзакции.
6 окт 11, 06:08    [11392265]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
aleks2
2. Простой и незатейливый: SHAPSHOT ISOLATION LEVEL для JOIN.


Зачем так жестко. ;) Достаточно просто включить опцию бд READ_COMMITED_SNAPSHOT.
6 окт 11, 09:02    [11392460]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
aleks2
Варианты борьбы:
1. Тривиальный: блокировать всю таблицу в Insert WITH(tablock).
2. Простой и незатейливый: SHAPSHOT ISOLATION LEVEL для JOIN.
3. Экономный: уменьшить размер данных Insert, чтоб блокировалось не более одной страницы за раз и выполнять не более одного Insert в транзакции.
И ещё:
4. Обрабатывать ошибку дедлока и повторять операцию.
6 окт 11, 09:09    [11392485]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
Очень лысый
Member

Откуда: БОМЖ
Сообщений: 591
aleks2
Ну и чо там непонятного?

Insert последовательно блокирует страницы под вставку: X-блокировка.
JOIN последовательно блокирует страницы под чтение: S-блокировка.

Insert блокировал
pageid=285326
и хочет блокировать (ждет)
pageid=60507

JOIN блокировал
pageid=60507
и хочет блокировать (ждет)
pageid=285326


Ну эт я как бы понял умозрительно. Хотелось больше деталей. Почему машинка себя так ведёт, почему insert с селектом дерутся за одну и ту же страницу? Я всё же недостаточно хорошо знаю работу ядра, чтобы сам на эти вопросы ответить.
6 окт 11, 11:18    [11393226]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
Очень лысый
Member

Откуда: БОМЖ
Сообщений: 591
Да и ещё вопрос insert with(rowlock) не может спасти отца русской демократии?
6 окт 11, 11:19    [11393235]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
aleks2
Guest
Очень лысый
Да и ещё вопрос insert with(rowlock) не может спасти отца русской демократии?


Ви, батенька, на роль отца не канаете. Тока-тока на роль чада-недоучки.

Как вам сервер заблокирует ROW, если ROW ишо нету?
А шоб вставить, фсяко, надо страницу блокировать... а то и не одну (если вставка задевает кластерный индекс).
6 окт 11, 11:55    [11393570]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
Очень лысый
Member

Откуда: БОМЖ
Сообщений: 591
aleks2
Очень лысый
Да и ещё вопрос insert with(rowlock) не может спасти отца русской демократии?


Ви, батенька, на роль отца не канаете. Тока-тока на роль чада-недоучки.

Что делать, издержки извилистого трудового пути и врождённого маразма. Однако учусь потихоньку.
6 окт 11, 12:09    [11393696]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2,

не горячись. Сервер прекрасно блокирует "ROW, которой еще нету", и по RID, и по KEY. Другое дело, что ROWLOCK тут врядли поможет: если выбираемые множества все равно пересекаются - какая разница, пересекуться они на уровне строк или на уровне страниц?
6 окт 11, 12:32    [11393859]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
iljy
Member

Откуда:
Сообщений: 8711
iljy,

вернее именно несуществующую блокирует только по KEY, если кластерного индекса нет - тогда только страничная блокировка.
6 окт 11, 12:38    [11393899]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock insert+select  [new]
1
Guest
А почему не Select with (rowlock) ?
2 ноя 11, 17:57    [11541879]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить