Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Deadlock when Update/Insert with Primary and Non-clustered index / 9 сообщений из 9, страница 1 из 1
17.01.2022, 12:34
    #40127185
notemac
notemac
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Коллеги, добрый день.
Таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE [dbo].[XEvents](
	[Id] [int] IDENTITY(-2147483647,1) NOT NULL,
	[XEventTypeId] [tinyint] NOT NULL,
	[XEventId] [int] NOT NULL,
	[GameId] [smallint] NOT NULL,
 CONSTRAINT [PK_XEvents] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))

CREATE UNIQUE NONCLUSTERED INDEX [UNCI_XEvents_XEventTypeId_XEventId] ON [dbo].[XEvents]
(
	[XEventTypeId] ASC,
	[XEventId] ASC
)


Процедура:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
CREATE PROCEDURE [dbo].[AddUpdateEvent]
	@Id INT = -2147483648
	, @XEventTypeId TINYINT
    , @XEventId     INT
    , @GameId     SMALLINT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	
    BEGIN TRANSACTION

    DECLARE @T TABLE(Id INT NULL);

    UPDATE dbo.XEvents WITH (UPDLOCK, SERIALIXABLE)
    SET 
        GameId = ISNULL(@GameId, GameId)
    OUTPUT 
        INSERTED.Id INTO @T
    WHERE
        Id = @Id -- PRIMARY KEY CLUSTERED INDEX
        OR
        (   -- NON_CLUSTERED INDEX on XEventTypeId and XEventId
            XEventTypeId = @XEventTypeId
            AND
            XEventId = @XEventId
        );

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO dbo.XEvents (
            XEventTypeId
            , XEventId
            , GameId
        )
        VALUES (
            @XEventTypeId
            , @XEventId
            , ISNULL(@GameId, 0)
        )

        SELECT CAST(SCOPE_IDENTITY() AS INT) AS Id;
    END
    ELSE
    BEGIN
        SELECT Id FROM @T;
    END

    COMMIT TRANSACTION
    RETURN 0;
END



Время от времени возникает Deadlock (приложил скрин). Как можно поправить процедуру, чтобы разрешить взаимные блокировки?
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
<deadlock>
 <victim-list>
  <victimProcess id="process2a0361f8ca8" />
 </victim-list>
 <process-list>
  <process id="process2a0361f8ca8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594046709760 (4133a597a19b)" waittime="3609" transactionname="user_transaction" lasttranstarted="2022-01-17T03:16:08.430" XDES="0x2a01cda0428" lockMode="U" schedulerid="34" kpid="35792" status="suspended" spid="174" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-01-17T03:16:08.417" lastbatchcompleted="2022-01-17T03:16:08.417" lastattention="1900-01-01T00:00:00.417"  isolationlevel="read committed (2)" xactid="1895003543"  lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="dbo.AddUpdateEvent" line="27" stmtstart="1302" stmtend="2544">
UPDATE dbo.XEvents WITH (UPDLOCK, SERIALIZABLE)
		SET 
			GameId = ISNULL(@GameId, GameId)
		OUTPUT 
			INSERTED.Id INTO @T
		WHERE
			Id = @Id
			OR
			(
				XEventTypeId = @XEventTypeId
				AND
				XEventId = @XEventId    </frame>
   </executionStack>
   <inputbuf>
Proc [Object Id = 1765581328]   </inputbuf>
  </process>
  <process id="process29c2faa0108" taskpriority="0" logused="0" waitresource="KEY: 7:72057594046644224 (f7121353c89e)" waittime="3609" transactionname="user_transaction" lasttranstarted="2022-01-17T03:16:08.427" XDES="0x29f40774428" lockMode="U" schedulerid="1" kpid="21624" status="suspended" spid="122" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-01-17T03:16:08.427" lastbatchcompleted="2022-01-17T03:16:08.427" lastattention="1900-01-01T00:00:00.427" hostpid="25516" isolationlevel="read committed (2)" xactid="1895003512" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="dbo.AddUpdateEvent" line="27" stmtstart="1302" stmtend="2544">
UPDATE dbo.XEvents WITH (UPDLOCK, SERIALIZABLE)
		SET 
			ChampId = ISNULL(@ChampId, ChampId)
		OUTPUT 
			INSERTED.Id INTO @T
		WHERE
			Id = @Id
			OR
			(
				XEventTypeId = @XEventTypeId
				AND
				XEventId = @XEventId    </frame>
   </executionStack>
   <inputbuf>
Proc [Object Id = 1765581328]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594046709760" objectname="dbo.XEvents" indexname="UNCI_XEvents_XEventTypeId_XEventId" id="lock29fb2e9bd80" mode="U" associatedObjectId="72057594046709760">
   <owner-list>
    <owner id="process29c2faa0108" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process2a0361f8ca8" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594046644224" objectname="dbo.XEvents" indexname="PK_XEvents" id="lock29faec84680" mode="U" associatedObjectId="72057594046644224">
   <owner-list>
    <owner id="process2a0361f8ca8" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process29c2faa0108" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 13:03
    #40127190
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Ну, если савсем незатейливо

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
CREATE PROCEDURE [dbo].[AddUpdateEvent]
	@Id INT = -2147483648
	, @XEventTypeId TINYINT
    , @XEventId     INT
    , @GameId     SMALLINT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	
    BEGIN TRANSACTION

    DECLARE @T TABLE(Id INT NULL);

    UPDATE dbo.XEvents WITH (tablelockx)
    SET 
        GameId = ISNULL(@GameId, GameId)
    OUTPUT 
        INSERTED.Id INTO @T
    WHERE
        Id = @Id -- PRIMARY KEY CLUSTERED INDEX
        OR
        (   -- NON_CLUSTERED INDEX on XEventTypeId and XEventId
            XEventTypeId = @XEventTypeId
            AND
            XEventId = @XEventId
        );

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO dbo.XEvents (
            XEventTypeId
            , XEventId
            , GameId
        )
        VALUES (
            @XEventTypeId
            , @XEventId
            , ISNULL(@GameId, 0)
        )

        SELECT CAST(SCOPE_IDENTITY() AS INT) AS Id;
    END
    ELSE
    BEGIN
        SELECT Id FROM @T;
    END

    COMMIT TRANSACTION
    RETURN 0;
END
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 13:17
    #40127193
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Если посимпотичнее

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE PROCEDURE [dbo].[AddUpdateEvent]
	@Id INT = -2147483648
	, @XEventTypeId TINYINT
    , @XEventId     INT
    , @GameId     SMALLINT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	
    DECLARE @newId INT;

    with x as ( select GameId = GameId, Id = @Id, XEventTypeId = @XEventTypeId, XEventId = @XEventId )
       , t as ( select  * from dbo.XEvents WITH(tablelockx) )
       merge t using x on (t.id = x.id) 
                       or (t.XEventTypeId = x.XEventTypeId and t.XEventId = x.XEventId ) -- за это надо дисквалифицировать пожизненно
         when not matched then insert(XEventTypeId, XEventId, GameId) values(XEventTypeId, XEventId, GameId)
         when matched then update set GameId = ISNULL(x.GameId, t.GameId), @newId = t.id
    ;

    SELECT isnull( @newId, SCOPE_IDENTITY() ) AS Id;

    RETURN 0;
END



Ну а если совсем чтоб православно - треба разъяснение о допустимых значениях

@Id INT = -2147483648

vs

@XEventTypeId TINYINT
@XEventId INT

и приоритете оных...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 13:54
    #40127195
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
notemac,

Если "время от времени", то напишите обработку взаимоблокировки через try catch. Если на пальцах, то выполняйте в цикле вставку, при получении исключения цикл должен продолжиться, если вставка успешна, то цикл завершайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 14:35
    #40127201
PaulWist
PaulWist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
notemac,

Приведите скрипт ХП [dbo].[AddUpdateEvent]

на размышление наводит хинт SERIALIXABLE :

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
    UPDATE dbo.XEvents WITH (UPDLOCK, SERIALIXABLE)
    SET 
        GameId = ISNULL(@GameId, GameId)
    OUTPUT 
        INSERTED.Id INTO @T
    WHERE
        Id = @Id -- PRIMARY KEY CLUSTERED INDEX
        OR
        (   -- NON_CLUSTERED INDEX on XEventTypeId and XEventId
            XEventTypeId = @XEventTypeId
            AND
            XEventId = @XEventId
        );
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 14:54
    #40127207
PaulWist
PaulWist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
COMMIT TRANSACTION в ХП [dbo].[AddUpdateEvent] не завершает клиентскую транзакцию, поэтому удерживаются блокировки на KEY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 16:32
    #40127239
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Владислав Колосов
notemac,

Если "время от времени", то напишите обработку взаимоблокировки через try catch. Если на пальцах, то выполняйте в цикле вставку, при получении исключения цикл должен продолжиться, если вставка успешна, то цикл завершайте.


Мягко скажем: "не очень разумный совет".
Ладно бы, речь шла об охеренной транзакции из 100500 строк кода и стольки же таблиц, но для вульгарного кода ТС - это слишком роскошно.
Если дедлок можно ликвидировать - надо ликвидировать дедлок.
Если дедлок НЕвозможно ликвидировать - надо ПОСТАРАТЬСЯ ликвидировать дедлок.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.01.2022, 21:25
    #40127305
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Надо смотреть по ситуации, например, создавать индекс для устранения взаимоблокировки может оказаться слишком дорогим по сравнению с повторным выполнением, например 1 к 1000 случаев.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.01.2022, 07:06
    #40127341
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock when Update/Insert with Primary and Non-clustered index
Владислав Колосов
Надо смотреть по ситуации, например, создавать индекс для устранения взаимоблокировки может оказаться слишком дорогим по сравнению с повторным выполнением, например 1 к 1000 случаев.


Надо учиться настоящим образом. (с) В.И.Ленин

Какие индексы?
Критическую секцию придумали на заре программирования.
А ты все еще не выучил.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Deadlock when Update/Insert with Primary and Non-clustered index / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 550ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1683916]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 182ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 269ms
total:  550ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]