"Violation of PRIMARY KEY constraint" bei einer Spalte vom Typ Identity bei sequentiellem Einfügen von Datensätzen

Question

Hallo,

bei einer Datenbankanwendung auf SQL Server 2012 (11.0.2100.60 (X64)) erhalte ich "sehr selten" in unterschiedlichen Tabellen die Meldung „Violation of PRIMARY KEY constraint …“.

Der Primary Key ist jeweils eine Spalte vom Data Type „int“ mit „Identity=True“ (Seed=1, Increment=1), das Insert Statement wird innerhalb einer „Stored Procedure“ ohne Verwendung der Identity-Spalte durchgeführt. Die Inserts werden serialisiert abgesetzt ohne die Verwendung von Transaktionen.

Wurde dieses Phänomen bereits berichtet?

Vielen Dank!

Stefan

Answer 1

Hallo Stefan,

vorab: Deine SQL Server Installation ist veraltet. Du setzt SQL Server 2012 RTM ein, aktuell wäre SP 4, es gibt danach noch ein Hotfox Update Package. Siehe dazu:

  https://sqlserverbuilds.blogspot.com/#sql2012

Da das Verhalten auch aufgrund eines (evtl. bereits behobenen) Bugs auftreten kann, aktualier bitte zuerst deine Installation, es macht wenig Sinn, die Ursachen zu suchen, wenn es durch ein Update behoben wird.

Falls es danach immer noch auftritt, meld dich nochmal mit mehr Details hier. (Was heißt bspw. "sehr selten" und "in unterschiedlichen Tabellen"? Wie genau sieht das INSERT INTO STatement aus? Wie werden die SPs aufgerufen? ...)

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 2

Hallo Stefan,

es handelt sich bei der Software um eine Anwendung, die als AddOn mit weiteren Applikationen ausgeliefert wird. Diese Applikationen sind verifiziert und zum Teil validiert. Ein Upgrade einer Komponente ist nicht so einfach möglich.

"Sehr selten" heißt, dass ich mindestens 2 Meldungen aus unserer installierten Basis habe, die aus mehreren hundert Systemen besteht, die wiederum seit etwa 10 Jahren auf SQL Server 2005 und seit einigen Jahren auf SQL Server 2012 laufen. Das Problem kann ich auch nicht einfach nachstellen, um zu bestätigen, dass eine neuere Version des SQL Servers die Schwäche nicht aufweist.

"In unterschiedlichen Tabellen" heißt, dass die beiden gemeldeten Issues, auf die ich mich beziehe, zwei unterschiedliche Tabellen betrifft. Der Zugriff allerdingsgeschieht in beiden Fällen durch jeweils eine Stored Procedure, die ein Insert-Statement ausführt, die alle Felder ausser die Indentitätsspalte beinhaltet.

Grüße, Stefan

Answer 3

Hallo Stefan,

normalerweise sollte das von Dir beschriebene Phänomen nicht auftreten - unabhängig von der Version. Ich habe über das Verfahren (< 2012) hier geblogged:

https://db-berater.blogspot.com/2013/06/identity-wertewarum-wird-der-wert-um.html

Ab SQL Server 2012 wird ein interner Cache für die Verwaltung der aktuellen Identity-Werte verwendet (daher kommt auch der Sprung um 1.000, wenn SQL Server neu gestartet wird).

http://www.sqlservercentral.com/blogs/the-dba-who-came-in-from-the-cold/2017/05/24/disabling-the-identity-cache-in-sql-server-2017/

Fakt ist, dass die Ermittlung der ID immer Bestandteil der Benutzertransaktion ist und nicht anderweitig verwendet werden kann.

Ich vermute eher, dass irgendeine Routine (vielleicht tatsächlich wegen der Sprünge nach einem Neustart) die Werte für IDENTITY wieder zurücksetzt. Das kann dann dazu führen, dass ein Wert doppelt vorkommt.

Du kannst das Caching deaktivieren. Dazu musst Du lediglich das Traceflag 272 als GLOBAL aktivieren:

https://docs.microsoft.com/de-de/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Die Aktivierung kannst Du entweder über eine Startup-Prozedur oder über den Konfigurations-Manager von SQL Server veranlassen. Ich würde hier eine Startup-Prozedur verwenden, da dann ein Neustart nicht erforderlich ist.

https://docs.microsoft.com/de-de/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-2017

https://docs.microsoft.com/de-de/sql/database-engine/configure-windows/configure-the-scan-for-startup-procs-server-configuration-option?view=sql-server-2017

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 4

Hallo Uwe,

ich fürchte, dass sich die Erklärungsversuche in eine falsche Richtung bewegen. Mir sind die Werte dieser ID herzlich egal und ich bin weder an Sprüngen noch an deren Behebung interessiert. Daher kann das Problem nicht an einem Versuch liegen, auf diesen Wert Einfluß zu nehmen.

Was wir allerdings in dieser Applikation machen, und vielleicht ist das die passendere Abweichung von der "normalen" Anwendung, ist, dass wir zu Beginn eines "Runs" eine Reihe von foreign keys droppen um dann eine Reihe von Tabellen zu leeren (per Truncate) und schließlich die foreign keys wieder anzulegen.

Die beiden Primary keys, bei denen die Probleme auftraten sind immerhin soweit miteinander verknüpft, dass der Primary key der einen Tabelle als Foreign key der anderen Tabelle wirkt. Die beiden Probleme sind aber nicht miteinander verknüpft und traten auf unterschiedlichen Systemen auf.

Vielen Dank! Stefan

Answer 5

Hallo Stefan,

kannst Du mal ein Beispielskript für den kompletten Prozess bereitstellen? Ein TRUNCATE setzt die IDENTITY zurück; daran kann es also nicht - direkt - liegen.

Was ist aber, wenn z. B. nur die "Master"-Tabelle gelöscht wird und dann versucht wird, in die Detail-Tabelle zu schreiben?

Wenn z. B. eine Tabelle dbo.Kunden gelöscht wird (TRUNCATE), dann befinden sich ja noch Aufträge in der Tabelle dbo.KundenAuftraege. Liegt dort z. B. der PK auf Customer_Id, Order_Id, kann es natürlich zu Verletzungen der Integrität des PK kommen.

Das ist aber alles nur Glaskugel-Lesen. Ein nachvollziehbares Beispiel der Datenstruktur wäre hier hilfreich :)

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 6

Wenn man mit Identity-Columns (o.ä. Autocolumns) als Referenzen in anderen Tabellen arbeitet, sollte man auch diesbezüglich Referential-Constraints festlegen um eben genau dies zu verhindern.
Dass eben Daten entfernt werden, für die noch Referenzen in anderen Tabellen existieren.
Wie bereits gesagt löscht ein Truncate den Inahlt einer Tabelle incl. der Identity, so dass es hier durchaus dann in den zugehörigen Tabellen zu dem beschriebenen Fehler kommt, da die Identity eben wieder von vorne beginnt.
Nun geht das in einem Mehrfeld-Index halt so lange gut, bis eben zufällig eine bereits früher vorhandene Kombination mal wieder auftaucht und zur Schutzverletzung führt.

In einem Business-Prozess ist Truncate auch nicht für Daten zu empfehlen (allenfalls ausschließlich für temporäre Arbeitstabellen ohne Referenz auf Andere), da hier auch der Transaktionsschutz verloren geht.

Übrigens: Wenn ein Referential-Constraint definiert ist, ist Truncate nicht mehr möglich.

https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php

Answer 7

Hallo Uwe,
hier sind Auszüge der Tabellen
CREATE TABLE [dbo].[ST_SAMPLE_INSTANCE](
[SAMPLEINSTANCEID] [int] IDENTITY(1,1) NOT NULL,
[SAMPLEINSTANCEALIAS] [varchar](100) NULL,
[VOLUME] [real] NULL,
:
:
CONSTRAINT [IDX_UPK_16_ST_SCE] PRIMARY KEY CLUSTERED
(
[SAMPLEINSTANCEID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
CREATE TABLE [dbo].[ST_SAMPLEINSTANCE_ACTION](
[ACTIONID] [int] NOT NULL,
[SAMPLEINSTANCEID] [int] NOT NULL,
[SAMPLEINSTANCEACTIONID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_ST_SAMPLEINSTANCE_ACTION] PRIMARY KEY CLUSTERED (
[SAMPLEINSTANCEACTIONID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UNIQ_ST_SAMPLEINSTANCE_ACTION] UNIQUE NONCLUSTERED
(
[ACTIONID] ASC,
[SAMPLEINSTANCEID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[ST_SAMPLEINSTANCE_ACTION]  WITH CHECK ADD  CONSTRAINT [FK_ST_SAMPLEINSTANCE_ACTION_ST_SAMPLE_INSTANCE] FOREIGN KEY([SAMPLEINSTANCEID])
REFERENCES
[dbo].[ST_SAMPLE_INSTANCE] ([SAMPLEINSTANCEID])
GO
Der fehlschlagende Insert sieht so aus:
ALTER PROCEDURE [dbo].[st_sp_sampleinstance_insert]
(
@sampleinstancealias varchar(100),
@volume real,
:
:
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.st_sample_instance
(
sampleinstancealias,
volume,
:
:
)
VALUES
(
@sampleinstancealias,
@volume,
:
:
)
SELECT @@IDENTITY as NewSampleInstanceId
SET NOCOUNT OFF
END
zwischenzeitlich werden alle Foreign Keys gedropped, alle beteiligten Tabellen truncated und die Foreign Keys wieder hergestellt.
Ich denke, dass wir in den letzen Jahren schon häufiger auf Probleme gestoßen wären, wenn dieses nicht richtig gemacht würde.
Die beiden mir vorliegenden Issues traten am 17.08. bzw. 06.09. auf. Bei der Recherche ist mir ein Windows-Patch vom Juli(?) über den Weg gelaufen, der angeblich eine Auswirkung auf den SQL Server hatte. wäre dies eine mögliche Erklärung?

Vielen Dank!
Stefan