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

Откуда: большая деревня
Сообщений: 266
Подскажите, что сделать, чтобы устранить deadlock, опыта в этом деле нет.
Есть xml данные с описанием двух процессов, полученные из событий Extended Events. Один процесс пишет данные, другой пытается их читать.

Настройка базы
SET READ_COMMITTED_SNAPSHOT OFF 


+
<deadlock>
 <victim-list>
  <victimProcess id="processfb3f6dc28" />
 </victim-list>
 <process-list>
  <process id="processfb3f6dc28" taskpriority="0" logused="0" waitresource="PAGE: 14:1:912927 " waittime="70181" ownerId="29311074214" transactionname="SELECT" lasttranstarted="2016-08-11T17:00:48.550" XDES="0x673083a20" lockMode="IS" schedulerid="8" kpid="17276" status="suspended" spid="90" sbid="1" ecid="0" priority="0" trancount="0" lastbatchstarted="2016-08-11T17:00:41.720" lastbatchcompleted="2016-08-11T17:00:41.720" lastattention="1900-01-01T00:00:00.720" clientapp="PHP" hostname="WS-CC-02" hostpid="3524" loginname="WebServer" isolationlevel="read committed (2)" xactid="29311074214" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="CONTACT_CENTR.dbo.CRMCampaignTraficSelectALL" line="13" stmtstart="558" stmtend="5544" sqlhandle="0x030011005cf8440d6936ee005fa6000001000000000000000000000000000000000000000000000000000000">
select	a.campaign
			...
from (	
	select	campaign
			,count(distinct Operator) as CCOperators
			,count(*) as  AllCalls
			,sum(case when result_ccs = 'OK' then 1 else 0 end) as CallOper
			,sum(case when result_ccs  &lt;&gt;  'OK'  then 1 else 0 end) as CallNo
						
			,case when max(T.comment) = 1 then sum(case when result_c    </frame>
    <frame procname="adhoc" line="1" stmtstart="18" sqlhandle="0x010011004f8e3f2150e801930300000000000000000000000000000000000000000000000000000000000000">
EXEC [dbo].[CRMCampaignTraficSelectALL] @P1    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 int)EXEC [dbo].[CRMCampaignTraficSelectALL] @P1   </inputbuf>
  </process>
  <process id="process11f931f468" taskpriority="0" logused="0" waitresource="PAGE: 14:1:912927 " waittime="3814" ownerId="29311068210" transactionname="user_transaction" lasttranstarted="2016-08-11T17:00:10.717" XDES="0x15015e58e0" lockMode="IS" schedulerid="2" kpid="9452" status="suspended" spid="240" sbid="1" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-08-11T17:00:10.720" lastbatchcompleted="2016-08-11T17:00:10.717" lastattention="1900-01-01T00:00:00.717" clientapp="Microsoft SQL Server" hostname="AVSQL" hostpid="4936" loginname="sa" isolationlevel="read committed (2)" xactid="29311068210" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="28" stmtend="400" sqlhandle="0x02000000d3aac8264f1b7e6e99aa9a6e3d3cf696343f643c0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 datetime)SELECT SUM(CONVERT(int,"Tbl1012"."Answer1",0)) "Expr1013",COUNT("Tbl1012"."Answer1") "Expr1014" FROM "AVAYA"."dbo"."LKK" "Tbl1012" WHERE "Tbl1012"."EVENT_TIME"&gt;=@P1   </inputbuf>
  </process>
  <process id="process13eeeb3468" taskpriority="0" logused="964200" waitresource="PAGE: 14:1:2784554 " waittime="102121" ownerId="29311064342" transactionname="INSERT" lasttranstarted="2016-08-11T17:00:06.023" XDES="0x109502540" lockMode="X" schedulerid="8" kpid="16044" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-08-11T17:00:05.977" lastbatchcompleted="2016-08-11T17:00:05.973" lastattention="1900-01-01T00:00:00.973" clientapp="SQLAgent - TSQL JobStep (Job 0x78A5BA4C822129448957DE08E4E3A93E : Step 1)" hostname="SQLDATA-NODE-01" hostpid="3088" loginname="CCNTT\sqlservice" isolationlevel="read committed (2)" xactid="29311064342" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="CRM.dbo.sp_CRM_Import_from_100" line="29" stmtstart="1680" stmtend="6892" sqlhandle="0x03000e001466447d5244c90051a4000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [CRM].[dbo].[CALL_HISTORY]
				   ([CAMPAIGN]
				   ,[CUSTOMER_ID]
				   ,[DATE_CALLSTART]
				   ...
				   ,[CLIEN    </frame>
    <frame procname="adhoc" line="3" stmtstart="116" stmtend="244" sqlhandle="0x0200000063998a0417f8fbd736ae3fbb80952e3076cc522c0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
If sys.fn_hadr_is_primary_replica ( 'CRM' ) = 1 
begin
	EXECUTE [CRM].[dbo].[sp_CRM_Import_from_100] 

                EXECUTE [CRM].[dbo].[sp_CRM_CCS2_Import_from_100] 
end

else
	print 'Это Secondary реплика'   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="912927" dbid="14" subresource="FULL" objectname="CRM.dbo.CALL_HISTORY" id="lock418641d00" mode="X" associatedObjectId="72057594112704512">
   <owner-list>
    <owner id="process13eeeb3468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="processfb3f6dc28" mode="IS" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="912927" dbid="14" subresource="FULL" objectname="CRM.dbo.CALL_HISTORY" id="lock418641d00" mode="X" associatedObjectId="72057594112704512">
   <owner-list>
    <owner id="processfb3f6dc28" mode="IS" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process11f931f468" mode="IS" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="2784554" dbid="14" subresource="FULL" objectname="CRM.dbo.CALL_HISTORY" id="lock12bc5f1f00" mode="IS" associatedObjectId="72057594112835584">
   <owner-list>
    <owner id="process11f931f468" mode="IS" />
   </owner-list>
   <waiter-list>
    <waiter id="process13eeeb3468" mode="X" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>
11 авг 16, 18:04    [19530908]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4832
virtuOS,

http://stackoverflow.com/questions/7559849/is-it-a-good-idea-to-handle-deadlock-retry-from-stored-procedure-catch-block
http://stackoverflow.com/questions/13159326/implementing-retry-logic-for-deadlock-exceptions
11 авг 16, 19:58    [19531272]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Не, как запускать повторную транзакцию мне не надо :). Мне хотелось какой-то методики по разбору дедлока с соответствующими выводами по изменению. Что-то типа методички.
Например, в моем случае должно помочь включение snapshot isolation (или нет?), но при этом может просесть производительность tempdb. Может, есть другие решения?
11 авг 16, 22:09    [19531705]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Судя по паглокам, надо или выбирать с rowlock, или модифицировать.
11 авг 16, 23:37    [19531990]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
virtuOS
Мне хотелось какой-то методики по разбору дедлока
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
virtuOS
Например, в моем случае должно помочь включение snapshot isolation
Поможет. Достаточно RCSI включить.
virtuOS
Может, есть другие решения?
Возможно. Показывайте полный скрипт создания таблицы CRM.dbo.CALL_HISTORY вместе с индексами.
11 авг 16, 23:55    [19532018]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Гавриленко Сергей Алексеевич
Судя по паглокам, надо или выбирать с rowlock, или модифицировать.

В принципе можно сделать, но переписывать каждый запрос...

invm
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example

Спасибо, почитаю.

invm
Возможно. Показывайте полный скрипт создания таблицы CRM.dbo.CALL_HISTORY вместе с индексами.

Таблица широкая, 55 столбцов. Убрал часть незначащих столбцов для сокращения листинга.
+
CREATE TABLE [dbo].[CALL_HISTORY](
	[CAMPAIGN] [nchar](15) NOT NULL,
	[CUSTOMER_ID] [nchar](15) NULL,
	[DATE_CALLSTART] [datetime] NULL,
	[PHONE] [char](20) NULL,
	[RESULT_CCS] [nchar](16) NULL,
	[OPERATOR] [nchar](10) NULL,
	[CALL_PRIORITY] [char](3) NULL,
	[CUSTOM_DATA] [nvarchar](128) NULL,
	[CALLID] [varchar](36) NULL,
	[EVENTID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_CALL_HISTORY_EVENTID]  DEFAULT (newid()),
	[E_MAIL] [nvarchar](50) NULL,
	[TS] [timestamp] NULL,
	[REC_FILE_ID] [varchar](250) NULL,
	[REC_DMS_ID] [uniqueidentifier] NULL,
	[GLOBALID] [varchar](50) NULL,
	[SESSION_ID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_CALL_HISTORY] PRIMARY KEY NONCLUSTERED 
(
	[EVENTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE CLUSTERED INDEX [IX_CH_DATE_CALLSTART] ON [dbo].[CALL_HISTORY]
(
	[DATE_CALLSTART] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CH_CALLID] ON [dbo].[CALL_HISTORY]
(
	[CALLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_CUSTOM_DATA] ON [dbo].[CALL_HISTORY]
(
	[CUSTOM_DATA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_CUSTOMER] ON [dbo].[CALL_HISTORY]
(
	[CAMPAIGN] ASC,
	[CUSTOMER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_DATE_CALLEND] ON [dbo].[CALL_HISTORY]
(
	[DATE_CALLEND] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_EMAIL] ON [dbo].[CALL_HISTORY]
(
	[E_MAIL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_PHONE] ON [dbo].[CALL_HISTORY]
(
	[PHONE] ASC,
	[PHONE_AC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_CH_TS] ON [dbo].[CALL_HISTORY]
(
	[TS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_GlobalID] ON [dbo].[CALL_HISTORY]
(
	[GLOBALID] ASC
)
WHERE ([GlobalID] IS NOT NULL)
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_Rec_File_ID] ON [dbo].[CALL_HISTORY]
(
	[REC_FILE_ID] ASC
)
WHERE ([Rec_File_ID] IS NOT NULL)
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]


В данную таблицу вставляются данные такой процедурой (переливаю данные с другого сервера):
declare @Date_Start datetime
set @Date_Start = dateadd(day, datediff(day, 0, getdate()), 0)
set @Date_Start = dateadd(day, -1, @Date_Start)

INSERT INTO [CRM].[dbo].[CALL_HISTORY] ( ... )
SELECT ...
FROM [172.19.16.70].crm_ccs2.dbo.call_history H101
		where date_callstart >= @Date_Start

			and not exists (
				select 1
				from [CRM].[dbo].[CALL_HISTORY] H
				where date_callstart >= @Date_Start
					and H.EVENTID = H101.EVENTID
			)

		order by H101.date_callstart

Обновлений данных и удаления нет. Остальное - чтение данных.
12 авг 16, 09:07    [19532475]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
aleks2
Guest
Ну, с таким дурдомом...

1. Сливать данные в промежуточную временную таблицу.
2. Разучить таки merge.
3. Merge из времянки с tablockx.
12 авг 16, 09:52    [19532630]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
aleks2, и чем в данном случае merge лучше? Мне требуется только вставка данных.
12 авг 16, 10:53    [19532949]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
virtuOS
и чем в данном случае merge лучше?
В данном случае ничем.

У вас данные вставляются в одном порядке, а считываются в другом. А дедлок возникает из-за блокировок страниц при вставке.
Можно:
а) как уже предлагалось, включить RCSI
б) добавить хинт rowlock - INSERT INTO [CRM].[dbo].[CALL_HISTORY] with (rowlock). Добавлять rowlock ко всем остальным запросам не нужно.
12 авг 16, 11:24    [19533176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить