MERGE
명령문 을 수행하는 저장 프로 시저가 있습니다.
병합을 수행 할 때 기본적으로 전체 테이블을 잠그는 것처럼 보입니다.
다른 저장 작업을 수행하는 트랜잭션 내 에서이 저장 프로 시저를 호출하고 영향을받는 행만 잠그기를 바랍니다.
나는 힌트를 시도했지만 MERGE INTO myTable WITH (READPAST)
덜 잠그는 것처럼 보였다. 그러나 ms doc에는 기본 키조차 무시하고 중복 키를 삽입 할 수 있다는 경고가있었습니다.
내 테이블 스키마는 다음과 같습니다.
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'WANG')
INSERT INTO StudentDetails
VALUES(2,'JOHNSON')
GO
CREATE TABLE StudentTotalMarks
(
Id INT IDENTITY PRIMARY KEY,
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
GO
내 저장 프로 시저는 다음과 같습니다.
CREATE PROCEDURE MergeTest
@StudentId int,
@Mark int
AS
WITH Params
AS
(
SELECT @StudentId as StudentId,
@Mark as Mark
)
MERGE StudentTotalMarks AS stm
USING Params p
ON stm.StudentID = p.StudentId
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = p.Mark
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(p.StudentId, p.Mark);
GO
잠금을 관찰하는 방법은 다음과 같습니다.
begin tran
EXEC MergeTest 1, 1
그리고 다른 세션에서 :
EXEC MergeTest 2, 2
두 번째 세션은 진행하기 전에 첫 번째 세션이 완료되기를 기다립니다.
답변
StudentTotalMarks
레코드 를 찾기 위해 쿼리 프로세서에보다 효율적인 액세스 경로를 제공해야 합니다. 작성된대로 쿼리에는 [StudentID] = [@StudentId]
각 행에 잔존 조건자가 적용된 테이블의 전체 스캔이 필요합니다 .
U
변환 교착 상태의 일반적인 원인에 대한 기본 방어로 읽을 때 엔진은 잠금을 수행합니다 (업데이트). 이 동작 은 첫 번째 실행에 의해 (독점) 잠금으로 U
이미 잠긴 행 에서 잠금 을 얻으려고 할 때 두 번째 실행 블록을 의미합니다 X
.
다음 색인은 불필요한 U
잠금을 피하면서 더 나은 액세스 경로를 제공합니다 .
CREATE UNIQUE INDEX uq1
ON dbo.StudentTotalMarks (StudentID)
INCLUDE (StudentMarks);
쿼리 계획에는 이제 찾기 작업이 포함되어 StudentID = [@StudentId]
있으므로 U
대상 행에서만 잠금이 요청됩니다.
인덱스가되지 않은 필요 로 UNIQUE
합니다 (이 있지만 손에서 문제를 해결하는 INCLUDE
것이이 쿼리에 대한 커버링 인덱스를 만들기 위해 필요합니다).
만들기 의 테이블도 액세스 경로 문제를 해결할 것 (그리고 분명히 여분의 열을 제거 할 수있다). 항상 또는 제약 조건으로 대체 키를 적용해야합니다 (그리고 정당한 이유없이 의미없는 대리 키를 추가하지 마십시오).StudentID
PRIMARY KEY
StudentTotalMarks
Id
UNIQUE
PRIMARY KEY