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

Откуда:
Сообщений: 74
Прочитал все топики про дедлоки - понял что даже без явных транзакций два оператора могут накладывать блокировки намерения, а потом при эскалации блокировок может возникать дедлок.
Понятно что очевидный способ борьбы с этим - насильное наложение максимально сильной блокировки, скажем WITH (XLOCK).

Ниже привожу лог, где пойман дедлок, в котором участвует такой оператор.

Я вот думаю - ведь HINT - это по определению СОВЕТ компилятору сделать нечто. Вопрос - а всегда ли компилятор следует нашим советам?

В любом случае - какие будут предложения?
Как бороться?

Помогите.

Deadlock encountered .... Printing deadlock information
2005-03-01 23:11:10.57 spid4
2005-03-01 23:11:10.57 spid4 Wait-for graph
2005-03-01 23:11:10.57 spid4
2005-03-01 23:11:10.57 spid4 Node:1
2005-03-01 23:11:10.57 spid4 KEY: 6:347148282:1 (4400967b4bee) CleanCnt:1 Mode: X Flags: 0x0
2005-03-01 23:11:10.57 spid4 Grant List 1::
2005-03-01 23:11:10.57 spid4 Owner:0x38a87040 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:65 ECID:0
2005-03-01 23:11:10.57 spid4 SPID: 65 ECID: 0 Statement Type: DELETE Line #: 1
2005-03-01 23:11:10.57 spid4 Input Buf: Language Event: delete from Orders with (XLOCK) where State = 'W' and AI = 'Y' and CCY1 = 'AUD' and CCY2 = 'USD' and abs(TargetRate - 0.7873) > 10*0.0001

2005-03-01 23:11:10.57 spid4 Requested By:
2005-03-01 23:11:10.57 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:59 ECID:0 Ec:(0x042F6548) Value:0x39426ac0 Cost:(0/0)
2005-03-01 23:11:10.57 spid4
2005-03-01 23:11:10.57 spid4 Node:2
2005-03-01 23:11:10.57 spid4 KEY: 6:347148282:14 (9c011a7298ad) CleanCnt:1 Mode: X Flags: 0x0
2005-03-01 23:11:10.57 spid4 Grant List 1::
2005-03-01 23:11:10.59 spid4 Owner:0x38a870c0 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:59 ECID:0
2005-03-01 23:11:10.59 spid4 SPID: 59 ECID: 0 Statement Type: DELETE Line #: 101
2005-03-01 23:11:10.59 spid4 Input Buf: Language Event: exec ml_NewRate 'GBP', 'USD', 1.9205, 1.9208

2005-03-01 23:11:10.59 spid4 Requested By:
2005-03-01 23:11:10.59 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:65 ECID:0 Ec:(0x04338548) Value:0x38a86500 Cost:(0/3C)
2005-03-01 23:11:10.59 spid4 Victim Resource Owner:
2005-03-01 23:11:10.59 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:59 ECID:0 Ec:(0x042F6548) Value:0x39426ac0 Cost:(0/0)

Если я правильно понимаю, то вторым оператором в конфликте тоже был оператор Delete.
Ниже привожу операторы delete, которые вызываются из ml_NewRate
Других точно нет.

delete from Orders with (XLOCK)
where AI = 'Y'
and CCY1 = @CCY1 and CCY2 = @CCY2
and State = 'W' and Amount > 0
and @Bid <= TargetRate
delete from Orders with (XLOCK)
where AI = 'Y'
and CCY1 = @CCY1 and CCY2 = @CCY2
and State = 'W' and Amount < 0
and @Ask >= TargetRate
2 мар 05, 07:30    [1354549]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, дык ЗДЕСЬ и надо было продолжать.
2 мар 05, 07:42    [1354552]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А можно планы всех трех запросов увидеть?
2 мар 05, 10:05    [1354851]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Какие индексы есть у таблицы Orders ?
2 мар 05, 10:11    [1354874]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Да там и без индексах при вот таких инсинуациях

and @Bid <= TargetRate 
сплошной скан да и только...
2 мар 05, 10:16    [1354896]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
Вот кусок из скрипта.
Планы сейчас сделаю...


CREATE TABLE [dbo].[Orders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CCY1] [char] (3) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[CCY2] [char] (3) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Acc_ID] [int] NOT NULL ,
[RegDate] [datetime] NOT NULL ,
[Amount] [numeric](18, 2) NOT NULL ,
[ExecType] [char] (1) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[ExpiryType] [char] (3) COLLATE Cyrillic_General_CI_AS NULL ,
[ExpiryDate] [datetime] NULL ,
[InitiatedBy] [char] (2) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[State] [char] (1) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[TargetRate] [numeric](18, 4) NULL ,
[FactDate] [datetime] NULL ,
[FactRate] [numeric](18, 5) NULL ,
[ValueDate] [datetime] NULL ,
[CancelRequest] [char] (1) COLLATE Cyrillic_General_CI_AS NULL ,
[CancelDate] [datetime] NULL ,
[CancelByOCO] [int] NULL ,
[ExecTimer] [datetime] NULL ,
[StopLimit] [char] (1) COLLATE Cyrillic_General_CI_AS NULL ,
[EnteredBy] [varchar] (20) COLLATE Cyrillic_General_CI_AS NULL ,
[InternalTicket] [varchar] (20) COLLATE Cyrillic_General_CI_AS NULL ,
[Ticket] AS (isnull([InternalTicket],convert(varchar(20),[ID]))) ,
[AI] [char] (1) COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]

CREATE INDEX [IX_ORDERS_MAIN] ON [dbo].[Orders]([State], [StopLimit], [RegDate], [ExecType], [Acc_ID]) ON [PRIMARY]
CREATE INDEX [IX_ORDERS_AMOUNT] ON [dbo].[Orders]([Amount]) ON [PRIMARY]
CREATE INDEX [IX_ORDERS_MARKET] ON [dbo].[Orders]([CCY1], [CCY2]) ON [PRIMARY]
CREATE INDEX [IX_ORDERS_ACC_ID] ON [dbo].[Orders]([Acc_ID]) ON [PRIMARY]
2 мар 05, 10:32    [1354963]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
Подскажите как сохранить план выполнения в текстовом виде.
2 мар 05, 10:38    [1354993]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37100
Alex M
Подскажите как сохранить план выполнения в текстовом виде.

https://www.sql.ru/faq/faq_topic.aspx?fid=393
2 мар 05, 10:40    [1355001]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Alex M
Подскажите как сохранить план выполнения в текстовом виде.
SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE title_id = 'PS1372'
GO
SET SHOWPLAN_TEXT OFF
GO
2 мар 05, 10:40    [1355002]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
Планы запросов.


StmtText                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------- 

delete from Orders with (XLOCK) 
where State = 'W' and AI = 'Y' and CCY1 = 'AUD' and CCY2 = 'USD' 
and abs(TargetRate - 0.7873) > 10*0.0001

(1 row(s) affected)

StmtText                                                                                                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Assert(WHERE:(If NOT(([Expr1012] IS NULL)) then 0 else If NOT(([Expr1013] IS NULL)) then 1 else If NOT(([Expr1014] IS NULL)) then 2 else NULL))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1014] = [PROBE VALUE]))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1013] = [PROBE VALUE]))
            |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1012] = [PROBE VALUE]))
            |    |    |--Clustered Index Delete(OBJECT:([fx].[dbo].[Orders].[PK_Orders]))
            |    |    |    |--Top(ROWCOUNT est 0)
            |    |    |         |--Filter(WHERE:([Orders].[AI]='Y' AND abs([Orders].[TargetRate]-0.7873)>0.0010))
            |    |    |              |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([fx].[dbo].[Orders]))
            |    |    |                   |--Merge Join(Inner Join, MERGE:(Orders.[ID])=(Orders.[ID]), RESIDUAL:(Orders.[ID]=Orders.[ID]))
            |    |    |                        |--Index Seek(OBJECT:([fx].[dbo].[Orders].[IX_ORDERS_MARKET]), SEEK:([Orders].[CCY1]='AUD' AND [Orders].[CCY2]='USD') ORDERED FORWARD)
            |    |    |                        |--Sort(ORDER BY:(Orders.[ID] ASC))
            |    |    |                             |--Index Seek(OBJECT:([fx].[dbo].[Orders].[IX_ORDERS_MAIN]), SEEK:([Orders].[State]='W') ORDERED FORWARD)
            |    |    |--Row Count Spool
            |    |         |--Index Seek(OBJECT:([fx].[dbo].[Trades].[TradeOpen]), SEEK:([Trades].[OpenOrder_ID]=[Orders].[ID]) ORDERED FORWARD)
            |    |--Row Count Spool
            |         |--Clustered Index Seek(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), SEEK:([OCOLinks].[Order1_ID]=[Orders].[ID]) ORDERED FORWARD)
            |--Row Count Spool
                 |--Clustered Index Scan(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), WHERE:([OCOLinks].[Order2_ID]=[Orders].[ID]))




StmtText                                                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------- 

delete from Orders with (XLOCK)
where AI = 'Y' 
and CCY1 = 'USD' and CCY2 = 'JPY'
and State = 'W' and Amount > 0 
and 100 <= TargetRate

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Assert(WHERE:(If NOT(([Expr1012] IS NULL)) then 0 else If NOT(([Expr1013] IS NULL)) then 1 else If NOT(([Expr1014] IS NULL)) then 2 else NULL))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1014] = [PROBE VALUE]))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1013] = [PROBE VALUE]))
            |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1012] = [PROBE VALUE]))
            |    |    |--Clustered Index Delete(OBJECT:([fx].[dbo].[Orders].[PK_Orders]))
            |    |    |    |--Top(ROWCOUNT est 0)
            |    |    |         |--Clustered Index Scan(OBJECT:([fx].[dbo].[Orders].[PK_Orders]), WHERE:((((([Orders].[Amount]>0.00 AND 100.0000<=[Orders].[TargetRate]) AND [Orders].[AI]='Y') AND [Orders].[CCY1]='USD') AND [Orders].[CCY2]='JPY') AND [Order
            |    |    |--Row Count Spool
            |    |         |--Index Seek(OBJECT:([fx].[dbo].[Trades].[TradeOpen]), SEEK:([Trades].[OpenOrder_ID]=[Orders].[ID]) ORDERED FORWARD)
            |    |--Row Count Spool
            |         |--Clustered Index Seek(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), SEEK:([OCOLinks].[Order1_ID]=[Orders].[ID]) ORDERED FORWARD)
            |--Row Count Spool
                 |--Clustered Index Scan(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), WHERE:([OCOLinks].[Order2_ID]=[Orders].[ID]))


StmtText                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------- 

delete from Orders with (XLOCK)
where AI = 'Y' 
and CCY1 = 'USD' and CCY2 = 'JPY'
and State = 'W' and Amount < 0 
and 100 >= TargetRate

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Assert(WHERE:(If NOT(([Expr1012] IS NULL)) then 0 else If NOT(([Expr1013] IS NULL)) then 1 else If NOT(([Expr1014] IS NULL)) then 2 else NULL))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1014] = [PROBE VALUE]))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1013] = [PROBE VALUE]))
            |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Orders].[ID]), DEFINE:([Expr1012] = [PROBE VALUE]))
            |    |    |--Clustered Index Delete(OBJECT:([fx].[dbo].[Orders].[PK_Orders]))
            |    |    |    |--Top(ROWCOUNT est 0)
            |    |    |         |--Clustered Index Scan(OBJECT:([fx].[dbo].[Orders].[PK_Orders]), WHERE:((((([Orders].[Amount]<0.00 AND 100.0000>=[Orders].[TargetRate]) AND [Orders].[AI]='Y') AND [Orders].[CCY1]='USD') AND [Orders].[CCY2]='JPY') AND [Order
            |    |    |--Row Count Spool
            |    |         |--Index Seek(OBJECT:([fx].[dbo].[Trades].[TradeOpen]), SEEK:([Trades].[OpenOrder_ID]=[Orders].[ID]) ORDERED FORWARD)
            |    |--Row Count Spool
            |         |--Clustered Index Seek(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), SEEK:([OCOLinks].[Order1_ID]=[Orders].[ID]) ORDERED FORWARD)
            |--Row Count Spool
                 |--Clustered Index Scan(OBJECT:([fx].[dbo].[OCOLinks].[PK_OCOLinks]), WHERE:([OCOLinks].[Order2_ID]=[Orders].[ID]))
2 мар 05, 10:50    [1355056]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
2 Alex M
В следующий раз включайте скрипты, планы и т.п. в теги
[src][/SRC]
2 мар 05, 10:52    [1355069]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Вместо
and 100 >= TargetRate
попробуйте писать
and TargetRate <= 100
Да и
and abs(TargetRate - 0.7873) > 10*0.0001
не мешало бы привести к божьему виду.
2 мар 05, 11:26    [1355239]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
aag
Member

Откуда: Москва
Сообщений: 1955
автор
Я вот думаю - ведь HINT - это по определению СОВЕТ компилятору сделать нечто. Вопрос - а всегда ли компилятор следует нашим советам?

Не всегда. Бывают случаи, когда он пытается работать вопреки этим советам. Но можно окончательно выломать ему руки SET FORCEPLAN ON.

Nobody faults but mine... (LZ)
2 мар 05, 11:31    [1355265]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
Спасибо за советы.

Только вот заноза-то осталась.

Есть ли РАЗУМНЫЕ ОБЪЯСНЕНИЯ этому факту?

Если бы не было хинтов - тогда все объяснялось бы эскалацией блокировок.
А вот если эти хинты есть - тогда на что валить?
Как обычно - на кривой запрос? :-)
Тогда в чем кривость?

Какой бы ни был план у этих запросов - наличие хинта исключает дедлок от эскалации блокировок.
А что еще может быть причиной?

Теоретики и знатоки, есть ли мысли по данному поводу?
Концептуальные.
2 мар 05, 11:57    [1355409]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Какой бы ни был план у этих запросов - наличие хинта исключает дедлок от эскалации блокировок.
А что еще может быть причиной?

Ключ в индексе и запись в таблице - это 2 разных ресурса. А если разные ресурсы то значит и в одном коннекте может произойти deadlock

https://www.sql.ru/forum/actualthread.aspx?tid=83697
2 мар 05, 12:04    [1355453]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
To Glory.

Я эту ветку читал несколько раз.
И вынес из нее рекомендацию ставить хинт WITH (UPDLOCK) или WITH (XLOCK).

Вопрос заключается в том почему при наличии этих хинтов дедлок все же возник.
2 мар 05, 13:42    [1355985]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Вопрос заключается в том почему при наличии этих хинтов дедлок все же возник
Хинт - это ведь не панацея. Типа поставил и пошел пить пиво.
2 мар 05, 14:01    [1356114]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Alex M
Member

Откуда:
Сообщений: 74
To Glory.

Мне бы хотелось узнать есть ли у корифеев разумное объяснение приведенному факту?

Ведь если объяснения нет, то как жить?

Хочется предсказуемости...

И если нет панацеи, то укажите хотя бы на паллиатив.

Все то что написано в документации (типа делайте транзакции возможно более короткими) к этой ситуации неприменимо.
2 мар 05, 14:07    [1356150]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
Glory
Member

Откуда:
Сообщений: 104760
To Glory.

Мне бы хотелось узнать есть ли у корифеев разумное объяснение приведенному факту?

Свое объяснение я вам привел - считаю что причина в индексах и записях.
Можете поставить простой эксперимент - удалить все индексы и проверить скрипт

Почему не помогает хинт мне отсюда не видно - смотрите что собственно блокирует один запрос и как это может повлиять на второй запрос
2 мар 05, 14:13    [1356200]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок вопреки хинту (XLOCK)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Эххх....
Второй раз даю ссылку на статью http://rsdn.ru/?article/?db/deadlocks.xml
Прочитайте, в конце концов, внимательно...
У вас полно сканов, а они первые друзья взаимоблокировок, один процесс -сканирует, находит, блокирует и продолжает сканировать, в это время второй тоже сканирует, находит, блокирует, сканирует дальше и останавливается на заблокированном ресурсе от первого, который в свою очередь тоже добежал до заблокированного вторым и также остановился.
Постарайтесь тщательней построить индексы, тогда, скорее всего и хинтов никаких не потребуется.
2 мар 05, 14:32    [1356320]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить