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

Откуда:
Сообщений: 7868
Ломаю голову - как победить ситуацию, когда два процесса накладывают S блокировку, а, затем, разом намерены наложить SIX в одном и том же объекте.
10 июл 15, 15:03    [17877197]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов,

А графом не поделитесь? Речь о блокировках на страницы/таблицы? :)
10 июл 15, 15:10    [17877253]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
churupaha, во вложении.

К сообщению приложен файл (qq3.xdl - 10Kb) cкачать
10 июл 15, 15:27    [17877360]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Создал индекс, написал хинт with (paglock, xlock), пока тихо... В понедельник посмотрим.
Дэдлок возникал при merge.
10 июл 15, 15:41    [17877454]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов,

ну а процедуру не засветите?

чтобы выяснить, что привело к захвату S блокировки на целую таблицу. там какой-то хитрозапрос который читает большой объем db1.dbo.RES а несколько строк обновляет/вставляет?
10 июл 15, 15:44    [17877481]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов,

можно было updlock
10 июл 15, 15:46    [17877493]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

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

там выполняется MERGE (upd-ins), т.к. до UPDATE даже дело не доходит. MERGE многоэтапный, понятное дело. SERIALIZABLE не помогает. Скорее всего, дело в нужном покрывающем индексе, дабы не было блокировки страниц данных при анализе.
13 июл 15, 12:55    [17885141]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Запрос в сильно упрощенном виде (без многих полей) выглядит так:
автор
declare @tblmaxid table (id int)
declare @name vachar(30) = '1', @type varchar(30) = '2', @commnt varchar(100) = '3'

merge dbo.res with (paglock, xlock)
using (select @name,@type) t(name,res_type)
on (res.name = t.name and res.res_type = t.res_type)
when matched then
update set commnt=@commnt
when not matched then
insert (name,type,commnt)
values (@name,@type,@commnt)
output inserted.id into @tblmaxid ;
13 июл 15, 13:12    [17885227]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
declare @tblmaxid table (id int)
declare @name vachar(30) = '1', @type varchar(30) = '2', @commnt varchar(100) = '3'

merge dbo.res with (paglock, xlock)
	using (select @name,@type) t(name,res_type)
		on (res.name = t.name and res.res_type = t.res_type)
		when matched then
			update set commnt=@commnt
		when not matched then
			insert (name,type,commnt)
			values (@name,@type,@commnt)
		output inserted.id into @tblmaxid ;


Уф, сколько раз говорил себе использовать предварительный просмотр :)
13 июл 15, 13:14    [17885240]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9409
Вообще-то, merge не накладывает S на ресурсы целевой таблицы, а сразу накладывает U.
Так что у вас S на таблицу целиком или на ее страницы возникает и удерживается где-то ранее.
13 июл 15, 13:42    [17885405]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
invm
Так что у вас S на таблицу целиком или на ее страницы возникает и удерживается где-то ранее.


+ 100

trancount="2"
13 июл 15, 13:54    [17885466]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Но в графе упомянута ссылка именно на эту строку кода и процедуру, что я привёл.
13 июл 15, 14:05    [17885517]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов
Но в графе упомянута ссылка именно на эту строку кода и процедуру, что я привёл.


но как вам уже сказали S блокировка могла быть захвачена ранее. то что вы видите в графе, это на момент deadlock'a, но ведь это событие не мгновенно произошло, блокировки захватывались ранее...
13 июл 15, 14:09    [17885536]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ имеется ввиду, захватывались блокировки до тех пор пока не возникла ситуация deadlock'a. потому ищите где у вас begin tran вызывается что насчитало trancount=2 и показуйте побольше кода.
13 июл 15, 14:14    [17885568]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9409
Владислав Колосов,

SIX у вас возникает на уровне таблицы. Поэтому хинтами (paglock, xlock) вы уберете дедлоки, возникающие при модификациях в разных строках на одной странице. Если страницы будут разные - дедлок опять появится.

Вам нужно найти где ранее в транзакции накладывается S на таблицу целиком и оценить необходимость этого.
13 июл 15, 14:26    [17885648]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Vadim999
Member [скрыт]

Откуда:
Сообщений: 107
Я конечно не специалист по mssql, но не пойму зачем автор топика мучается с блокировками. Я бы на его месте решил проблему радикально и переключил БД на работу с MVCC (snapshot isolation), которую сделали еще в бородатом mssql 2005. Как это из покон веков работает в Oracle, PostgreSQL и проч.
Snapshot isolation решает все 4 проблемы консистентности данных (lost update, dirty read, non-repeatable read, phantom read), которые в 90-е пытались избежать с помощью блокировок, при этом программирование становится сильно проще и сон разработчика будет спокойным.
К тому же не требуется менять существующий код и делается всё за пару минут https://msdn.microsoft.com/ru-ru/library/tcbchxcb(v=vs.110).aspx
13 июл 15, 15:16    [17885928]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
Vadim999
Я конечно не специалист по mssql, но не пойму зачем автор топика мучается с блокировками. Я бы на его месте решил проблему радикально и переключил БД на работу с MVCC (snapshot isolation), которую сделали еще в бородатом mssql 2005. Как это из покон веков работает в Oracle, PostgreSQL и проч.

Новерное затем, что автор как раз не хочет работать как в Oracle, PostgreSQL и проч.
13 июл 15, 15:19    [17885956]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
Vadim999
Snapshot isolation решает все 4 проблемы консистентности данных (lost update, dirty read, non-repeatable read, phantom read), которые в 90-е пытались избежать с помощью блокировок, при этом программирование становится сильно проще и сон разработчика будет спокойным.

И deadlock не есть lost update, dirty read, non-repeatable read, phantom read
13 июл 15, 15:20    [17885964]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Изоляция моментальным снимком снизила производительность раза в четыре, так что нет уж. А везде прописывать read committed нет никакого желания. Попробую найти, где раньше может появиться блокировка таблицы и удерживаться. SERIALIZABLE только в одном месте применяется, не исключено, что она и удерживает. Сама процедура километровой длины, так что не буду приводить, но обращений к этой таблице в ней нет больше.

Это процедура содержит транзакцию и может быть вызвана из других процедур, отсюда счетчик транзакций 2.
13 июл 15, 16:02    [17886260]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов
SERIALIZABLE только в одном месте применяется, не исключено, что она и удерживает.


таки обратите внимание еще и на trancount=2 :)
13 июл 15, 16:04    [17886277]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
В дэдлок графе я вижу

OBJECT: 8:766911541:0 для обоих процессов и procname="db1.dbo.ADD" line="306", которые указывают на тот код, что я привёл. Или это не имеет отношения к блокировке?

lockPartition="0" означает блокировку таблицы?
13 июл 15, 16:06    [17886290]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Владислав Колосов
lockPartition="0" означает блокировку таблицы?


lock partition
13 июл 15, 16:23    [17886389]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
схема
use tempdb;
go

create table t2(id int, data char(10) default ('a'))
go

insert into t2(id) select number from master..spt_values;
go


в двух сессиях выполняем

begin transaction

set transaction isolation level serializable;

select @@trancount -- 1

select * from t2;

select * from sys.dm_tran_locks where request_session_id = @@spid;

-- забыли сделать commit/rollback

waitfor delay '00:00:05'

begin transaction

set transaction isolation level read committed;

select @@trancount -- 1

;with src as (select 7 as id)
merge 
	into t2
	using src on t2.id = src.id
when matched then
	update set data = 'upd'
when not matched by target then
	insert (id, data)
	values(src.id, 'ins');

select * from sys.dm_tran_locks where request_session_id = @@spid;


автор
Msg 1205, Level 13, State 56, Line 21
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.



select @@version



Microsoft SQL Server 2012 - 11.0.5522.0 (X64)
Jun 17 2014 17:01:31
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


граф очень похож на ваш, только у вас request SIX, у меня convert IX

К сообщению приложен файл (e3.xdl - 9Kb) cкачать
13 июл 15, 16:30    [17886433]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Спасибо, навели меня на мысль. В процедуре, в которой выполняется MERGE ранее есть вызов другой процедуры, которая ищет строки, помеченные атрибутом как удаленные для повторного использования. В этой процедуре использовался SERIALIZABLE изоляция, соответственно, удерживались S блокировки после поиска. Полагаю, что если я использую при поиске запрос с явным указанием (paglock, xlock), это решит проблему.
14 июл 15, 11:05    [17889078]     Ответить | Цитировать Сообщить модератору
 Re: Снова о Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9409
invm
Поэтому хинтами (paglock, xlock) вы уберете дедлоки, возникающие при модификациях в разных строках на одной странице. Если страницы будут разные - дедлок опять появится.
Это я фигню полную написал...

Владислав Колосов
В процедуре, в которой выполняется MERGE ранее есть вызов другой процедуры, которая ищет строки, помеченные атрибутом как удаленные для повторного использования. В этой процедуре использовался SERIALIZABLE изоляция, соответственно, удерживались S блокировки после поиска. Полагаю, что если я использую при поиске запрос с явным указанием (paglock, xlock), это решит проблему.
Вам нужно избавится от S уровня таблицы или страницы. Для этого достаточно при поиске указать хинт (updlock).
И если у вас поисковый запрос с serializable привел к S на таблицу, значит на таблице нет кластерного индекса и идет либо сканирование таблицы, либо seek по индексу, но индекс не покрывающий.
14 июл 15, 11:40    [17889280]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить