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

Откуда:
Сообщений: 1003
Коллеги, приветствую!

Недавно столкнулся с чудесным, иначе не назовешь, поведением MSSQLserver'а

Имеется: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Имеется БД, содержащая десяток таблиц, связанных по автоинкрементным ключам с опцией каскадного удаления.
Имеется хранимая процедура, которая пишет в этот набор таблиц записи, которые берет из передаваемого в аргументе куска xml-файла. Процедура вызывается в 8-32 потока (потому что в 1 поток пишет всего около 200 наборов в секунду)

CREATE PROCEDURE [dbo].[ConvertFromXml_K_REC] 
	@Data nvarchar(max),
	@RECLIST int
AS
BEGIN
	
	SET NOCOUNT ON;
Begin Try 
	Begin Tran

		Declare @CntREC int
		Declare @CntINSURANCE int
		Declare	@xml xml

		Set @xml=CONVERT(xml, @Data)
		
		--Заполняем таблицу REC	
		insert into dbo.REC (CntRECLIST, ID)
		Select @RECLIST, @xml.value('(REC/@ID)[1]', 'int') [ID]
		Set @CntREC=SCOPE_IDENTITY()
		
		--Заполняем таблицу PERSON
		insert into dbo.PERSON (CntREC, FAM, IM, OT, DR, TRUE_DR, C_OKSM, SS, 
			PHONE, EMAIL, FIOPR, CONTACT, DDEATH, W)
		Select @CntREC,
			  @xml.value('(REC/PERSON/@FAM)[1]', 'nvarchar(40)') [FAM],
			  @xml.value('(REC/PERSON/@IM)[1]', 'nvarchar(40)') [IM],
			  @xml.value('(REC/PERSON/@OT)[1]', 'nvarchar(40)') [OT],
			  @xml.value('(REC/PERSON/@DR)[1]', 'datetime') [DR],
			  @xml.value('(REC/PERSON/@TRUE_DR)[1]', 'int') [TRUE_DR],
			  @xml.value('(REC/PERSON/@C_OKSM)[1]', 'nvarchar(3)') [C_OKSM],
			  @xml.value('(REC/PERSON/@SS)[1]', 'nvarchar(14)') [SS],
			  @xml.value('(REC/PERSON/@PHONE)[1]', 'nvarchar(40)') [PHONE],
			  @xml.value('(REC/PERSON/@EMAIL)[1]', 'nvarchar(50)') [EMAIL],
			  @xml.value('(REC/PERSON/@FIOPR)[1]', 'nvarchar(130)') [FIOPR],
			  @xml.value('(REC/PERSON/@CONTACT)[1]', 'nvarchar(200)') [CONTACT],
			  @xml.value('(REC/PERSON/@DDEATH)[1]', 'datetime') [DDEATH],
			  @xml.value('(REC/PERSON/@W)[1]', 'int') [W]
		
		--Заполняем таблицу DOC
		insert into dbo.DOC (CntREC, DOCTYPE, DOCSER, DOCNUM, DOCDATE, NAME_VP, MR)
		Select  @CntREC,
			  @xml.value('(REC/DOC/@DOCTYPE)[1]', 'nvarchar(2)') [DOCTYPE],
			  @xml.value('(REC/DOC/@DOCSER)[1]', 'nvarchar(10)') [DOCSER],
			  @xml.value('(REC/DOC/@DOCNUM)[1]', 'nvarchar(20)') [DOCNUM],
			  @xml.value('(REC/DOC/@DOCDATE)[1]', 'datetime') [DOCDATE],
			  @xml.value('(REC/DOC/@NAME_VP)[1]', 'nvarchar(80)') [NAME_VP],
			  @xml.value('(REC/DOC/@MR)[1]', 'nvarchar(100)') [MR]
			  where @xml.exist('/REC/DOC')=1
				
		--Заполняем таблицу ADDRES_G
		insert into dbo.ADDRES_G (CntREC, BOMG, SUBJ, INDX, OKATO, RNNAME, NPNAME, UL, DOM, KORP, KV, DREG)
		Select @CntREC,
			  @xml.value('(REC/ADDRES_G/@BOMG)[1]', 'int') [BOMG],
			  @xml.value('(REC/ADDRES_G/@SUBJ)[1]', 'nvarchar(5)') [SUBJ],
			  @xml.value('(REC/ADDRES_G/@INDX)[1]', 'nvarchar(6)') [INDX],
			  @xml.value('(REC/ADDRES_G/@OKATO)[1]', 'nvarchar(11)') [OKATO],
			  @xml.value('(REC/ADDRES_G/@RNNAME)[1]', 'nvarchar(80)') [RNNAME],
			  @xml.value('(REC/ADDRES_G/@NPNAME)[1]', 'nvarchar(80)') [NPNAME],
			  @xml.value('(REC/ADDRES_G/@UL)[1]', 'nvarchar(80)') [UL],
			  @xml.value('(REC/ADDRES_G/@DOM)[1]', 'nvarchar(7)') [DOM],
			  @xml.value('(REC/ADDRES_G/@KORP)[1]', 'nvarchar(6)') [KORP],
			  @xml.value('(REC/ADDRES_G/@KV)[1]', 'nvarchar(6)') [KV],
			  @xml.value('(REC/ADDRES_G/@DREG)[1]', 'datetime') [DREG]
			  where @xml.exist('/REC/ADDRES_G')=1
		
		--Заполняем таблицу ADDRES_P
		insert into dbo.ADDRES_P (CntREC, SUBJ, INDX, OKATO, RNNAME, NPNAME, UL, DOM, KORP, KV )
		Select @CntREC,
			  @xml.value('(REC/ADDRES_P/@SUBJ)[1]', 'nvarchar(5)') [SUBJ],
			  @xml.value('(REC/ADDRES_P/@INDX)[1]', 'nvarchar(6)') [INDX],
			  @xml.value('(REC/ADDRES_P/@OKATO)[1]', 'nvarchar(11)') [OKATO],
			  @xml.value('(REC/ADDRES_P/@RNNAME)[1]', 'nvarchar(80)') [RNNAME],
			  @xml.value('(REC/ADDRES_P/@NPNAME)[1]', 'nvarchar(80)') [NPNAME],
			  @xml.value('(REC/ADDRES_P/@UL)[1]', 'nvarchar(80)') [UL],
			  @xml.value('(REC/ADDRES_P/@DOM)[1]', 'nvarchar(7)') [DOM],
			  @xml.value('(REC/ADDRES_P/@KORP)[1]', 'nvarchar(6)') [KORP],
			  @xml.value('(REC/ADDRES_P/@KV)[1]', 'nvarchar(6)') [KV]
			  where @xml.exist('/REC/ADDRES_P')=1
				
		--Заполняем таблицу VIZIT
		insert into dbo.VIZIT (CntREC, DVIZIT, METHOD, PETITION, RSMO, RPOLIS, FPOLIS)
		Select @CntREC,
			  @xml.value('(REC/VIZIT/@DVIZIT)[1]', 'datetime') [DVIZIT],
			  @xml.value('(REC/VIZIT/@METHOD)[1]', 'nvarchar(1)') [METHOD],
			  @xml.value('(REC/VIZIT/@PETITION)[1]', 'nvarchar(1)') [PETITION],
			  @xml.value('(REC/VIZIT/@RSMO)[1]', 'int') [RSMO],
			  @xml.value('(REC/VIZIT/@RPOLIS)[1]', 'int') [RPOLIS],
			  @xml.value('(REC/VIZIT/@FPOLIS)[1]', 'int') [FPOLIS]
			  where @xml.exist('/REC/VIZIT')=1
		
		if @xml.exist('/REC/INSURANCE')=1
		Begin
		--Заполняем таблицу INSURANCE	
		insert into dbo.INSURANCE (CntREC, [TER_ST], [ENP], [OGRNSMO], [ERP])
		Select @CntREC, 
			  @xml.value('(REC/INSURANCE/@TER_ST)[1]', 'nvarchar(5)') [TER_ST],
			  @xml.value('(REC/INSURANCE/@ENP)[1]', 'nvarchar(16)') [ENP],
			  @xml.value('(REC/INSURANCE/@OGRNSMO)[1]', 'nvarchar(15)') [OGRNSMO],
			  @xml.value('(REC/INSURANCE/@ERP)[1]', 'int') [ERP]
		Set @CntINSURANCE=SCOPE_IDENTITY()			
		
		--Заполняем таблицу POLIS
		insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ
		insert into dbo.ORDERZ (CntINSURANCE, NORDER, DORDER)
		Select @CntINSURANCE,
			 b.value('(./@NORDER)[1]', 'nvarchar(100)') [NORDER],
			 b.value('(./@DORDER)[1]', 'datetime') [DORDER]
				from @xml.nodes('REC/INSURANCE/ORDERZ') t(b)
		End

		Insert into dbo.DTA (CntRECLIST, CntREC, xmlData)
		values (@RECLIST, @CntREC, @Data)
					
	Commit
End Try
Begin catch
	Rollback
		Insert into dbo.DTA (CntRECLIST, xmlData, ErrorCode)
		values (@RECLIST, @Data, ERROR_NUMBER())
End catch				
	
END


Всё работает - просто замечательно. НО (!!!)

Беру базу. Делаю с нее скрипт. Создаю рядом аналогичную базу.
Начинают сыпаться дедлоки с участием таблицы dbo.INSURANCE
После того, как модифицирую запрос как:
insert into dbo.INSURANCE with (tablockx) (CntREC, [TER_ST], [ENP], [OGRNSMO], [ERP]) - всё начинает работать, но 2,5 - 3 раза медленнее (разумеется).
При этом рядом-стоящая база, с которой сделан скрипт, работает без эксклюзивного лока таблицы, демонстрируя скорость записи порядка 1500-2000 наборов в секунду при 16 потоках (у сервера 16 ядер, когда ставлю больше потоков - производительность больше не растёт).

Я голову сломал.
1. В обеих БД нет индексов. Никаких. Кроме кластерных PK по автоинкрементным ключам (есть в каждой таблице).
2. Вторая представляет из себя скрипт первой (сгенерировать скрипт - все объекты - всё по дефолту, только уровень совместимости 90, для SQL 2005. И первая, и вторая БД - уровень совместимости 90).
3. В первой БД удалял все данные. Делал шринк файла. Вторую - создавал заново. Одни и те же данные запускаю на загрузку.
Результат - в оригинальной базе дедлоков нет. В копии из скрипта - они ЕСТЬ.

Нет. Я бы понял, когда они бы были и там и здесь (хотя, честно говоря, не понятно, какого черта им быть???!).
Но когда там есть - здесь нет...

Поделитесь мыслями, а?
25 сен 13, 18:51    [14884411]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
invm
Member

Откуда: Москва
Сообщений: 9725
Чудес не бывает. Выкладывайте граф дедлока в xml.
25 сен 13, 19:05    [14884438]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
Поделитесь мыслями
Триггера то нету на эту таблицу? :-)

Ну и вообще, всего остального.
А то мало ли, может скрипт неполный, студия же по умолчанию не всё генерит...

Описание дедлока нужно подробнее смотреть.

А вообще подход нехороший, процедурный, быстрее было бы не кусочками XML импортить, а сразу. Как будто ораклисты писали, прости господи!
25 сен 13, 23:44    [14885140]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
uaggster
Member

Откуда:
Сообщений: 1003
<deadlock-list>
 <deadlock victim="process44b2988">
  <process-list>
   <process id="process44b2988" taskpriority="0" logused="1356" waitresource="KEY: 15:72057594039042048 (61a06abd401c)" waittime="3417" ownerId="9853028" transactionname="user_transaction" lasttranstarted="2013-09-26T08:58:11.893" XDES="0x89599950" lockMode="S" schedulerid="2" kpid="1684" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T08:58:11.647" lastbatchcompleted="2013-09-26T08:58:11.607" clientapp=".Net SqlClient Data Provider" hostname="test" hostpid="7468" loginname="sa" isolationlevel="read committed (2)" xactid="9853028" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TFOMSKFile22.dbo.ConvertFromXml_K_REC" line="112" stmtstart="10210" stmtend="11254" sqlhandle="0x03000f00e7a4787d23d0930045a200000100000000000000">
insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 15 Object Id = 2105058535]    </inputbuf>
   </process>
   <process id="process3ea9048" taskpriority="0" logused="1508" waitresource="KEY: 15:72057594039042048 (8194443284a0)" waittime="3442" ownerId="9853034" transactionname="user_transaction" lasttranstarted="2013-09-26T08:58:11.893" XDES="0x804f1950" lockMode="S" schedulerid="1" kpid="3944" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T08:58:11.653" lastbatchcompleted="2013-09-26T08:58:11.617" clientapp=".Net SqlClient Data Provider" hostname="test" hostpid="7468" loginname="sa" isolationlevel="read committed (2)" xactid="9853034" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TFOMSKFile22.dbo.ConvertFromXml_K_REC" line="112" stmtstart="10210" stmtend="11254" sqlhandle="0x03000f00e7a4787d23d0930045a200000100000000000000">
insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 15 Object Id = 2105058535]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock19004f200" mode="X" associatedObjectId="72057594039042048">
    <owner-list>
     <owner id="process3ea9048" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process44b2988" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock19008dc80" mode="X" associatedObjectId="72057594039042048">
    <owner-list>
     <owner id="process44b2988" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process3ea9048" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Вот граф дедлока :)
Возникает и еще один:
<deadlock-list>
 <deadlock victim="process51e2988">
  <process-list>
   <process id="process51e2988" taskpriority="0" logused="1468" waitresource="KEY: 15:72057594039042048 (61a06abd401c)" waittime="5005" ownerId="9853140" transactionname="user_transaction" lasttranstarted="2013-09-26T08:58:13.217" XDES="0x9d90ee80" lockMode="S" schedulerid="7" kpid="5660" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T08:58:13.217" lastbatchcompleted="2013-09-26T08:58:11.633" clientapp=".Net SqlClient Data Provider" hostname="test" hostpid="7468" loginname="sa" isolationlevel="read committed (2)" xactid="9853140" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TFOMSKFile22.dbo.ConvertFromXml_K_REC" line="112" stmtstart="10210" stmtend="11254" sqlhandle="0x03000f00e7a4787d23d0930045a200000100000000000000">
insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 15 Object Id = 2105058535]    </inputbuf>
   </process>
   <process id="process3ea9048" taskpriority="0" logused="1508" waitresource="KEY: 15:72057594039042048 (98ec012aa510)" waittime="5005" ownerId="9853034" transactionname="user_transaction" lasttranstarted="2013-09-26T08:58:11.893" XDES="0x804f1950" lockMode="S" schedulerid="1" kpid="3944" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T08:58:11.653" lastbatchcompleted="2013-09-26T08:58:11.617" clientapp=".Net SqlClient Data Provider" hostname="test" hostpid="7468" loginname="sa" isolationlevel="read committed (2)" xactid="9853034" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TFOMSKFile22.dbo.ConvertFromXml_K_REC" line="112" stmtstart="10210" stmtend="11254" sqlhandle="0x03000f00e7a4787d23d0930045a200000100000000000000">
insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 15 Object Id = 2105058535]    </inputbuf>
   </process>
   <process id="process14fdc8" taskpriority="0" logused="1536" waitresource="KEY: 15:72057594039042048 (61a06abd401c)" waittime="5005" ownerId="9853039" transactionname="user_transaction" lasttranstarted="2013-09-26T08:58:11.893" XDES="0x9f6d43b0" lockMode="S" schedulerid="5" kpid="5604" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T08:58:11.653" lastbatchcompleted="2013-09-26T08:58:11.623" clientapp=".Net SqlClient Data Provider" hostname="test" hostpid="7468" loginname="sa" isolationlevel="read committed (2)" xactid="9853039" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TFOMSKFile22.dbo.ConvertFromXml_K_REC" line="112" stmtstart="10210" stmtend="11254" sqlhandle="0x03000f00e7a4787d23d0930045a200000100000000000000">
insert into dbo.POLIS (CntINSURANCE, VPOLIS, NPOLIS, SPOLIS, DBEG, DEND, DSTOP)
		Select @CntINSURANCE, 
			  b.value('(./@VPOLIS)[1]', 'int') [VPOLIS],
		      b.value('(./@NPOLIS)[1]', 'nvarchar(20)') [NPOLIS],
		      b.value('(./@SPOLIS)[1]', 'nvarchar(10)') [SPOLIS],
		      b.value('(./@DBEG)[1]', 'datetime') [DBEG],
		      b.value('(./@DEND)[1]', 'datetime') [DEND],
		      b.value('(./@DSTOP)[1]', 'datetime') [DSTOP]
				from @xml.nodes('REC/INSURANCE/POLIS') t(b)
		
		--Заполняем таблицу ORDERZ     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 15 Object Id = 2105058535]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock19004f200" mode="X" associatedObjectId="72057594039042048">
    <owner-list>
     <owner id="process3ea9048" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process51e2988" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock18fb11400" mode="X" associatedObjectId="72057594039042048">
    <owner-list>
     <owner id="process14fdc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process3ea9048" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock19004f200" mode="X" associatedObjectId="72057594039042048">
    <owner-list/>
    <waiter-list>
     <waiter id="process14fdc8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>


alexeyvg> А вообще подход нехороший, процедурный, быстрее было бы не кусочками XML импортить, а сразу. Как будто ораклисты писали, прости господи!
Не, к сожалению всё сразу импортить не выходит.
Во-первых, файлы очень большие, порядка миллиона <REC>, под гигабайт толщиной.
Во-вторых, некоторые <REC> содержат куски, не совместимые с жизнью. Т.е. это, фактически, не xml, а некий хмл-подобный текст, местами - кривой.
Собственно, такие кривые куски ловятся кэтчем и хранятся дальше, вместе с кодом ошибки в специальной, отдельной табличке Insert into dbo.DTA (CntRECLIST, xmlData, ErrorCode) values (@RECLIST, @Data, ERROR_NUMBER())

Нет, триггеров нету. Ни триггеров, ни индексов.
Единственное, наверное, отличие - identity на оригинале - далеко ушагал, за десяток миллионов во всех таблицах.
Но! а) полностью удалял данные и б) Сжимал базу
Даже статистику пересчитывал. На пустой базе - оригинале.
26 сен 13, 09:25    [14885784]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
Нет, триггеров нету. Ни триггеров, ни индексов.
Единственное, наверное, отличие - identity на оригинале - далеко ушагал, за десяток миллионов во всех таблицах.
Но! а) полностью удалял данные и б) Сжимал базу
Если количество данные изначально нулевое, то ИМХО не важно, с какой цифры стартует идентити...
uaggster
Не, к сожалению всё сразу импортить не выходит.
Во-первых, файлы очень большие, порядка миллиона <REC>, под гигабайт толщиной.
Ну, я импортирую XML файлы, это не предположения...

У меня в день 70000 файлов, от 1 кб до 30 мб, записей получается несколько миллиардов, ещё из одного источника импортировались файлы по несколько гигабайт (вроде 4 гб).

Гигабайтный файл SSIS пакетом импортируется за минуты, раскладывается на некоторое количество таблиц с сырыми данными, далее уже из этих таблиц вставляем данные в БД.

Главное, что бы это был валидный XML.
Импорт там потоковый, от размера файла не зависит, это не модель в памяти.
26 сен 13, 09:45    [14885874]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
Т.е. это, фактически, не xml, а некий хмл-подобный текст, местами - кривой.
Собственно, такие кривые куски ловятся кэтчем и хранятся дальше, вместе с кодом ошибки в специальной, отдельной табличке
Ну да, это другое дело...
Может, как то попытаться парсить, валидировать до импорта (не в сиквеле), делать валидный файл и файл с описанием кривых фрагментов...
26 сен 13, 09:47    [14885885]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
Не, к сожалению всё сразу импортить не выходит.
Ещё хороший подход - многопоточный импорт процедурами в отдельные таблицы (свои для каждого потока), возможно в tempdb, возможно неинедксированные кучи, и потом уже вставка в постоянные таблицы БД
Тогда будет минимальная переделка кода, и будет быстрее, чем сейчас.
26 сен 13, 09:52    [14885907]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
uaggster
Member

Откуда:
Сообщений: 1003
alexeyvg
uaggster
Не, к сожалению всё сразу импортить не выходит.
Ещё хороший подход - многопоточный импорт процедурами в отдельные таблицы (свои для каждого потока), возможно в tempdb, возможно неинедксированные кучи, и потом уже вставка в постоянные таблицы БД
Тогда будет минимальная переделка кода, и будет быстрее, чем сейчас.

Гм... Нужно обдумать. Но при слиянии из десятка наборов таблиц в единый целевой набор - о5 будут локи. Т.е. их, наверное, нужно будет сначала параллельно разложить по кучкам, а потом кучки - последовательно слить по назначению?
Это убъет выигрыш, ИМХО.

В принципе, начальный вариант "без дедлока", меня устраивает по производительности и по простоте. Миллион наборов попадают в целевую структуру чуть меньше, чем за 10 минут, и модифицировать код легко.
Только не пойму, в чем странность!
26 сен 13, 10:15    [14886013]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
aleks2
Guest
Граф то тривиальный - типо азбука дедлоков. Хотя, забавно.
Два процесса сначала читают индекс - вместе...
А потом хотят его заблокировать, шоб изменить.

Индекс маленький, поэтому оптимизатор, возможно, выбирает стратегию "а целиком заблокируем"...
26 сен 13, 10:23    [14886054]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
aleks2
Guest
Забавность состоит в том, что общепринятая трактовка "вставки в хвост кластерного индекса " как "неблокирующего" процесса дает здеся явный сбой...
26 сен 13, 10:25    [14886066]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
alexeyvg
Ещё хороший подход - многопоточный импорт процедурами в отдельные таблицы (свои для каждого потока), возможно в tempdb, возможно неинедксированные кучи, и потом уже вставка в постоянные таблицы БД
Тогда будет минимальная переделка кода, и будет быстрее, чем сейчас.

Гм... Нужно обдумать. Но при слиянии из десятка наборов таблиц в единый целевой набор - о5 будут локи. Т.е. их, наверное, нужно будет сначала параллельно разложить по кучкам, а потом кучки - последовательно слить по назначению?
Это убъет выигрыш, ИМХО.
Нет, потом, после окончания импорта "из кусочков файла", все эти таблички сливаются в постоянные таблицы в одном потоке (одним запросом с union all или последовательно, в цикле, если данные одного потока не поместятся в память и сортировки перейдёт в tempdb)
26 сен 13, 10:58    [14886241]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
Гость333
Member

Откуда:
Сообщений: 3683
aleks2
Два процесса сначала читают индекс - вместе...
А потом хотят его заблокировать, шоб изменить.

Всё наоборот.
   <keylock hobtid="72057594039042048" dbid="15" objectname="TFOMSKFile22.dbo.INSURANCE" indexname="PK__INSURANC__5485C8E50EA330E9" id="lock19004f200" mode="X" associatedObjectId="72057594039042048">
    <owner-list>
-->     <owner id="process3ea9048" mode="X"/>
    </owner-list>
    <waiter-list>
-->     <waiter id="process44b2988" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

Процессы сначала заблокировали по ключу индекса. А потом хотят прочитать ключи друг друга.

aleks2
Индекс маленький, поэтому оптимизатор, возможно, выбирает стратегию "а целиком заблокируем"...

keylock — это блокировка одного ключа индекса, а не "целиком заблокируем".
26 сен 13, 11:05    [14886263]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31869
uaggster
В принципе, начальный вариант "без дедлока", меня устраивает по производительности и по простоте. Миллион наборов попадают в целевую структуру чуть меньше, чем за 10 минут, и модифицировать код легко.
А вообще не проще не разбираться, а обрабатывать событие дедлока и повторять операцию? Много их, этих дедлоков?
26 сен 13, 11:12    [14886284]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
Поговорить захотелось
Guest
Судя по вашему графу дедлока у вас есть еще и внешние ключи между таблицами. INSERT в таблицу POLIS делает проверку внешнего ключа к таблице INSURANCE и делает это с полным сканом этой таблицы. При этом возникает дедлок, т.к. разные потоки пытаются сделать скан таблицы удерживая на ней X блокировки на свои свежевставленные строки.

Чтобы это точно выяснить нужен план исполнения вашей процедуры.

Как вариант решения - добавьте во все INSERTы хинт OPTION (LOOP JOIN).
Но как это повлияет на производительность - х.з.
26 сен 13, 11:19    [14886310]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Поговорить захотелось
Как вариант решения - добавьте во все INSERTы хинт OPTION (LOOP JOIN).

Спасибо, что навели меня на мысль, как воспроизвести дедлок.

То, что он возникает из-за внешних ключей, я понял из первого сообщения:
uaggster
Имеется БД, содержащая десяток таблиц, связанных по автоинкрементным ключам с опцией каскадного удаления.

Но воспроизвести ситуацию не получилось.

А теперь вот:
+ Подготовка тестовых таблиц
create table dbo.ParentTable(id_Parent int identity primary key with(allow_page_locks = off));

create table dbo.ChildTable
( id_Child int identity primary key,
  id_Parent int references dbo.ParentTable(id_Parent)
);

insert dbo.ParentTable default values;
insert dbo.ParentTable default values;
insert dbo.ParentTable default values;

insert dbo.ChildTable(id_Parent) values (1), (2), (3);

select * from dbo.ParentTable;
select * from dbo.ChildTable;
go

+ Запустить одновременно в двух окнах SSMS. В одном из окон будет такой же дедлок, как у ТСа
declare @id_Parent int;

begin transaction;

insert dbo.ParentTable default values;

set @id_Parent = scope_identity();

waitfor delay '00:00:05';

insert dbo.ChildTable(id_Parent) values(@id_Parent) option(merge join); -- небольшой фокус

commit transaction;

Не знал, что OPTION(LOOP/MERGE JOIN) влияет также на способ доступа к родительской таблице при проверке значения внешнего ключа.
26 сен 13, 12:17    [14886668]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
uaggster
Member

Откуда:
Сообщений: 1003
alexeyvg
uaggster
В принципе, начальный вариант "без дедлока", меня устраивает по производительности и по простоте. Миллион наборов попадают в целевую структуру чуть меньше, чем за 10 минут, и модифицировать код легко.
А вообще не проще не разбираться, а обрабатывать событие дедлока и повторять операцию? Много их, этих дедлоков?

Нет, не пустой базе всё дедлочится сразу и намертво.
Т.е. ПРОХОДЯТ только некоторые вставки, остальные - падают.

Поговорить захотелось
Судя по вашему графу дедлока у вас есть еще и внешние ключи между таблицами. INSERT в таблицу POLIS делает проверку внешнего ключа к таблице INSURANCE и делает это с полным сканом этой таблицы. При этом возникает дедлок, т.к. разные потоки пытаются сделать скан таблицы удерживая на ней X блокировки на свои свежевставленные строки.

Чтобы это точно выяснить нужен план исполнения вашей процедуры.

Как вариант решения - добавьте во все INSERTы хинт OPTION (LOOP JOIN).
Но как это повлияет на производительность - х.з.


Да, как я говорил, таблицы связаны FK с опцией каскадного удаления.

Добавил в три запроса (--Заполняем таблицу INSURANCE, --Заполняем таблицу POLIS, --Заполняем таблицу ORDERZ) хинт OPTION (LOOP JOIN).
Дедлоки исчезли.
Скорость обработки в сравнении с вариантом с эксклюзивной блокировкой выросла в 2,5 раза, до, примерно, 1200 батчей/сек. в сравнение с оригинальной 1400 - 1800 батчей/с (в среднем).

Т.е., получается, чтобы воспроизвести дедлоки на оригинальной БД мне нужно удалить в ней данные и почистить кэш процедур :)

Вопрос... Может какой - нибудь дополнительный индекс выправит ситуацию?

Кстати, большое спасибо! Это уже можно как то использовать.
26 сен 13, 13:37    [14887222]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
Поговорить захотелось
Guest
SQL Server выбирает способ выполнения join в зависимости от количества строк в таблице. В целом если в INSURANCE много строк, то выгоднее делать loop join, а если мало - то SQL Server решает сделать merge и вы получаете дедлок (Вообще-то пока мы не видели планов все это гадание на кофейной гуще)

Поэтому если количество данных на ваших серверах сильно отличается, то не удивительно, что и планы разные.

Индекс вам вряд ли поможет, он у вас и так есть по PK, именно он то и нужен.

А вот статистику нужно регулярно обновлять. Проверьте, включено ли автоообновление статистики на вашем проблемном сервере.
26 сен 13, 14:00    [14887370]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
uaggster
Member

Откуда:
Сообщений: 1003
Поговорить захотелось,
План вот: (см. прил.)
Но это план дедлочной процедуры.
К сожалению, план оригинальной процедуры дать не могу. Почистил кэш процедур и получил аналогичное поведение. Дедлоки.
Поторопился проверить идею :)

К сообщению приложен файл (1.zip - 85Kb) cкачать
26 сен 13, 15:05    [14887936]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
Поговорить захотелось
Guest
Ну вот, в плане действительно merge join при вставке в POLIS.
Причем Estimated Number of Rows из таблицы INSURANCE = 1, т.е. она практически пустая.
Если вы регулярно очищаете таблицы, в которые идет вставка, то не удивительно, что генерируется такой план.
Ставьте хинт и все будет работать.
26 сен 13, 16:05    [14888373]     Ответить | Цитировать Сообщить модератору
 Re: Чудесное поведение deadlock'а  [new]
uaggster
Member

Откуда:
Сообщений: 1003
Поговорить захотелось
Если вы регулярно очищаете таблицы, в которые идет вставка, то не удивительно, что генерируется такой план.
Ставьте хинт и все будет работать.

Нет, там немного не так. На одном сервере уже имеется заполненная БД (бекап которой я восстановил на тестовом сервере). И нужно было растиражировать ее (для заполнения) на другие сервера.
В тупую сделать скрипт, создать аналогичную пустую БД рядом - не получилось. Новая, пустая база - демонстрировала описанное поведение.
Я попытался выяснить, чем они отличаются (хотя ничем не должны были). Не нашел. Удалил данные в исходной базе. Но она по прежнему демонстрировала нормальное поведение, без дедлоков.
И только когда я удалил данные и сбросил кэш процедур - обе пустые базы стали работать одинаково.

Я выяснил, почему это раньше не проявлялось. Раньше в базу данные заливали в 1 поток, и когда приложение - клиент переделали в многопоточный вариант - в базе уже скопилось солидно записей.
26 сен 13, 16:54    [14888765]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить