Databases
StackOverflow2013
Postsdbo
POBest way to get the next id number without "identity"
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest way to get the next id number without "identity"
    text
    copied!<p>I have to insert some records in a table in a legacy database and, since it's used by other ancient systems, changing the table is not a solution.</p> <p>The problem is that the target table has a int primary key but no identity specification. So I have to find the next available ID and use that:</p> <pre><code>select @id=ISNULL(max(recid)+1,1) from subscriber </code></pre> <p>However, I want to prevent other applications from inserting into the table when I'm doing this so that we don't have any problems. I tried this:</p> <pre><code>begin transaction declare @id as int select @id=ISNULL(max(recid)+1,1) from subscriber WITH (HOLDLOCK, TABLOCK) select @id WAITFOR DELAY '00:00:01' insert into subscriber (recid) values (@id) commit transaction select * from subscriber </code></pre> <p>in two different windows in SQL Management Studio and the one transaction is always killed as a deadlock victim. </p> <p>I also tried <code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE</code> first with the same result...</p> <p>Any good suggestions to how I can ensure that I get the next id and use that without risking that someone else (or me!) is getting hosed?</p> <p>Sorry for not mentioning this earlier, but this is a SQL 2000 server so I can't use things like FOR UPDATE and OUTPUT </p> <h2><strong>UPDATE</strong>: This is the solution that worked for me:</h2> <pre><code>BEGIN TRANSACTION DECLARE @id int SELECT @id=recid FROM identities WITH (UPDLOCK, ROWLOCK) WHERE table_name = 'subscriber' waitfor delay '00:00:06' INSERT INTO subscriber (recid) values (@id) UPDATE identities SET recid=recid+1 WHERE table_name = 'subscriber' COMMIT transaction select * from subscriber </code></pre> <p>The WAITFOR is so that I can have multiple connections and start the query several times to provoke concurrency. </p> <p>Thanks to Quassnoi for the answer and to all you other guys that contributed! Awesome!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload