Jak IDENTITY_INSERT wpływa na współbieżność?

Próbuję pomóc klientowi z zewnętrznym dodatkiem SAP, który ma usterkę związaną z publikowaniem i nie ma już wsparcia.

W pewnych okolicznościach archiwizuje i niekompletne przesyłanie z tabeli kolejki wysyłania do tabeli archiwizacji wysyłania. Muszę przenieść te zarchiwizowane wyniki z powrotem do kolejki.

Identyfikator kolejki jest kolumną tożsamości i chciałbym, aby była taka sama.

Pytanie brzmi: jeśli wykonam wstawianie tożsamości / wstawianie / wstawianie tożsamości wyłączone, czego mogę się spodziewać w odniesieniu do współbieżności z procesami, które tworzą wpisy w kolejce i oczekują, że kolumna tożsamości zostanie wygenerowana automatycznie?

Docenione zostaną również wszelkie wskazówki dotyczące najlepszego sposobu wykazania takiego zachowania.

Samo ustawienie IDENTITY_INSERT ONnie eliminuje współbieżności - nie nakłada żadnych wyłącznych blokad na stół, a jedynie blokadę stabilności schematu (Sch-S).

Więc teoretycznie może się zdarzyć, przy zachowaniu domyślnym, że możesz to zrobić w sesji 1:

BEGIN TRANSACTION;

-- 1
SET IDENTITY_INSERT dbo.tablename ON;

-- 2
INSERT dbo.tablename(id, etc) VALUES(100, 'foo'); -- next identity is now 101

-- 3
INSERT dbo.tablename(id, etc) VALUES(101, 'foo'); -- next identity is now 102

-- 4
SET IDENTITY_INSERT dbo.tablename OFF;

COMMIT TRANSACTION;

W innej sesji możesz wstawić wiersze do tabeli w punktach 1, 2, 3 lub 4. To może wydawać się dobrą rzeczą, z wyjątkiem tego, co dzieje się w przypadku każdej wstawki, która występuje między 2 a 3, jest to, że automatycznie generowana jest wartość przez inną sesję opiera się na wynikach instrukcji 2 - więc wygeneruje 101, a następnie instrukcja 3 zakończy się niepowodzeniem z naruszeniem klucza podstawowego. Jest to dość proste w konfiguracji i przetestowaniu się za pomocą niektórych WAITFORs:

-- session 1
-- DROP TABLE dbo.what;
CREATE TABLE dbo.what(id INT IDENTITY PRIMARY KEY);
GO
BEGIN TRANSACTION;

SET IDENTITY_INSERT dbo.what ON;

INSERT dbo.what(id) VALUES(32);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(33);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(34);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(35);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(36);

SET IDENTITY_INSERT dbo.what OFF;

COMMIT TRANSACTION;

Po uruchomieniu tej partii uruchom ją w innym oknie:

-- session 2
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

Sesja 2 powinna zawsze wstawiać wartości od 1 do 20, prawda? Z wyjątkiem tego, że ponieważ tożsamość bazowa została zaktualizowana przez ręczne wstawianie sesji 1, w pewnym momencie sesja 2 przejdzie tam, gdzie zakończyła się sesja 1, i wstawi 32, 33 lub 34 itd. Można to zrobić, ale wtedy sesja 1 zakończy się niepowodzeniem na następnej wkładce z naruszeniem PK (która wygrywa może być tylko kwestią czasu).

Jednym ze sposobów obejścia tego jest wywołanie a TABLOCKna pierwszej wstawce:

INSERT dbo.what WITH (TABLOCK) (id) VALUES(32);

Spowoduje to zablokowanie innych użytkowników próbujących wstawić (lub zrobić cokolwiek, naprawdę) z tą tabelą, dopóki nie zakończysz przenoszenia tych zarchiwizowanych wierszy z powrotem. To oczywiście ogranicza współbieżność, ale w ten sposób chcesz blokować działanie. Mam nadzieję, że nie dzieje się to tak często, gdy cały czas blokujesz innym ludziom.

Kilka innych obejść:

• przestańcie dbać o IDENTITYwygenerowaną wartość. Kogo to obchodzi? Użyj UNIQUEIDENTIFIER(być może wygenerowanego w oddzielnej tabeli IDENTITYjako surogat), jeśli oryginalna wartość jest bardzo ważna.
• zmień proces archiwizacji, aby używał „miękkiego usuwania”, w którym coś jest początkowo oznaczone jako zarchiwizowane, a archiwizacja nie jest trwała aż do późniejszego terminu. Wtedy każdy proces, który próbuje je wycofać, może po prostu przeprowadzić bezpośrednią aktualizację i naprawić flagę miękkiego usuwania.