>source

나는 테이블이있다 :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [username] [nvarchar](65) NOT NULL,
    [nickname] [nvarchar](65) NOT NULL,
    [status] [tinyint] NOT NULL,
    [email_address] [nvarchar](255) NULL,
    [activation_date] [datetime] NOT NULL,
    [deactivation_date] [datetime] NULL,
    [language] [nvarchar](16) NULL,
    [last_modify_date] [datetime] NULL,
    [creation_date] [datetime] NOT NULL,
    [suspension_start_date] [datetime] NULL,
    [suspension_end_date] [datetime] NULL,
    [authentication_code] [int] NOT NULL,
    [federation_id] [int] NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
CREATE TRIGGER [dbo].[trg_users_modify_date] ON [dbo].[users] FOR INSERT,UPDATE 
AS
BEGIN
    DECLARE @UserID INT
    
    SELECT @UserID = id FROM INSERTED
    
    UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
    WHERE id = @UserID
END
GO
ALTER TABLE [dbo].[users] ENABLE TRIGGER [trg_users_modify_date]

이것은 전체 DDL이 아니지만 중요한 부분을 모두 선택했다고 가정합니다.

사용자가 기본 키로 업데이트 할 때 교착 상태가 반복되는 것을 발견했습니다. 다음은 교착 상태 그래프입니다.

<deadlock>
  <victim-list>
    <victimProcess id="process203fe3dd468"/>
  </victim-list>
  <process-list>
    <process XDES="0x2043ed48428" clientapp="Microsoft JDBC Driver for SQL Server" clientoption1="671088672" clientoption2="128058" currentdb="7" currentdbname="wchess-dev" ecid="0" hostname="pod-backend" hostpid="0" id="process203fe3dd468" isolationlevel="read committed (2)" kpid="3848" lastattention="1900-01-01T00:00:00.740" lastbatchcompleted="2020-11-24T13:51:32.740" lastbatchstarted="2020-11-24T13:51:32.743" lasttranstarted="2020-11-24T13:51:32.740" lockMode="U" lockTimeout="4294967295" loginname="login_wchess_dev" logused="0" ownerId="195074352" priority="0" sbid="0" schedulerid="1" spid="111" status="suspended" taskpriority="0" trancount="2" transactionname="implicit_transaction" waitresource="RID: 7:1:1911:29" waittime="5003" xactid="195074352">
      <executionStack>
        <frame line="1" procname="adhoc" sqlhandle="0x02000000fa186a2f32f20072197494be8c0806011fa23fc20000000000000000000000000000000000000000" stmtend="848" stmtstart="324">
unknown    </frame>
        <frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]   </inputbuf>
    </process>
    <process XDES="0x204399e4428" clientapp="Microsoft JDBC Driver for SQL Server" clientoption1="671219744" clientoption2="128058" currentdb="7" currentdbname="wchess-dev" ecid="0" hostname="pod-backend" hostpid="0" id="process203c32feca8" isolationlevel="read committed (2)" kpid="2680" lastattention="1900-01-01T00:00:00.743" lastbatchcompleted="2020-11-24T13:51:32.743" lastbatchstarted="2020-11-24T13:51:32.743" lasttranstarted="2020-11-24T13:51:32.740" lockMode="U" lockTimeout="4294967295" loginname="login_wchess_dev" logused="628" ownerId="195074353" priority="0" sbid="0" schedulerid="1" spid="109" status="suspended" taskpriority="0" trancount="2" transactionname="implicit_transaction" waitresource="KEY: 7:72057594071285760 (208031161cd1)" waittime="5003" xactid="195074353">
      <executionStack>
        <frame line="8" procname="wchess-dev.dbo.trg_users_modify_date" sqlhandle="0x030007006ab0ff55e20ae70063ac000000000000000000000000000000000000000000000000000000000000" stmtend="456" stmtstart="302">
UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
  WHERE id = @UserI    </frame>
        <frame line="1" procname="adhoc" sqlhandle="0x02000000fa186a2f32f20072197494be8c0806011fa23fc20000000000000000000000000000000000000000" stmtend="848" stmtstart="324">
unknown    </frame>
        <frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <ridlock associatedObjectId="72057594044743680" dbid="7" fileid="1" id="lock203f0b88200" mode="X" objectname="wchess-dev.dbo.users" pageid="1911">
      <owner-list>
        <owner id="process203c32feca8" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process203fe3dd468" mode="U" requestType="wait"/>
      </waiter-list>
    </ridlock>
    <keylock associatedObjectId="72057594071285760" dbid="7" hobtid="72057594071285760" id="lock2040a141880" indexname="PK_users" mode="U" objectname="wchess-dev.dbo.users">
      <owner-list>
        <owner id="process203fe3dd468" mode="U"/>
      </owner-list>
      <waiter-list>
        <waiter id="process203c32feca8" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

이 쿼리를 사용하여 서버에서 수집했습니다.

WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx )
SELECT dl.deadlockgraph
FROM
(
    SELECT dl.query('.') AS deadlockgraph
    FROM fxd
        CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl)
) AS dl;

MS SQL에서 교착 상태를 디버깅하는 것이 처음입니다. 내가 알기로 업데이트를 시도하는 트리거로 인한 교착 상태 last_modify_date 다른 사람이 업데이트 한 행마다 열.

처음에 행을 구체적으로 업데이트하는 사람은 알아낼 수 없었습니다. 이러한 쿼리는 저에게 매우 혼란 스럽습니다 (서식없이 그대로 둡니다).

(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]

문제는이 교착 상태를 피하는 방법입니다. 트리거, 일부 DB 연결 매개 변수 또는 내 애플리케이션 코드를 수정해야합니까?

섬기는 사람:

Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64)    Jun 10 2020 18:25:25    Copyright (C) 2019 Microsoft Corporation    Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Hibertate 5.4.20.Final을 사용하고 있습니다.

  • 답변 # 1

    당신의 방아쇠는 고장 났지만 책임이 없습니다. SQL Server에는 행 트리거가 아닌 문 트리거가 있으므로

    CREATE TRIGGER [dbo].[trg_users_modify_date] ON [dbo].[users] FOR INSERT,UPDATE 
    AS
    BEGIN
        UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
        WHERE id in ( select id FROM INSERTED);
    END
    
    

    이 교착 상태는 PK 인덱스 키 잠금과 동일한 행 (또는 동일한 lockid를 갖는 행)을 업데이트하는 두 세션에 대한 힙 RID 잠금 사이에 있으며, 하나는 힙으로 시작하고 다른 하나는 비 클러스터 된 PK. 문제를 해결하려면 테이블에 클러스터형 인덱스를 제공하면 업데이트 할 데이터 구조가 하나만 있고 먼저 업데이트 할 데이터 구조를 선택할 수 없습니다. 예 :

    alter table users drop constraint PK_users;
    alter table users add constraint PK_users 
     primary key clustered (id);
    
    

    클러스터형 인덱스는 SQL Server에서 강력하게 선호됩니다. 여기에 두 개의 데이터 구조가 있어야 할 이유가 없습니다. 모든 FK를 삭제하고 다시 만드는 것은 고통스럽고 레크리에이션에서 FK를 다시 확인해야합니다. 그러나 여전히 할 가치가 있습니다.

    PK와 힙을 단일 클러스터형 인덱스로 통합하지 않는 경우 잠금 힌트 또는 쿼리 저장소를 사용하여 쿼리 계획을 고정하거나 응용 프로그램의 교착 상태 오류 후에 다시 시도 할 수 있습니다.

    또는 한 명령문에서 전체 행을 업데이트하고 잠금 힌트로 잠금 획득 순서를 제어하거나 트리거 실행을 완전히 직렬화 할 수있는 INSTEAD OF 트리거로 에스컬레이션 할 수 있습니다.

    오늘 아침에 잠금 장치를 다시 살펴 보았습니다.

    클러스터되지 않은 PK가있는 경우 먼저 ID 별 업데이트 쿼리

    세션 1 UPDATE는 PK를 읽고 행의 RID를 찾고 키에 대한 U 잠금을 취합니다.

    세션 1 행에서 X 잠금을 사용하는 힙의 행을 업데이트합니다.

    세션 1 키의 U 잠금을 해제하고 수정 된 행에 X 잠금을 유지합니다.

    세션 2 UPDATE는 행의 RID를 찾기 위해 PK를 읽고 행에서 U 잠금을 사용합니다.

    세션 2 UPDATE가 RID에 대한 X 잠금을 요청하지만 세션 1에 의해 차단됨

    세션 1은 키에 대한 U 잠금을 요청하지만 세션 2에 의해 차단됩니다.

    교착 상태가 감지되고 세션 2의 트랜잭션 (변경 사항이 없음)이 종료됩니다.

    SQL Server는 동시성을 최대화하기 위해 UPDATE 끝과 트리거 전에 PK 키 U 잠금을 해제하지만 트리거에는 교착 상태의 가능성을 만드는 동일한 잠금이 필요하기 때문입니다. SQL Server가 트랜잭션 중간에 잠금을 해제하지 못하도록하기 위해 잠금 힌트는 (holdlock)입니다. 그래서

    update users with (holdlock) set ...

관련 자료

  • 이전 postgresql - Postgres seq 스캔에 대한 시작 시간 및 다양한 계획 해석
  • 다음 postgresql - age () 함수 postgres 사용