views:

172

answers:

2

I'm using SQL Server 2005 for the first time, having mostly worked with MySQL in the past. I'm used to using auto_increment to create unique IDs in tables.

Anyway... I'm working in a java app, and need to do the following. Presume my table has two columns: itemID (int) and itemValue(int).

This is basically what I want to do (the dbconn methods are just pseudocode):

dbconn.execSQL("begin tran");
int nextID = dbconn.execSQLSelect("select max(itemID)+1 from itemTable");
dbconn.execSQLInsert("insert into itemTable values " + nextID + ", 1000");
dbconn.execSQL("commit tran");

Will the begin/commit tran statements deal with the possible race condition between lines 2 and 3? Or is there some TSQL equivalent of MySQL's "lock table" that I need to do?

+6  A: 

Just make the column itemID IDENTITY ON and let MS SQL engine handle it. Handling identity incrementation on your own is very very unstable.

Ender
And use the OUtput clause to return the identity if you need to or use scope_Identity(), do not use @@identity.
HLGEM
Is there some kind of "lock table" alternative that I could use here to make the auto-incrementing behavior more stable? For various reasons that would take too long to go into, I need to have explicit control over the unique IDs themselves.
DanM
+2  A: 

You should make an IDENTITY column.

SLaks