Jakie są moje opcje blokowania instrukcji Scalanie?

Mam procedurę składowaną, która wykonuje MERGEinstrukcję .

Wygląda na to, że domyślnie blokuje całą tabelę podczas scalania.

Nazywam tę procedurę przechowywaną wewnątrz transakcji, w której robię również inne rzeczy i chciałbym, aby to tylko zablokowało dotknięte wiersze.

Wypróbowałem podpowiedź, MERGE INTO myTable WITH (READPAST)która wydawała się mniej blokować. Ale w dokumencie ms było ostrzeżenie, że może wstawiać duplikaty kluczy, omijając nawet klucz podstawowy.

Oto mój schemat tabeli:

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

Oto moja procedura składowana:

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

Oto jak obserwuję blokadę:

begin tran
EXEC MergeTest 1, 1

A następnie w innej sesji:

EXEC MergeTest 2, 2

Druga sesja czeka na zakończenie pierwszej przed kontynuowaniem.

sql-server sql-server-2008 merge

— John Buchanan
źródło

WITH (READPAST)instruuje SQL Server, aby pomijał wiersze zablokowane przez inne sesje. Czy na pewno chcesz to zrobić? Ponadto, ile wierszy w tej tabeli modyfikujesz? Pokaż nam schemat tabeli (w tym indeksy) i wykonywaną MERGEinstrukcję.

— Nick Chammas,

@NickChammas dziękuję za pomoc, zaktualizowałem pytanie ze szczegółami. Wyobrażam sobie, że READPAST byłby zły ...

— John Buchanan

Aby zlokalizować StudentTotalMarksrekordy, należy zapewnić procesorowi zapytań bardziej wydajną ścieżkę dostępu . Jak napisano, zapytanie wymaga pełnego skanu tabeli z predykatem resztkowym [StudentID] = [@StudentId]zastosowanym do każdego wiersza:

Plan skanowania

Silnik przyjmuje U(aktualizuje) blokady podczas odczytu jako podstawową obronę przed powszechną przyczyną zakleszczeń konwersji. To zachowanie oznacza drugie bloki wykonania podczas próby uzyskania Ublokady w wierszu już zablokowanym X(wyłączną) blokadą przy pierwszym wykonaniu.

Poniższy indeks zapewnia lepszą ścieżkę dostępu, unikając niepotrzebnych Ublokad:

CREATE UNIQUE INDEX uq1 
ON dbo.StudentTotalMarks (StudentID) 
INCLUDE (StudentMarks);

Plan zapytań obejmuje teraz operację wyszukiwania StudentID = [@StudentId], więc Ublokady są wymagane tylko w wierszach docelowych:

Szukaj planu

Indeks nie musi być UNIQUEw stanie rozwiązać danego problemu (chociaż INCLUDEjest wymagany, aby był to indeks obejmujący to zapytanie).

Dokonywanie na stole będzie również rozwiązać problem ścieżki dostępu (i najwyraźniej zbędne kolumny mogą być usunięte). Zawsze należy wymuszać alternatywne klucze z ograniczeniem lub (i unikać dodawania bezsensownych kluczy zastępczych bez uzasadnionego powodu).StudentIDPRIMARY KEYStudentTotalMarksIdUNIQUEPRIMARY KEY

— Paul White 9
źródło