Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Привет!

Простой
DELETE FROM MyTable WHERE ID = @ID
приволит к DEADLOCK, потому что эскалирует блокировки до X.
Дело в том, что система постороена так, что генерится параллельно куча подобных DELETE команд и они "деруться" друг с другом за одну и ту же таблицу.
Два вопроса:
1. Какого <censored> блокируется вся таблица, а не строка, если поиск идет по ключа (кластерный индекс)?
2. Как его заставить не переписывая запрос выполнять ROW_LOCK?
8 сен 17, 13:14    [20781614]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
А триггеров на удаление нет ?
8 сен 17, 13:17    [20781624]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
LSV,

Да, есть тригер AFTER DELETE и FOR UPDATE.
8 сен 17, 13:20    [20781632]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
msLex
Member

Откуда:
Сообщений: 7726
Yuri Abele,


1. Эскалация до X - это бред, т.к. эскалация это уменьшение гранулярности (укрупнение) а не изменение типа блокировки.
2. У вас индекс то по ID есть?
3. У вас "куча подобных DELETE" внутри одной транзакции?



Запретить эскалацию (если именно она является проблемой) на уровне таблицы

alter table ... set ( lock_escalation = disable)
8 сен 17, 13:23    [20781651]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Не знаю я происходит изменения тиба блокировки в процессе выполнения, или сразу, но блокирует всю таблицу.

Похоже понятна причина - сразу не сообразил, коллега подсказал - индекс-то кластерный.
Т.е. при удалении строки по этому индексу, поправьте если я не прав, происходит физическая реорганизация таблицы, что требует всей ее блокировки. Так?
8 сен 17, 13:30    [20781688]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
msLex
3. У вас "куча подобных DELETE" внутри одной транзакции?

Разные транзакции, сессии и даже пользователи (security contexts)
8 сен 17, 13:34    [20781703]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
Yuri Abele
Т.е. при удалении строки по этому индексу, поправьте если я не прав, происходит физическая реорганизация таблицы, что требует всей ее блокировки. Так?
Господи, откуда вы это все берете?
8 сен 17, 13:38    [20781717]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele
LSV,

Да, есть тригер AFTER DELETE и FOR UPDATE.

и что вы в них делаете?
8 сен 17, 13:45    [20781742]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Т.е. он просто освобождает страницы (т.е. речь о фрагментации)?
8 сен 17, 13:45    [20781745]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
Yuri Abele
LSV,

Да, есть тригер AFTER DELETE и FOR UPDATE.

и что вы в них делаете?

Изменяются данные совсем другой таблицы.
В AFTER DELETE:
UPDATE, а потом DELETE WHERE SomeFK IN (SELECT ID FROM deleted)
В FOR UPDATE:
UPDATE OtherTable INNER JOIN inserted ...
8 сен 17, 13:48    [20781756]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele,

и сюдя по тексту не очень уверен что вы правильно определяете гранулярность блокировки.
8 сен 17, 13:48    [20781757]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele
TaPaK
пропущено...

и что вы в них делаете?

Изменяются данные совсем другой таблицы.
В AFTER DELETE:
UPDATE, а потом DELETE WHERE SomeFK IN (SELECT ID FROM deleted)
В FOR UPDATE:
UPDATE OtherTable INNER JOIN inserted ...

нахрен фантазии, граф дедлока давайте
8 сен 17, 13:49    [20781763]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK,

Так как на картинке. частенько слева в верху блокировка S, а справа внизу X

К сообщению приложен файл. Размер - 18Kb
8 сен 17, 14:00    [20781803]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
оба запроса вида
DELETE FROM SomeTable WHERE ID=@ID
8 сен 17, 14:02    [20781807]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele
оба запроса вида
DELETE FROM SomeTable WHERE ID=@ID

дедлок на такой операции не возникнет. хоть с принудительным tablock, все подождут.
граф не картиночкой надо бы
8 сен 17, 14:04    [20781822]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
+ вы сами не видите что в графе разные объекты?? дедлок это грубо говоря перекрёстно заблокировались бю
8 сен 17, 14:06    [20781832]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
разные объекты

Tooltip-ы над оваломи слева и справа показывают (см. выше) те самые делеты на одной и той же таблице.

но есть и такое, гда убиты были обновления в тригере (см. выше) другой таблицы:

К сообщению приложен файл. Размер - 67Kb
8 сен 17, 14:13    [20781866]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
В виде XML сейчас вытащу.

Еще, эксперименты показали, что активация в базе данных SNAPSHOT ISOLATION многократно сократила количество DEADLOCKов, но совсем не убрала.
8 сен 17, 14:17    [20781885]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Вот: Похоже-таки, что разные таблицы:

<deadlock>
	<victim-list>
		<victimProcess id="process2b6d2c81468"/>
	</victim-list>
	<process-list>
		<process id="process2b6d2c81468" taskpriority="0" logused="0" waitresource="PAGE: 18:1:614134 " waittime="8886" ownerId="1390589" transactionname="implicit_transaction" lasttranstarted="2017-09-08T03:56:17.777" XDES="0x2b6d2c94e58" lockMode="S" schedulerid="4" kpid="16772" status="suspended" spid="77" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-09-08T03:56:17.773" lastbatchcompleted="2017-09-08T03:56:17.740" lastattention="1900-01-01T00:00:00.740" clientapp="Microsoft JDBC Driver for SQL Server" hostname="bm-deka-si.c1064.brandmaker.net" hostpid="0" loginname="deka_si_media1" isolationlevel="read committed (2)" xactid="1390589" currentdb="18" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
			<executionStack>
				<frame procname="adhoc" line="1" stmtstart="94" stmtend="520" sqlhandle="0x020000003267b3003f8fbbed8699ca5da6181f591c2f02780000000000000000000000000000000000000000">  unknown    </frame>
				<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  unknown    </frame>
			</executionStack>
			<inputbuf>  (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int)SELECT m.MEDIA_GUID   FROM media m    LEFT JOIN media_hashes mh ON m.MEDIA_GUID = mh.MEDIA_GUID AND m.VERSION = mh.VERSION_NUMBER   WHERE (m.MD5_HASH = @P0 OR mh.MD5_HASH = @P1) AND  ISNULL(m.DATABASE_ID,-1) &lt;&gt; @P2                           </inputbuf>
		</process>
		<process id="process2b6d2c84108" taskpriority="0" logused="11432" waitresource="PAGE: 18:1:614133 " waittime="7787" ownerId="1387486" transactionname="implicit_transaction" lasttranstarted="2017-09-08T03:56:12.770" XDES="0x2b6d2c958a8" lockMode="IX" schedulerid="1" kpid="6728" status="suspended" spid="76" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-09-08T03:56:17.157" lastbatchcompleted="2017-09-08T03:56:17.157" lastattention="1900-01-01T00:00:00.157" clientapp="Microsoft JDBC Driver for SQL Server" hostname="bm-deka-si.c1064.brandmaker.net" hostpid="0" loginname="deka_si_media1" isolationlevel="read committed (2)" xactid="1387486" currentdb="18" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128058">
			<executionStack>
				<frame procname="c1064_deka_si_media.dbo.asset_delete_after" line="5" stmtstart="358" stmtend="492" sqlhandle="0x030012005b063637b5b70701d1a7000000000000000000000000000000000000000000000000000000000000">  DELETE FROM media WHERE MEDIA_GUID IN (select ASSET_ID from deleted    </frame>
				<frame procname="adhoc" line="1" stmtstart="122" stmtend="192" sqlhandle="0x0200000075310c17002dff4a97004f3bea783b6fa30f524f0000000000000000000000000000000000000000">  unknown    </frame>
			</executionStack>
			<inputbuf>  (@P0 int)/* delete com.brandmaker.mms.mp.domain.AssetBean */ delete from asset where ASSET_ID=@P0           </inputbuf>
		</process>
	</process-list>
	<resource-list>
		<pagelock fileid="1" pageid="614134" dbid="18" subresource="FULL" objectname="c1064_deka_si_media.dbo.MEDIA" id="lock2b6c4014900" mode="IX" associatedObjectId="72057611050811392">
			<owner-list>
				<owner id="process2b6d2c84108" mode="IX"/>
			</owner-list>
			<waiter-list>
				<waiter id="process2b6d2c81468" mode="S" requestType="wait"/>
			</waiter-list>
		</pagelock>
		<pagelock fileid="1" pageid="614133" dbid="18" subresource="FULL" objectname="c1064_deka_si_media.dbo.MEDIA" id="lock2b6d78ccb80" mode="SIU" associatedObjectId="72057611050811392">
			<owner-list>
				<owner id="process2b6d2c81468" mode="S"/>
			</owner-list>
			<waiter-list>
				<waiter id="process2b6d2c84108" mode="IX" requestType="convert"/>
			</waiter-list>
		</pagelock>
	</resource-list>
</deadlock>
8 сен 17, 14:34    [20781954]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele
В виде XML сейчас вытащу.

Еще, эксперименты показали, что активация в базе данных SNAPSHOT ISOLATION многократно сократила количество DEADLOCKов, но совсем не убрала.

мда. снапшотом вы отвязали читателей, и там дедлоки с S блокировками у васушли.
Разбирайте объекты которые вы изменяете в триггерах. Ну и вообще разберитесь что такое дедлок.
зы Большая картинка не есть в xml
8 сен 17, 14:35    [20781962]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele,

автор
Вот: Похоже-таки, что разные таблицы:

получить deadlock на одном объекте можно, но это абсолютно не ваш случай
8 сен 17, 14:36    [20781965]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
получить deadlock на одном объекте можно, но это абсолютно не ваш случай


К сообщению приложен файл. Размер - 24Kb
8 сен 17, 14:43    [20781993]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele,

это лок на разных операциях с объектом. Вы же объявляете что у вас DELETE и всё. Смотрите на свои триггеры
8 сен 17, 14:50    [20782029]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Yuri Abele,

тот тип дедлока что вы привели в виде xml должен был уйти по сути после переключения на RCSI, там у вас борьба читатель-писатель.
8 сен 17, 14:52    [20782040]     Ответить | Цитировать Сообщить модератору
 Re: DELETE приводит к DEADLOCK  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Я вообще не понимаю, как при RCSI может к DEDALOCKам привести.

Мог бы мне кто-то объяснить на каких-то простых примерах?
8 сен 17, 14:59    [20782077]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить