Select MAX(field)+1 FROM ... Concurrency issues

Question

Hello Im afraid about concurrency on partner application cause in the last days it was having troubles with CRUDS Operations, especially with inserts. So I ran SQL Profiler and note that his insert statement dont use transaction and also he is using :

INSERT INTO TABLEA VALUES ( (SELECT MAX(NUMBERFIELD) +1 FROM TABLEA), ....);

How avoid the use of MAX()+1 for generate primary keys? I suggest to use autoincrement or transaction scopes but he dont want or maybe he dont know how to achieve that, is there another way for lead with this?

Using SQL and C++ 1.1

*Its not my code but I might consider show this post to him cause I think he have to consider that all opinions are welcome. :)

Answer 1

Have you considered making the primary key column IDENTITY? It'll make SQL Server automatically generate values for the column:

CREATE TABLE Test (
    Id int identity primary key not null
    -- ...
)

Answer 2

Have a look at the following stackoverflow thread.

Answer 3

If you code it right, you should be able to get the same concurrency out of this as with an IDENTITY solution. It is not intuitive; you would think that the locking would reduce concurrency. But I ran several tests with five distinct connections hammering the table, and proved to myself that the MAX+1 trick performed just about exactly the same as IDENTITY. You can see the discussion in the first section of the following blog post:

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/10/11/bad-habits-to-kick-expecting-identity-to-mean-something.aspx

Anyway here is the syntax I recommend (I don't really trust the INSERT ... VALUES (SUBQUERY) model, sorry):

   DECLARE @NextID INT;

   BEGIN TRANSACTION;

   SELECT @NextID = COALESCE(MAX(NumberColumn), 0) + 1
       FROM dbo.TableA WITH (UPDLOCK);

   INSERT dbo.TableA SELECT @NextID;

   COMMIT TRANSACTION;

You'll have to imagine the error handling portions (omitted for brevity) but I think you'll get the drift.

Of course don't take any of this for gospel. You will need to run tests on your hardware using your data and access patterns to determine whether concurrency will be harmed by this method. This could very well be one of those "it depends" answers.