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.