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.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
--|||JohnSouth (jsouth@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> JohnSouth (jsouth@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../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.|||JohnSouth (jsouth@.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, esquel@.sommarskog.se

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