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

Откуда: Москва
Сообщений: 9407
aleks2
invm
И хинт xlock тут абсолютно не при чем и не является лечением.

Он меня шарлатаном назвал!
Практика -- критерий истины.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)   Aug 23 2012 15:56:56   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

+ Подготовка
use tempdb;
set ansi_nulls, quoted_identifier on;
go

if object_id('dbo.fnDelay', 'FN') is not null
 drop function dbo.fnDelay;
go

create function dbo.fnDelay
(
 @Milliseconds int
)
returns tinyint
as
begin
 declare @d datetime2 = sysdatetime();
 
 while datediff(ms, @d, sysdatetime()) <= @Milliseconds
  continue;
 
 return 0;
end;
go
go

create table dbo.TestTable (i int not null primary key);
go

insert into dbo.TestTable
 (i)
 select top (10000)
  row_number() over (order by (select 1))
 from
  master.dbo.spt_values t1 cross join 
  master.dbo.spt_values t2;
go

+ Эксперимент 1
Сеанс 1
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null);

insert into #t
values
 (1), (1000), (5), (514), (150), (17);

delete x
from
 #t t join
 dbo.TestTable x on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
go
Сеанс 2
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null);

insert into #t
values
 (150), (10), (15), (41), (3001), (1);

delete x
from
 #t t join
 dbo.TestTable x on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
+ Эксперимент 2
Сеанс 1
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null);

insert into #t
values
 (1), (1000), (5), (514), (150), (17);

delete x
from
 #t t join
 dbo.TestTable x with (xlock) on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
go
Сеанс 2
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null);

insert into #t
values
 (150), (10), (15), (41), (3001), (1);

delete x
from
 #t t join
 dbo.TestTable x with (xlock) on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
+ Эксперимент 3
Сеанс 1
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null primary key clustered);

insert into #t
values
 (1), (1000), (5), (514), (150), (17);

delete x
from
 #t t join
 dbo.TestTable x on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
go
Сеанс 2
use tempdb;

declare @d datetime = dateadd(s, (datediff(s, '1970', getdate()) / 10 + 1) * 10, '1970');

waitfor time @d;

begin tran;

create table #t (i int not null primary key clustered);

insert into #t
values
 (150), (10), (15), (41), (3001), (1);

delete x
from
 #t t join
 dbo.TestTable x on x.i = t.i and dbo.fnDelay(case when t.i > 0 then 100 end) = 0

rollback;
15 окт 12, 10:27    [13318349]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Dedlock по определению это проблемы порядка блокировки.
А уровень изоляции это больше область целостности и/или бизнес задачи.
Да, проблема S -> (U)X (из-за непонимания и из-за ограничений) существует.

Просто иногда проблему решить невозможно в принципе, не изменив интерфейс данных (отказавшись от триггеров на вьюхах).
UpdLock, XLock блокирует неизменяемые общие ресурсы (справочники). А голый RepeatableRead, Serializable гипотетические дедлоки (S -> X)

Только я так ине понял, проблема из-за порядка и эскалации?
15 окт 12, 13:59    [13320002]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
invm
Практика -- критерий истины.

Вот нафега фсе эти опуСы?
У тредстартера классический дедлок при повышении разделяемой блокировки до эксклюзивной в двух конкурирующих сессиях.
И лечение этого случая простым наложением сразу эксклюзивной блокировки - классическая примочка.
15 окт 12, 17:14    [13321837]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
повышении разделяемой блокировки до эксклюзивной в двух конкурирующих сессиях
U не разделяемая локировка.
15 окт 12, 19:52    [13322633]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
Mnior
aleks2
повышении разделяемой блокировки до эксклюзивной в двух конкурирующих сессиях
U не разделяемая локировка.

Не придирайся.
Последовательное наложение эксклюзивных блокировок в различной последовательности - суть то же самое.
16 окт 12, 06:35    [13323863]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
Не придирайся.
Последовательное наложение эксклюзивных блокировок в различной последовательности - суть то же самое.
Что значит не придирайся. Для того и U придумано.
Ну т.е. в данном случае типа не из-за этого.

Поэтому и спросил invm, т.к. особо не разбирался в задаче.
Mnior
Только я так ине понял, проблема из-за порядка и эскалации?
16 окт 12, 11:12    [13325052]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
invm
Member

Откуда: Москва
Сообщений: 9407
Mnior
Поэтому и спросил invm, т.к. особо не разбирался в задаче.
Mnior
Только я так ине понял, проблема из-за порядка и эскалации?
Насколько я понял, да.
16 окт 12, 11:36    [13325226]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

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

Причина ваших дедлоков -- разный порядок обращения к страницам таблицы из разных соединений. И хинт xlock тут абсолютно не при чем и не является лечением. Триггером вы просто несколько поменяли:
а) последовательность наложения блокировок, убрав немедленную конвертацию U -> X;
б) план фактической инструкции delete. У вас там наверняка clustered index seek по deleted, вот последовательность обращения к страницам в разных сессиях и стала одинаковой.

Поменяю сейчас триггер, без инструкций всяких.
"У вас там наверняка clustered index seek по deleted" да тут кластерный индекс, я так понимаю сам sql такой делает.
16 окт 12, 12:20    [13325634]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Без инструкции with(xlock) превосходно работает!
Наверно, дело получается в том, как происходит удаление, это единственная таблица, записи для удаления которой берутся из временной таблицы.
16 окт 12, 12:37    [13325788]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Более того инструкция with(xlock) требует значительно больше ресурсов - памяти.
Я сейчас протестировал ( с вариантом with(xlock)), было 3 Гб для SQL не было взаимоблокировок, как только поставил 1Гб сразу полезли взаимоблокировки Key Lock - X

Сейчас, просто оставил удаление и все.

Спасибо тебе, invm! ;)
16 окт 12, 12:47    [13325868]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
invm, примеры твои выше, просто шикарные. Сразу дают понять, кто виноват.
16 окт 12, 13:03    [13325983]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
И что интересно, ответ был дан сразу же. Однако путь к нему был долгий у меня. ;)
16 окт 12, 13:11    [13326026]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Однако, взаимоблокировки вернулись, без with(xlock)

Вот план выполнения:

+ скриншот
Картинка с другого сайта.


Что же за напасть то такая?
16 окт 12, 14:13    [13326675]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Glory
Member

Откуда:
Сообщений: 104760
iSteel
Вот план выполнения:

Это как на 1 запрос 2 плана ? Или это планы из разных коннектов ?
16 окт 12, 14:20    [13326738]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
2й план автомат вылазит, событие - удаление на триггере же висит. Вот и показывает 2й план - план триггера
16 окт 12, 14:40    [13326955]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
iSteel
Что же за напасть то такая?

1. Хе-хе. Вы ишо верите в чудеса?
2. Тогда наука бессильна.
3. Сервер может изменить план выполнения в любой момент. Изменится и порядок наложения блокировок. Кластерный индекс тут ничего не гарантирует.
4. Так что при массированных удалениях только блокировка таблицы целиком гарантированно решает проблему.

with(xlock, tablock)
16 окт 12, 15:06    [13327174]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Попробую еще кластерный индекс на таблице сделать, вместо простого.
Вроде как удаление должно пойти быстрее.
16 окт 12, 15:12    [13327228]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Вот такие блокировки лезут:
+
Картинка с другого сайта.
16 окт 12, 15:23    [13327339]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Glory
Member

Откуда:
Сообщений: 104760
iSteel
2й план автомат вылазит, событие - удаление на триггере же висит. Вот и показывает 2й план - план триггера

У вас в триггере идет удаление из той же таблице что ли ???
16 окт 12, 15:26    [13327356]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Я насколько понял, триггер этот подменяет процедуру удаления стандартную, разве не так???
16 окт 12, 15:29    [13327384]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Glory
Member

Откуда:
Сообщений: 104760
iSteel
Я насколько понял, триггер этот подменяет процедуру удаления стандартную, разве не так???

Откуда же это известно другим ?
Про наличие триггера вы только сейчас сказали
16 окт 12, 15:31    [13327402]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
Glory
iSteel
Я насколько понял, триггер этот подменяет процедуру удаления стандартную, разве не так???

Откуда же это известно другим ?
Про наличие триггера вы только сейчас сказали


Не, тредстартер давно сказал про инстедный триггер.
Но инстедный триггер ничо особо не меняет.
16 окт 12, 15:34    [13327427]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Дело в том что я не могу менять запросы, т.к. это идет из 1С.
Поэтому и был создан триггер, чтобы удалять как положено, с индексами.
16 окт 12, 15:36    [13327442]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
aleks2
Glory
пропущено...

Откуда же это известно другим ?
Про наличие триггера вы только сейчас сказали


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


Хотя нет, вру. Инстедный триггер усугубляет проблемы тредстартера, ибо дважды выполняется соединение.

ЗЫ. инстедный триггер - зло.
16 окт 12, 15:41    [13327494]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
aleks2
Хотя нет, вру. Инстедный триггер усугубляет проблемы тредстартера, ибо дважды выполняется соединение.

ЗЫ. инстедный триггер - зло.


Вроде нет новых соединений spid тот же:
+
скриншот
Картинка с другого сайта.
16 окт 12, 15:48    [13327574]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить