Deadlock when Update/Insert with Primary and Non-clustered index / Microsoft SQL Server

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Deadlock when Update/Insert with Primary and Non-clustered index

9 сообщений из 9, страница 1 из 1

Deadlock when Update/Insert with Primary and Non-clustered index

#40127185

notemac

Гость

Коллеги, добрый день.
Таблица:

Код: 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, 12:34

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127190

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Ну, если савсем незатейливо

Код: 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:03

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127193

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Если посимпотичнее

Код: 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:17

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127195

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

notemac,

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

...

Рейтинг:

0 / 0

17.01.2022, 13:54

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127201

PaulWist

Участник

Сообщения: 2 607

Рейтинг: 0 / 0

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:35

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127207

PaulWist

Участник

Сообщения: 2 607

Рейтинг: 0 / 0

COMMIT TRANSACTION в ХП [dbo].[AddUpdateEvent] не завершает клиентскую транзакцию, поэтому удерживаются блокировки на KEY.

...

Рейтинг:

0 / 0

17.01.2022, 14:54

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127239

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Владислав Колосов

Мягко скажем: "не очень разумный совет".
Ладно бы, речь шла об охеренной транзакции из 100500 строк кода и стольки же таблиц, но для вульгарного кода ТС - это слишком роскошно.
Если дедлок можно ликвидировать - надо ликвидировать дедлок.
Если дедлок НЕвозможно ликвидировать - надо ПОСТАРАТЬСЯ ликвидировать дедлок.

...

Рейтинг:

0 / 0

17.01.2022, 16:32

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127305

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Надо смотреть по ситуации, например, создавать индекс для устранения взаимоблокировки может оказаться слишком дорогим по сравнению с повторным выполнением, например 1 к 1000 случаев.

...

Рейтинг:

0 / 0

17.01.2022, 21:25

| Ответить | Цитировать | Написать

Deadlock when Update/Insert with Primary and Non-clustered index

#40127341

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Владислав Колосов

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

Какие индексы?
Критическую секцию придумали на заре программирования.
А ты все еще не выучил.

...

Рейтинг:

0 / 0

18.01.2022, 07:06

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Deadlock when Update/Insert with Primary and Non-clustered index

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=40127305&tid=1683916]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	159ms
get topic data:	8ms
get forum data:	2ms
get page messages:	39ms
get tp. blocked users:	1ms
others:	14ms

total:	254ms