SQL Server – Understanding Primary Key Violation

primary-keysql servertransaction

I'm getting an error that I thought was impossible. I have a SQL Server (ver 11.0.6594) table that looks like this:

CREATE TABLE [IDMaster](
  [ID] [int] NOT NULL,
 CONSTRAINT [PK_IDMaster] PRIMARY KEY CLUSTERED 
([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]

It's basically supposed to do what the IDENTITY does now (it's been around a while) – it is used to get new unique integer values, that are used in a few other tables.

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table – all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it is getting one.

Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

I don't know how this is possible, but apparently it is; because it just happened.

Imagine any crazy, odd mixture of other ways to try generating a new ID value from this same table, and they probably aren't far off from what exists in this system. But I can't think of any way that they could cause that particular error. It was my understanding that the transaction locking used by SQL Server enforces that, within one SQL statement that doesn't have (NOLOCK) on it or anything, there can't be any interference with the rows that it is referencing.

The only other factor, which I don't think could be related but who knows, is that this SQL is wrapped in a TRY, with some options set. Here's the SQL that got that error:

SET XACT_ABORT ON;
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRY

INSERT INTO IDMaster (ID) 
 SELECT COALESCE(
  -- make sure the new value is odd
  CASE WHEN MAX(COALESCE(ID,0)) % 2 = 0
   THEN MAX(COALESCE(ID,0)) + 1
   ELSE MAX(COALESCE(ID,0)) + 2
   END,1)
 FROM IDMaster;

END TRY
BEGIN CATCH
  WHILE (@@TRANCOUNT > 0) ROLLBACK;
  THROW;
END CATCH
WHILE (@@TRANCOUNT > 0) COMMIT;

Please explain:

  1. How is it possible to get that error?
  2. If possible, how can I prevent it from ever happening (without altering the table)?

Best Answer

Your theory is wrong. Reading from your "identity" table doesn't lock it, the insert into it locks it. While they are part of a single compound statement, they are still seperate actions. Two (or more) connections can be reading from the table during the time between the read and the insert. The insert uses a lock, so only one of the connections will actually be able to write the value they read into the table, other connections, will, as you have discovered, get a primary key violation error.

The best solution is to use one of the builtin methods for handling this: identity columns, sequences, or guids. Roll your own solutions should generally be avoided: you simply don't have the experience or user base that the database management systems do (and you are dealing with an abstraction, so even if you were one of the programmers for one of the major database management systems, you wouldn't be able to do the same kind of things).

Related Question