Parallelisierung über ServiceBroker führt zu Primary Key Verletzung

Question

Hallo allerseits,

ich habe hier eine Prozedur, die Einträge in einer Tabelle macht. Der (auf das wesentliche vereinfachte) Aufruf für das INSERT lautet:

INSERT INTO A
(
    ID
)
SELECT
    (SELECT MAX(ID) FROM A) + ROW_NUMBER() OVER (ORDER BY B.ID ASC)
FROM
    B

Solange ich die Prozedur einzeln im SSMS aufrufe klappt das wunderbar. Um die Vorgänge in und um die Prozedur zu parallelisieren nutzen wir den ServiceBroker, der die Prozedur in 3 Instanzen aufruft. Dies führt allerdings leider zum folgenden Fehler:

Violation of PRIMARY KEY constraint 'PK_A'. Cannot insert duplicate key in object 'dbo.A'. The duplicate key value is (4903725).

Nach meinem Verständnis kann das gar nicht sein, da INSERT INTO ... SELECT - so dachte ich - eine atomare Operation ist, somit kann es gar nicht passieren, dass beide Instanzen des Broker gleichzeitig versuchen, das INSERT auszuführen. Trotzdem habe ich versucht, ganz explizit durch Nutzung einer Transaktion dafür zu sorgen, dass das INSERT Statement atomar ist, das mache ich so:

BEGIN TRAN
INSERT INTO A
(
    ID
)
SELECT
    (SELECT MAX(ID) FROM A WITH (TABLOCKX)) + ROW_NUMBER() OVER (ORDER BY B.ID ASC)
FROM
    B
COMMIT TRAN

Wie gesagt: Ich halte das selbst für überflüssig, da das INSERT INTO ... SELECT sowieso schon atomar sein sollte. Es ändert allerdings auch nichts an meinem Problem: Sobald ich mehrere Broker-Instanzen darauf loslasse, bekomme ich Primary Key Verletzungen.

Über jeden Hinweis darauf, wo mein Denkfehler liegen könnte, wäre ich dankbar!

Schöne Grüße,

Wilfried

Answer 1

Hallo Wilfried,

das Du PK-Fehler bekommst, ist logisch. Wenn zwei Prozesse gleichzeitig den aktuellen MAX(Id) ermitteln, bleibt das nicht aus.
Warum machst Du nicht gleich einen IDENTITY auf das Attribut ID.

Oder aber Du verwendest (wenn es SQL 2012) ist die neue Funktionalität von SEQUENCE
http://msdn.microsoft.com/en-us/library/ff878091.aspx

Für parallele Verarbeitung ist Dein Konzept ungeeignet!

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 2

Hallo Wilfried,

INSERT ... SELECT ist nicht automatisch atomar. Wie Du leicht feststellen kannst wenn Du eine Tabelle verwendest:

CREATE TABLE A (id int NOT NULL PRIMARY KEY);
INSERT INTO A VALUES (1000);
GO

und darauf mehrere SSMS Fenster (je mehr um so schneller) loslässt mit:

INSERT INTO A (ID) SELECT MAX(ID)+1 FROM A; -- WITH (SERIALIZABLE, UPDLOCK);
GO 100000

Ohne den auskommentierten Teil zu aktivieren, wirst Du über kurz über lang einen Duplicate Key bekommen.
Denn im Standard-Modus READ COMMITTED wird eine Sperre so bald wie möglich freigegeben und dass ist auch schon mal vor dem INSERT.

Erst durch Angabe der Sperrhinweise wird die Aktion atomar, da eine Aktualisierungssperre über das SELECT hinaus gehalten wird.

Wobei in Deinem Falle durch den Einsatz von ROW_NUMBER die Sperren relativ lange gehalten werden könnten.
Und finden weitere Zugriffe auf "B" statt ist ein Deadlock nicht ausgeschlossen.

Gruß Elmar

Answer 3

Vielen Dank für die schnellen und hilfreichen Antworten, da habe ich wieder was gelernt! IDENTITY hatte ich nicht ausprobiert, weil ich dachte, das macht für mein Problem keinen Unterschied zu der Implementierung die ich eh schon hatte - so kann man sich irren.