Atomicity of INSERT with subquery

Question

Assuming

CREATE TABLE TableA
(
    IntColumn INT NOT NULL PRIMARY KEY
)
INSERT INTO TableA VALUES (0)

is the following atomic? i.e. if multiple application threads execute the following, am I at risk of an eventual PK violation?

INSERT INTO TableA
VALUES ((SELECT MAX(IntColumn) FROM TableA) + 1)

Can you point me to any documentation about this? My google-fu failed...

[I'm aware of auto-incrementing columns, but that isn't sufficient for what I'm actually doing.]

Download Adam Machanic's free [SQL Query Stress](http://www.datamanipulation.net/sqlquerystress/) utility, have it run 10, 20, 50, 100 concurrent clients, and see if you get any duplicates. If you do, have it run e.g. 20 iterations and 20 clients at the same time - I almost guarantee you'll see primary key violations, showing that you *are* getting duplicates. This code is not safe. — marc_s, Sep 13 '12 at 01:09

score 2 · Answer 1 · edited May 23 '17 at 11:49

2

This answer shows the locking hints required:

INSERT INTO TableA
WITH (TABLOCKX,HOLDLOCK) -- added
VALUES ((SELECT MAX(IntColumn) FROM TableA) + 1)

Tried that out in Adam's utility and it's working.

edited May 23 '17 at 11:49

Community

answered Sep 13 '12 at 07:14

agentnega

1

It works - but you need to be aware that be placing an **exclusive lock on the entire table**, you're severely limiting the concurrency and thus performance/throughput. That's why using `INT IDENTITY` is in fact the best solution to situations like this.... – marc_s Sep 13 '12 at 11:14
Understood. But I can't use `INT IDENTITY` because in my real application `IntColumn` isn't actually the primary key -- it is a unique index on a certain _subset_ of rows, where each subset starts numbering from 0 (therefore having duplicates). Considering that this sounds _weird_ after writing it, maybe I'll rethink the design. – agentnega Sep 13 '12 at 15:20
Sounds like you need another column to define your subsets (perhaps with a PK that includes both columns) – brichins Sep 26 '12 at 16:18

score 1 · Accepted Answer · answered Oct 04 '12 at 18:47

1

You flagged this question with SQL 2012. If that is your environment you can use the new sequence feature: http://msdn.microsoft.com/en-us/library/ff878091.aspx and http://msdn.microsoft.com/en-us/library/ff878370.aspx

answered Oct 04 '12 at 18:47

Sebastian Meine

Thanks @Sebastian - indeed that is what I ended up doing. – agentnega Oct 05 '12 at 17:34

2 Answers2