Transaction isolation level required for secure identity escalation

Question

I am writing a small piece of software that is supposed to insert records into the database used by a commercial application. The unique primary keys (ids) in the corresponding table (s) are sequential, but do not seem to be set to "auto increment". Thus, I assume that I will need to find the largest identifier, increment it and use this value for the record that I am 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 completed its insertion, you will receive two identical identifiers and a failure when trying to insert the last one. You can verify this failure and try again, but a simpler solution would be to set the transaction isolation level. For this I need a SERIALIZABLE or lower level?

Also, is this usually a good way to solve a problem? Are there any other ways to do this?

Answer 1

One way to do this is to combine your first two lines into an 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 ( max (id) +1 Some_Table) st2

, , - ... , .

, - max_keys - , .

Answer 2

this

Answer 3

( SQL Server):

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

Answer 4

( , ) , . SQL:

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