Transactional isolation level needed for safely incrementing ids

Question

I'm writing a small piece of software that is to insert records into a database used by a commercial application. The unique primary keys (ids) in the relevant table(s) are sequential, but does not seem to be set to "auto increment". Thus, I assume, I will have to find the largest id, increment it and use that value for the record I'm inserting.

In pseudo-code for brevity:

id = select max(id) from some_table
id++
insert into some_table values(id, othervalues...)

Now, if another thread started the same transaction before the first one finished its insert, you would get two identical ids and a failure when trying to insert the last one. You could check for that failure and retry, but a simpler solution might be setting an isolation level on the transaction. For this, would I need SERIALIZABLE or a lower level?

Additionally, is this, generally, a sound way of solving the problem? Are the any other ways of doing it?

Answer 1

Hope this helps

Answer 2

One way of doing it would be to merge your first two lines into the insert statement:

INSERT INTO Some_Table VALUES ((Select Max(id) + 1 from Some_Table),Othervalues...)

or

INSERT INTO Some_Table SELECT st2.id, Othervalues FROM (select max(id)+1 from Some_Table) st2

Otherwise, what you really want to do is lock this up in a transaction and prevent someone else from reading in that first line, and ending up with duplicate ID's... But by blocking reads, you open up a larger can of worms.

In other systems, I've seen a table used that stored the max keys - that way you could just lock the max_keys table when incrementing and your problem goes away - but sounds like you are stuck with the vendor's table structure.

Answer 3

try this (using SQL Server syntax):

INSERT INTO some_table 
        (id, othervalues...)
    SELECT
        ISNULL(max(id),0)+1, othervalues...
        from some_table WITH (UPDLOCK, HOLDLOCK)
        WHERE ...

Answer 4

We solved similar problem for usually sequential (but sometimes can be overwritten and not unique) document numbers using separate table. SQL idea:

Declare @docno Int
Begin Tran
Select @docno=lastval+1 From docnotable With(Updlock) Where doctype='xyz'
Update docnotable Set lastval=@docno Where doctype='xyz'
... do whatever you need ...
Commit Tran