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

Откуда:
Сообщений: 7
Доброго времени суток, господа.
Предлагаю вашему вниманию следующую задачу.
Одновременно открываются несколько транзакций, производящих одинаковую последовательность действий. Если в обработку поступают 10-20 транзакций (запросов), то все ОК, никаких блокировок. Если количество транзакций превышает 100, получаем 4-5 deadlock`ов.
Сперва приведу граф блокировки:
<deadlock-list>
 <deadlock victim="process5e4f288">
  <process-list>
   <process id="process5e4f288" taskpriority="0" logused="7398728" 
            waitresource="KEY: 7:72057685052030976 (6a71fe733cd1)" 
            waittime="2673" ownerId="90920542" transactionname="user_transaction" 
            lasttranstarted="2014-10-02T17:23:22.337" XDES="0x1d8ad43b0" lockMode="S" 
            schedulerid="8" kpid="8020" status="suspended" spid="129" sbid="0" ecid="0" 
			priority="0" trancount="2" lastbatchstarted="2014-10-02T17:23:22.357" 
			lastbatchcompleted="2014-10-02T17:23:22.337" clientapp=".Net SqlClient Data Provider" 
			hostname="HOSTNAME" hostpid="4120" loginname="HOSTNAME\Service1" 
			isolationlevel="read committed (2)" xactid="90920542" 
			currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="Service.schema.CalculateRequestSTMCE" 
            line="2298" stmtstart="235612" stmtend="236666" 
            sqlhandle="0x03000700d006c22908c8ad00b1a300000100000000000000">
INSERT INTO [schema].[RequestMembershipCO] WITH (ROWLOCK)
        (
            [RequestKey],
            [MembershipConditionKey],
            [ObjectKey],
            [TrueStatementCount],
            [Iteration]
        )
        SELECT
            @requestKey,
            [cache].[MembershipConditionKey],
            [cache].[ObjectKey],
            [cache].[TrueStatementCount],
            [cache].[Iteration]
        FROM #calculateRSTECRC AS [cache];     </frame>
	  </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 985106600]    </inputbuf>
   </process>
   <process id="process5e3b708" taskpriority="0" logused="7403676" 
			waitresource="KEY: 7:72057685052030976 (6a71fe733cd1)" waittime="2673" 
			ownerId="90953226" transactionname="user_transaction" lasttranstarted="2014-10-02T17:23:26.947" 
			XDES="0x1e81ece80" lockMode="S" schedulerid="6" kpid="4972" status="suspended" spid="136" 
			sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-02T17:23:26.950" 
			lastbatchcompleted="2014-10-02T17:23:26.947" clientapp=".Net SqlClient Data Provider"
			hostname="HOSTNAME" hostpid="4120" loginname="HOSTNAME\Service1"
			isolationlevel="read committed (2)" xactid="90953226" currentdb="7" 
			lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
	   <executionStack>
		   <frame procname="Service.schema.CalculateRequestSTMCE" 
				  line="2298" stmtstart="235612" stmtend="236666"
				  sqlhandle="0x03000700d006c22908c8ad00b1a300000100000000000000">
			   INSERT INTO [schema].[RequestMembershipCO] WITH (ROWLOCK)
			   (
			   [RequestKey],
			   [MembershipConditionKey],
			   [ObjectKey],
			   [TrueStatementCount],
			   [Iteration]
			   )
			   SELECT
			   @requestKey,
			   [cache].[MembershipConditionKey],
			   [cache].[ObjectKey],
			   [cache].[TrueStatementCount],
			   [cache].[Iteration]
			   FROM #calculateRSTECRC AS [cache];
		   </frame>
    <inputbuf>
Proc [Database Id = 7 Object Id = 985106600]    </inputbuf>
   </process>
   <process id="process5e4fb88" taskpriority="0" logused="8785324" 
			waitresource="KEY: 7:72057685052030976 (a2c5a80da396)" waittime="2674" ownerId="90897465" 
			transactionname="user_transaction" lasttranstarted="2014-10-02T17:23:18.673" XDES="0x1191dce80" 
			lockMode="S" schedulerid="8" kpid="4836" status="suspended" spid="100" sbid="0" ecid="0" priority="0" 
			trancount="2" lastbatchstarted="2014-10-02T17:23:18.683" lastbatchcompleted="2014-10-02T17:23:18.673" 
			clientapp=".Net SqlClient Data Provider"
			hostname="HOSTNAME" hostpid="4120" loginname="HOSTNAME\Service1"
			isolationlevel="read committed (2)" xactid="90897465" currentdb="7" 
			lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="Service.schema.CalculateRequestSTMCE" 
			line="2298" stmtstart="235612" stmtend="236666" 
			sqlhandle="0x03000700d006c22908c8ad00b1a300000100000000000000">
		 INSERT INTO [schema].[RequestMembershipCO] WITH (ROWLOCK)
		 (
		 [RequestKey],
		 [MembershipConditionKey],
		 [ObjectKey],
		 [TrueStatementCount],
		 [Iteration]
		 )
		 SELECT
		 @requestKey,
		 [cache].[MembershipConditionKey],
		 [cache].[ObjectKey],
		 [cache].[TrueStatementCount],
		 [cache].[Iteration]
		 FROM #calculateRSTECRC AS [cache];
	 </frame>
      </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 985106600]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057685052030976" dbid="7" objectname="Service.schema.Request" indexname="PK_Request" id="lock1658aa980" mode="X" associatedObjectId="72057685052030976">
    <owner-list/>
    <waiter-list>
     <waiter id="process5e4f288" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057685052030976" dbid="7" objectname="Service.schema.Request" indexname="PK_Request" id="lock1658aa980" mode="X" associatedObjectId="72057685052030976">
    <owner-list>
     <owner id="process5e4fb88" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5e3b708" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057685052030976" dbid="7" objectname="Service.schema.Request" indexname="PK_Request" id="lock1d8a46080" mode="X" associatedObjectId="72057685052030976">
    <owner-list>
     <owner id="process5e4f288" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5e4fb88" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>


Можно увидеть 3 процесса получившие блокировки на индекс PK_Request.
Согласно графу якобы блокировку вызывает INSERT в таблицу, имеющую в качестве внешнего ключа поле, на котором как раз и построен блокируемый индекс.
Также следует отметить, что транзакция вызывается за несколько вызовов процедур до искомого INSERT`а.

Можно увидеть, что уровень изоляции транзакции read committed, однако в настройках базы первым делом я указал:
ALTER DATABASE Service SET ALLOW_SNAPSHOT_ISOLATION ON 
ALTER DATABASE Service SET READ_COMMITTED_SNAPSHOT ON


Также, в качестве превинтивной меры построил некластеризованный индекс на поле с блокировками, т.е. сделал некластеризованного товарища PK_Request. Хотелось добавить разнообразия при работе с внешним ключем, дабы не только кластеризованный индекс участвовал. От сией операции ожидал увидеть Index Seek (non-clustered) однако, план выполнения запроса на вставку не изменился:
Картинка с другого сайта.

Вопросы к знатокам:
1. Должен ли в графе быть указан уровень изоляции READ COMMITED SNAPSHOT если данная опция включена?
2. Возможно ли, что X блокировка индекса была вызвана ранее в цепочке хранимых процедур а в графе видно только команду, попытавшуюся получить S блокировку?
3. Как проверить, что Execution Plan учитывает присутствие нового некластеризованного индекса?
4. Что бы вы посоветовали для дальнейшего анализа данной проблемы?
2 окт 14, 19:15    [16652438]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексе внешнего ключа  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
Покажите структуру (вместе с иедексами и констрейнтами) объектов [schema].[RequestMembershipCO] и #calculateRSTECRC.
3 окт 14, 03:34    [16653853]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексе внешнего ключа  [new]
invm
Member

Откуда: Москва
Сообщений: 9404
olegdev
2. Возможно ли, что X блокировка индекса была вызвана ранее в цепочке хранимых процедур а в графе видно только команду, попытавшуюся получить S блокировку?
Именно это у вас и происходит.

В одной транзакции модифицируются строки в Request, ключи которых используются в другой транзакции при вставке в RequestMembershipCO.
Как следствие, порядок работы с таблицами в транзакции Request -> RequestMembershipCO приводит к дедлокам. Лечение: изменить порядок на RequestMembershipCO -> Request.

А версионные TIL в данном случае не помогут - при проверке FK всегда используются блокировки.
3 окт 14, 12:25    [16655358]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексе внешнего ключа  [new]
olegdev
Member

Откуда:
Сообщений: 7
Сергей Алексеевич, во временную таблицу #calculateRSTEMCRC на основании внутренней логики в ходе процедуры несколько раз могут быть INSERT. Также в таблице RequestMembershipCO есть несколько некластеризованных индексов по полю [RequestKey].

CREATE TABLE #calculateRSTEMCRC
    (
        [MembershipConditionKey]    INT NOT NULL,
        [ObjectKey]                 BIGINT NOT NULL,
        [TrueStatementCount]        INT NOT NULL,
        [Iteration]                 INT NOT NULL
    );

    CREATE UNIQUE CLUSTERED INDEX [IX_CalculateRSTEMCRC]
    ON #calculateRSTEMCRC
    (
        [MembershipConditionKey],
        [ObjectKey]
    );     
	
CREATE TABLE [schema].[RequestMembershipCO](
	[RequestKey] [bigint] NOT NULL,
	[MembershipConditionKey] [int] NOT NULL,
	[ObjectKey] [bigint] NOT NULL,
	[TrueStatementCount] [int] NOT NULL,
	[Iteration] [int] NOT NULL,
 CONSTRAINT [PK_RequestMembershipCO] PRIMARY KEY CLUSTERED 
(
	[RequestKey] ASC,
	[MembershipConditionKey] ASC,
	[ObjectKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [schema].[RequestMembershipCO]  WITH CHECK ADD  CONSTRAINT [FK_RequestMembershipCO_MembershipCondition] FOREIGN KEY([MembershipConditionKey])
REFERENCES [schema].[MembershipCondition] ([MembershipConditionKey])

ALTER TABLE [schema].[RequestMembershipCO]  WITH CHECK ADD  CONSTRAINT [FK_RequestMembershipCO_Object] FOREIGN KEY([ObjectKey])
REFERENCES [schema].[Objects] ([ObjectKey])

ALTER TABLE [schema].[RequestMembershipCO]  WITH CHECK ADD  CONSTRAINT [FK_RequestMembershipCO_Request] FOREIGN KEY([RequestKey])
REFERENCES [schema].[Request] ([RequestKey])

CREATE NONCLUSTERED INDEX [IX_RequestMembershipCO_RequestKey_ObjectKey] ON [schema].[RequestMembershipCO] 
(
	[RequestKey] ASC,
	[ObjectKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_RequestMembershipCO_MembershipConditionKey_RequestKey_TrueStatementCount] ON [schema].[RequestMembershipCO] 
(
	[MembershipConditionKey] ASC,
	[RequestKey] ASC,
	[TrueStatementCount] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_RequestMembershipCO_MembershipConditionKey_RequestKey_Iteration_TrueStatementCount_ObjectKey] ON [schema].[RequestMembershipCO] 
(
	[MembershipConditionKey] ASC,
	[RequestKey] ASC,
	[Iteration] ASC,
	[TrueStatementCount] ASC,
	[ObjectKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]


Единственная догадка на данный момент - добавить в INSERT - вызывающий deadlock вместо WITH (ROWLOCK) - WITH (ROWLOCK,UPDLOCK), т.к. MSDN говорит, что при READ_COMMITTED_SNAPSHOT блокировка на строки работает только в таком виде.
3 окт 14, 12:42    [16655502]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексе внешнего ключа  [new]
olegdev
Member

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

Спасибо, буду разбираться во внутренней логике обращения к объектам.
3 окт 14, 12:47    [16655557]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить