470,594 Members | 1,415 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Duplicate Reference Numbers using MAX()+1

Hi

Within a stored procedure I'm getting the next value of a reference
number using (simplified):

BEGIN TRANSACTION
@next_ref=select max(ref) from table
insert into table (ref) values (@next_ref+1)

create related records in other tables.

COMMIT TRANSACTION

I'm getting duplicate values in a multi-user network, presumably
because the new record is not commited until the transaction is
complete and another user starts another transaction and reads the same
max value.

Can anyone suggest a way of ensuring unique values? Perhaps by locking
the table for the duration.
There is already a separate identity column that increments ok.

Jul 23 '05 #1
4 2805
If you have an IDENTITY column, why do you want an incrementing "ref"
as well? Sure, you can lock the table each time but then you'll block
other inserts and turn your multi user system into a single user
system. The IDENTITY feature exists precisely to solve that problem.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
JohnSouth (js****@cix.co.uk) writes:
Within a stored procedure I'm getting the next value of a reference
number using (simplified):

BEGIN TRANSACTION
@next_ref=select max(ref) from table
insert into table (ref) values (@next_ref+1)

create related records in other tables.

COMMIT TRANSACTION

I'm getting duplicate values in a multi-user network, presumably
because the new record is not commited until the transaction is
complete and another user starts another transaction and reads the same
max value.


Add "WITH (UPDLOCK)" after the table name in the first query.

If you have a requirement that these values should be unique, you should
also add a UNIQUE constraint on this column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

Erland Sommarskog wrote:
JohnSouth (js****@cix.co.uk) writes:
Within a stored procedure I'm getting the next value of a reference
number using (simplified):

BEGIN TRANSACTION
@next_ref=select max(ref) from table
insert into table (ref) values (@next_ref+1)

create related records in other tables.

COMMIT TRANSACTION

I'm getting duplicate values in a multi-user network, presumably
because the new record is not commited until the transaction is
complete and another user starts another transaction and reads the same max value.

Add "WITH (UPDLOCK)" after the table name in the first query.

If you have a requirement that these values should be unique, you

should also add a UNIQUE constraint on this column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Thanks Erland
As I understand it, the UPDLOCK hint will still allow other users to
read the table but will stop another transaction from doing the same
select max()until the first transaction has done the update and is
complete.
Hopefully it won't have too much impact on performance.

Jul 23 '05 #4
JohnSouth (js****@cix.co.uk) writes:
As I understand it, the UPDLOCK hint will still allow other users to
read the table but will stop another transaction from doing the same
select max()until the first transaction has done the update and is
complete.


Correct. UPDLOCK is a shared lock, other processe can still read the
value. But if they use UPDLOCK they get stuck.

What you really do is to upgrade the transaction isolation level to
Serializable instead of the default READ COMMITTED. UPDLOCK is a
special tweak to prevent deadlocks. Regular serializable would have meant
that two processes could have read the max value, and then they
would have deadlocked on the INSERT statements. Thanks to the UPDLOCK
this does not happen.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

28 posts views Thread by dleecurt | last post: by
5 posts views Thread by dale.zjc | last post: by
2 posts views Thread by Jeff Johnson | last post: by
13 posts views Thread by Peter Oliphant | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.