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;
- How is it possible to get that error?
- If possible, how can I prevent it from ever happening (without altering the table)?