Nie byłoby problemu, gdyby zmienna tabeli zawierała tylko jedną wartość. Z wieloma rzędami istnieje nowa możliwość impasu. Załóżmy, że dwa równoległe procesy (A i B) działają ze zmiennymi tabel zawierającymi (1, 2) i (2, 1) dla tej samej firmy.
Proces A odczytuje miejsce docelowe, nie znajduje wiersza i wstawia wartość „1”. Posiada wyłączną blokadę wierszy dla wartości „1”. Proces B odczytuje miejsce docelowe, nie znajduje wiersza i wstawia wartość „2”. Posiada wyłączną blokadę wierszy dla wartości „2”.
Teraz proces A musi przetworzyć wiersz 2, a proces B musi przetworzyć wiersz 1. Żaden proces nie może zrobić postępu, ponieważ wymaga blokady, która jest niezgodna z blokadą wyłączną posiadaną przez inny proces.
Aby uniknąć zakleszczeń z wieloma wierszami, wiersze muszą być przetwarzane (i dostęp do tabel) za każdym razem w tej samej kolejności . Zmienna tabeli w planie wykonania pokazanym w pytaniu jest stertą, więc wiersze nie mają wewnętrznej kolejności (istnieje duże prawdopodobieństwo, że zostaną odczytane w kolejności wstawiania, choć nie jest to gwarantowane):
Brak spójnej kolejności przetwarzania wierszy prowadzi bezpośrednio do impasu. Drugą kwestią jest to, że brak kluczowej gwarancji unikalności oznacza, że szpula stołowa jest niezbędna do zapewnienia właściwej ochrony na Halloween. Szpula jest chętną szpulą, co oznacza, że wszystkie wiersze są zapisywane w stole roboczym tempdb, zanim zostaną ponownie odczytane i odtworzone dla operatora Insert.
Ponowne zdefiniowanie TYPEzmiennej tabeli w celu włączenia klastra PRIMARY KEY:
DROP TYPE dbo.CoUserData;
CREATE TYPE dbo.CoUserData
AS TABLE
(
MyKey integer NOT NULL PRIMARY KEY CLUSTERED,
MyValue integer NOT NULL
);
Plan wykonania pokazuje teraz skan indeksu klastrowanego, a gwarancja unikatowości oznacza, że optymalizator jest w stanie bezpiecznie usunąć bufor bufora:
W testach z 5000 iteracjami MERGEinstrukcji dla 128 wątków nie wystąpiły zakleszczenia ze zmienną tabeli klastrowej. Powinienem podkreślić, że dzieje się tak tylko na podstawie obserwacji; zmienna tabeli klastrowej może również ( technicznie ) wytwarzać swoje wiersze w różnych rzędach, ale szanse na spójne zamówienie są znacznie zwiększone. Obserwowane zachowanie należy oczywiście przetestować ponownie dla każdej nowej aktualizacji zbiorczej, dodatku Service Pack lub nowej wersji SQL Server.
W przypadku, gdy nie można zmienić definicji zmiennej tabeli, istnieje inna alternatywa:
MERGE dbo.CompanyUser AS R
USING
(SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
R.CompanyId = @CompanyID
AND R.UserID = @UserID
AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN
INSERT
(CompanyID, UserID, MyKey, MyValue)
VALUES
(@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);
Pozwala to również wyeliminować bufor (i spójność rzędu wierszy) kosztem wprowadzenia jawnego sortowania:
Ten plan nie spowodował również zakleszczenia przy użyciu tego samego testu. Skrypt reprodukcji poniżej:
CREATE TYPE dbo.CoUserData
AS TABLE
(
MyKey integer NOT NULL /* PRIMARY KEY */,
MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
CompanyID integer NOT NULL
CONSTRAINT PK_Company
PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
CompanyID integer NOT NULL,
UserID integer NOT NULL,
MyKey integer NOT NULL,
MyValue integer NOT NULL
CONSTRAINT PK_CompanyUser
PRIMARY KEY CLUSTERED
(CompanyID, UserID, MyKey),
FOREIGN KEY (CompanyID)
REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE
@DataTable AS dbo.CoUserData,
@CompanyID integer = 1,
@UserID integer = 1;
INSERT @DataTable
SELECT TOP (10)
V.MyKey,
V.MyValue
FROM
(
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();
BEGIN TRANSACTION;
-- Test MERGE statement here
ROLLBACK TRANSACTION;
(Jeśli obraz jest wyświetlany w nowej karcie, jest czytelny. Przepraszamy za mały rozmiar.)