Ponieważ używasz Sekwencji, możesz użyć tej samej NASTĘPNEJ WARTOŚCI DLA - którą masz już w Ograniczeniu domyślnym w Idpolu Klucz podstawowy - aby wygenerować nową Idwartość z wyprzedzeniem. Generowanie wartości w pierwszej kolejności oznacza, że nie musisz się martwić o jej brak SCOPE_IDENTITY, co oznacza, że nie potrzebujesz ani OUTPUTklauzuli, ani robienia dodatkowych, SELECTaby uzyskać nową wartość; będziesz miał wartość, zanim to zrobisz INSERT, i nawet nie musisz zadzierać z SET IDENTITY INSERT ON / OFF:-)
To zajmuje część ogólnej sytuacji. Druga część to obsługa problemu dwóch procesów jednocześnie, nie znajdowanie istniejącego wiersza dla dokładnie tego samego łańcucha i kontynuowanie INSERT. Chodzi o uniknięcie naruszenia unikatowego ograniczenia, które mogłoby wystąpić.
Jednym ze sposobów radzenia sobie z tego rodzaju problemami współbieżności jest wymuszenie, aby ta konkretna operacja była jednowątkowa. Można to zrobić za pomocą blokad aplikacji (które działają między sesjami). Choć są skuteczne, mogą być nieco ciężkie w sytuacji takiej jak ta, w której częstotliwość kolizji jest prawdopodobnie dość niska.
Innym sposobem radzenia sobie z kolizjami jest zaakceptowanie, że czasami się zdarzają, i radzenie sobie z nimi, a nie próba ich uniknięcia. Korzystając z TRY...CATCHkonstrukcji, możesz skutecznie wychwycić konkretny błąd (w tym przypadku: „unikalne naruszenie ograniczenia”, Msg 2601) i ponownie wykonać, SELECTaby uzyskać Idwartość, ponieważ wiemy, że teraz istnieje, ponieważ jest w CATCHbloku z tym konkretnym błąd. Inne błędy mogą być obsługiwane w typowym RAISERROR/ RETURNlub THROWsposób.
Konfiguracja testu: Sekwencja, Tabela i Indeks unikalny
USE [tempdb];
CREATE SEQUENCE dbo.MagicNumber
AS INT
START WITH 1
INCREMENT BY 1;
CREATE TABLE dbo.NameLookup
(
[Id] INT NOT NULL
CONSTRAINT [PK_NameLookup] PRIMARY KEY CLUSTERED
CONSTRAINT [DF_NameLookup_Id] DEFAULT (NEXT VALUE FOR dbo.MagicNumber),
[ItemName] NVARCHAR(50) NOT NULL
);
CREATE UNIQUE NONCLUSTERED INDEX [UIX_NameLookup_ItemName]
ON dbo.NameLookup ([ItemName]);
GO
Konfiguracja testu: procedura przechowywana
CREATE PROCEDURE dbo.GetOrInsertName
(
@SomeName NVARCHAR(50),
@ID INT OUTPUT,
@TestRaceCondition BIT = 0
)
AS
SET NOCOUNT ON;
BEGIN TRY
SELECT @ID = nl.[Id]
FROM dbo.NameLookup nl
WHERE nl.[ItemName] = @SomeName
AND @TestRaceCondition = 0;
IF (@ID IS NULL)
BEGIN
SET @ID = NEXT VALUE FOR dbo.MagicNumber;
INSERT INTO dbo.NameLookup ([Id], [ItemName])
VALUES (@ID, @SomeName);
END;
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 2601) -- "Cannot insert duplicate key row in object"
BEGIN
SELECT @ID = nl.[Id]
FROM dbo.NameLookup nl
WHERE nl.[ItemName] = @SomeName;
END;
ELSE
BEGIN
;THROW; -- SQL Server 2012 or newer
/*
DECLARE @ErrorNumber INT = ERROR_NUMBER(),
@ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(N'Msg %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);
RETURN;
*/
END;
END CATCH;
GO
Test
DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
@SomeName = N'test1',
@ID = @ItemID OUTPUT;
SELECT @ItemID AS [ItemID];
GO
DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
@SomeName = N'test1',
@ID = @ItemID OUTPUT,
@TestRaceCondition = 1;
SELECT @ItemID AS [ItemID];
GO
Pytanie od OP
Dlaczego to jest lepsze niż MERGE? Czy nie uzyskam tej samej funkcjonalności bez TRYużycia WHERE NOT EXISTSklauzuli?
MERGEma różne „problemy” (kilka referencji jest powiązanych w odpowiedzi @ SqlZim, więc nie ma potrzeby kopiowania tych informacji tutaj). I w tym podejściu nie ma dodatkowego blokowania (mniej rywalizacji), więc powinno być lepiej w przypadku współbieżności. Dzięki takiemu podejściu nigdy nie otrzymasz naruszenia Unikalnego Ograniczenia, wszystko bez żadnego HOLDLOCK, itp. Jest prawie pewne, że zadziała.
Uzasadnieniem tego podejścia jest:
- Jeśli masz wystarczającą liczbę wykonań tej procedury, więc musisz się martwić o kolizje, nie chcesz:
- podejmij więcej kroków niż jest to konieczne
- przytrzymaj blokady wszystkich zasobów dłużej niż to konieczne
- Ponieważ kolizje mogą się zdarzyć tylko w przypadku nowych wpisów (nowe wpisy przesłane dokładnie w tym samym czasie ), częstotliwość wpadania do
CATCHbloku w pierwszej kolejności będzie dość niska. Bardziej sensowne jest zoptymalizowanie kodu, który będzie działał przez 99% czasu, zamiast kodu, który będzie działał przez 1% czasu (chyba że nie ma kosztów optymalizacji obu, ale tak nie jest w tym przypadku).
Komentarz z odpowiedzi @ SqlZim (wyróżnienie dodane)
Ja osobiście wolę próbować dostosować rozwiązanie, aby w miarę możliwości unikać tego . W tym przypadku nie wydaje mi się, aby używanie blokad z serializablebyło ciężkim podejściem i byłbym pewien, że dobrze poradziłby sobie z wysoką współbieżnością.
Zgodziłbym się z tym pierwszym zdaniem, gdyby zostało zmienione w taki sposób, aby zawierało sformułowanie „i - gdy jest ostrożny”. To, że coś jest technicznie możliwe, nie oznacza, że sytuacja (tj. Zamierzony przypadek użycia) byłaby z tego korzystna.
Problem, który widzę w tym podejściu, polega na tym, że blokuje on więcej niż sugeruje się. Ważne jest, aby ponownie przeczytać cytowaną dokumentację dotyczącą „serializowalnego”, w szczególności następujące (podkreślenie dodane):
- Inne transakcje nie mogą wstawiać nowych wierszy z wartościami kluczy, które mieszczą się w zakresie kluczy odczytanych przez dowolne instrukcje w bieżącej transakcji, dopóki bieżąca transakcja się nie zakończy.
Oto komentarz w przykładowym kodzie:
SELECT [Id]
FROM dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
Słowo operacyjne to „zasięg”. Blokada jest podejmowana nie tylko od wartości w @vName, ale dokładniej od zakresu odmiejsce, w którym powinna iść ta nowa wartość (tj. między istniejącymi kluczowymi wartościami po obu stronach miejsca, w którym mieści się nowa wartość), ale nie sama wartość. Oznacza to, że inne procesy będą blokowane przed wstawianiem nowych wartości, w zależności od aktualnie badanych wartości. Jeśli wyszukiwanie odbywa się u góry zakresu, wstawianie wszystkiego, co mogłoby zajmować tę samą pozycję, zostanie zablokowane. Na przykład, jeśli istnieją wartości „a”, „b” i „d”, to jeśli jeden proces wykonuje WYBÓR na „f”, wówczas nie będzie można wstawić wartości „g”, a nawet „e” ( ponieważ którykolwiek z nich pojawi się natychmiast po „d”). Jednak wstawienie wartości „c” będzie możliwe, ponieważ nie zostanie ona umieszczona w zakresie „zarezerwowanym”.
Poniższy przykład powinien zilustrować to zachowanie:
(Na karcie zapytania (tj. Sesja) # 1)
INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'test5');
BEGIN TRAN;
SELECT [Id]
FROM dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE ItemName = N'test8';
--ROLLBACK;
(Na karcie zapytania (tj. Sesja) # 2)
EXEC dbo.NameLookup_getset_byName @vName = N'test4';
-- works just fine
EXEC dbo.NameLookup_getset_byName @vName = N'test9';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1
EXEC dbo.NameLookup_getset_byName @vName = N'test7';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1
EXEC dbo.NameLookup_getset_byName @vName = N's';
-- works just fine
EXEC dbo.NameLookup_getset_byName @vName = N'u';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1
Podobnie, jeśli istnieje wartość „C”, a wartość „A” jest wybierana (a zatem blokowana), wówczas można wstawić wartość „D”, ale nie wartość „B”:
(Na karcie zapytania (tj. Sesja) # 1)
INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'testC');
BEGIN TRAN
SELECT [Id]
FROM dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE ItemName = N'testA';
--ROLLBACK;
(Na karcie zapytania (tj. Sesja) # 2)
EXEC dbo.NameLookup_getset_byName @vName = N'testD';
-- works just fine
EXEC dbo.NameLookup_getset_byName @vName = N'testB';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1
Szczerze mówiąc, w moim sugerowanym podejściu, w przypadku wyjątku, w Dzienniku transakcji pojawią się 4 wpisy, które nie pojawią się w tym podejściu do „transakcji możliwej do serializacji”. ALE, jak powiedziałem powyżej, jeśli wyjątek zdarzy się 1% (lub nawet 5%) czasu, będzie to miało o wiele mniejszy wpływ niż o wiele bardziej prawdopodobny przypadek początkowego SELECT blokującego tymczasowo operacje WSTAWIANIA.
Innym, aczkolwiek niewielkim, problemem związanym z tym podejściem „możliwa do serializacji transakcja + klauzula OUTPUT” jest to, że OUTPUTklauzula (w obecnym użyciu) odsyła dane jako zestaw wyników. Zestaw wyników wymaga większego obciążenia (prawdopodobnie po obu stronach: w SQL Server do zarządzania wewnętrznym kursorem oraz w warstwie aplikacji do zarządzania obiektem DataReader) niż prosty OUTPUTparametr. Biorąc pod uwagę, że mamy do czynienia tylko z jedną wartością skalarną i że założeniem jest wysoka częstotliwość wykonywania, ten dodatkowy narzut zestawu wyników prawdopodobnie się sumuje.
Chociaż OUTPUTklauzula może być użyta w taki sposób, aby zwrócić OUTPUTparametr, wymagałoby to dodatkowych kroków w celu utworzenia tabeli tymczasowej lub zmiennej tabeli, a następnie wybrania wartości z tej tabeli temp / zmiennej tabeli do OUTPUTparametru.
Dalsze wyjaśnienia: Odpowiedź na odpowiedź @ SqlZim (zaktualizowana odpowiedź) na moją odpowiedź na odpowiedź @ SqlZim (w oryginalnej odpowiedzi) na moje oświadczenie dotyczące współbieżności i wydajności ;-)
Przepraszam, jeśli ta część jest trochę za długa, ale w tym momencie jesteśmy po prostu na niuansach obu podejść.
Uważam, że sposób, w jaki informacje są prezentowane, może prowadzić do fałszywych założeń dotyczących wielkości blokady, jakiej można się spodziewać podczas korzystania serializableze scenariusza przedstawionego w pierwotnym pytaniu.
Tak, przyznaję, że jestem stronniczy, choć szczerze mówiąc:
- Niemożliwe jest, aby człowiek nie był stronniczy, przynajmniej w niewielkim stopniu, a ja staram się zachować go na minimalnym poziomie,
- Podany przykład był uproszczony, ale miał on na celu zilustrowanie zachowania bez nadmiernego komplikowania go. Implikowanie nadmiernej częstotliwości nie było zamierzone, chociaż rozumiem, że ja również nie stwierdziłem wprost inaczej i można to odczytać jako sugerujące większy problem niż w rzeczywistości istnieje. Spróbuję wyjaśnić to poniżej.
- Dołączyłem również przykład blokowania zakresu między dwoma istniejącymi kluczami (drugi zestaw bloków „Karta zapytania 1” i „Karta zapytania 2”).
- Znalazłem (i zgłosiłam się na ochotnika) „ukryty koszt” mojego podejścia, że są to cztery dodatkowe wpisy w Dzienniku Tran za każdym razem, gdy
INSERTkończy się niepowodzeniem z powodu naruszenia Unikalnego Ograniczenia. Nie widziałem tego wymienionego w żadnej z innych odpowiedzi / postów.
Jeśli chodzi o podejście „JFDI” @ gbn, post „Ugly Pragmatism For The Win” Michaela J. Swarta oraz komentarz Aarona Bertranda do postu Michaela (dotyczący jego testów pokazujących, które scenariusze zmniejszyły wydajność), a także komentarz na temat „adaptacji Michaela J. Adaptacja Stewarta do procedury JFDI Try Catch @ gbn, stwierdzająca:
Jeśli wstawiasz nowe wartości częściej niż wybierając istniejące wartości, może to być bardziej wydajne niż wersja @ srutzky. W przeciwnym razie wolałbym wersję @ srutzky od tej.
W odniesieniu do dyskusji gbn / Michael / Aaron związanej z podejściem „JFDI” niewłaściwe byłoby zrównanie mojej sugestii z podejściem „JFDI” gbn. Ze względu na charakter operacji „Pobierz lub wstaw” istnieje wyraźna potrzeba wykonania tej czynności, SELECTaby uzyskać IDwartość dla istniejących rekordów. Ten WYBÓR działa jak IF EXISTSsprawdzenie, co czyni to podejście bardziej równoważnym wariantowi „CheckTryCatch” testów Aarona. Ponownie napisany kod Michaela (i twoja ostateczna adaptacja adaptacji Michaela) obejmuje również WHERE NOT EXISTSsprawdzenie tego samego. Dlatego moja sugestia (wraz z ostatecznym kodem Michaela i twoją adaptacją jego końcowego kodu) tak naprawdę nie trafi CATCHtak często. Mogą to być tylko sytuacje, w których dwie sesje,ItemNameINSERT...SELECTdokładnie w tym samym momencie, tak że obie sesje otrzymują „prawdziwą” WHERE NOT EXISTSdokładnie w tym samym momencie, a zatem obie próbują wykonać dokładnie INSERTw tym samym momencie. Ten bardzo specyficzny scenariusz zdarza się znacznie rzadziej niż wybór istniejącego ItemNamelub wstawienie nowego, ItemNamegdy żaden inny proces nie próbuje tego zrobić dokładnie w tym samym momencie .
Z WSZYSTKIMI POWYŻSZYMI UMYSŁAMI: Dlaczego wolę swoje podejście?
Najpierw spójrzmy na to, co blokuje się w podejściu „szeregowalnym”. Jak wspomniano powyżej, „zakres”, który zostaje zablokowany, zależy od istniejących wartości kluczy po obu stronach miejsca, w którym zmieściłaby się nowa wartość klucza. Początkiem lub końcem zakresu może być również odpowiednio początek lub koniec indeksu, jeśli w tym kierunku nie ma żadnej wartości klucza. Załóżmy, że mamy następujący indeks i klucze ( ^reprezentuje początek indeksu, a $reprezentuje jego koniec):
Range #: |--- 1 ---|--- 2 ---|--- 3 ---|--- 4 ---|
Key Value: ^ C F J $
Jeśli sesja 55 spróbuje wstawić kluczową wartość:
A, następnie zakres nr 1 (od ^do C) jest zablokowany: sesja 56 nie może wstawić wartości B, nawet jeśli jest unikalna i poprawna (jeszcze). Ale sesja 56 można wstawić wartości D, Goraz M.D, następnie zakres nr 2 (od Cdo F) jest zablokowany: sesja 56 nie może wstawić wartości E(jeszcze). Ale sesja 56 można wstawić wartości A, Goraz M.M, następnie zakres # 4 (od Jdo $) jest zablokowany: sesja 56 nie może wstawić wartości X(jeszcze). Ale sesja 56 można wstawić wartości A, Doraz G.
W miarę dodawania kolejnych kluczowych wartości, zakresy między kluczowymi wartościami stają się węższe, co zmniejsza prawdopodobieństwo / częstość wstawienia wielu wartości w tym samym czasie walcząc o ten sam zakres. Trzeba przyznać, że nie jest to poważny problem i na szczęście wydaje się, że problem ten z czasem maleje.
Problem z moim podejściem został opisany powyżej: dzieje się tak tylko wtedy, gdy dwie sesje próbują jednocześnie wprowadzić tę samą wartość klucza. W związku z tym sprowadza się to do tego, co ma większe prawdopodobieństwo wystąpienia: dwie różne, ale bliskie, wartości klucza są próbowane w tym samym czasie, czy próbowana jest ta sama wartość klucza w tym samym czasie? Przypuszczam, że odpowiedź leży w strukturze aplikacji wykonującej wstawki, ale ogólnie mówiąc, bardziej prawdopodobne byłoby założenie dwóch różnych wartości, które akurat dzielą ten sam zakres. Ale jedynym sposobem, aby naprawdę wiedzieć, byłoby przetestowanie obu w systemie operacyjnym.
Następnie rozważmy dwa scenariusze i sposób, w jaki radzi sobie z nimi każde podejście:
Wszystkie żądania dotyczą unikatowych wartości kluczowych:
W tym przypadku CATCHblok w mojej sugestii nigdy nie jest wprowadzany, dlatego nie ma „problemu” (tj. 4 wpisów w dzienniku tran i czas potrzebny na to). Ale w podejściu „szeregowalnym”, nawet jeśli wszystkie płytki są unikalne, zawsze będzie istniał potencjał do blokowania innych płytek w tym samym zakresie (choć nie na bardzo długo).
Wysoka częstotliwość żądań dla tej samej wartości klucza w tym samym czasie:
W tym przypadku - bardzo niski stopień wyjątkowości pod względem przychodzących żądań nieistniejących wartości klucza - CATCHblok w mojej sugestii będzie wprowadzany regularnie. Skutkiem tego będzie to, że każda nieudana wstawka będzie musiała automatycznie przywracać i zapisywać 4 wpisy w Dzienniku transakcji, co jest niewielkim spadkiem wydajności za każdym razem. Ale ogólna operacja nigdy nie powinna zawieść (przynajmniej nie z tego powodu).
(Wystąpił problem z poprzednią wersją „zaktualizowanego” podejścia, która pozwalała mu cierpieć z powodu zakleszczeń. Dodano updlockwskazówkę, aby rozwiązać ten problem i nie ma już zakleszczeń).ALE w podejściu „serializowalnym” (nawet w zaktualizowanej, zoptymalizowanej wersji) operacja zostanie zakleszczona. Dlaczego? Ponieważ serializablezachowanie zapobiega tylko INSERToperacjom w zakresie, który został odczytany, a zatem zablokowany; nie zapobiega SELECToperacjom w tym zakresie.
serializablePodejście, w tym przypadku, wydaje się nie mieć dodatkowe obciążenie i może wykonywać nieco lepsze niż to, co ja sugeruję.
Podobnie jak w przypadku wielu / większości dyskusji na temat wydajności, ponieważ istnieje tak wiele czynników, które mogą wpłynąć na wynik, jedynym sposobem, aby naprawdę poczuć, jak coś się stanie, jest wypróbowanie go w docelowym środowisku, w którym będzie działać. W tym momencie nie będzie to kwestia opinii :).