You don't mention how this is all handled. Various apps calling for the transaction numbers? Or is it one app calling for it (threads?). Not knowing your setup, I don't know if it makes sense, but can you use a Monitor class to handle calling for the next transaction number, or possibly use a lock statement on the method that calls for it?

Alan

>We have a situation where we store a one column, one row entry called CurrTransNo. As the name implies, it stores the next transaction number to be used. This transaction number will be used across many different transaction tables, so it must be unique across all of the tables.
>
>We're trying to come up with the safest way to avoid any concurrency issues and to avoid any instance of duplicate transaction records. We have a stored procedure that increments the NextTransNo by one, and then returns the new number as a result, so it can be used, like so...
>
>

>
>DECLARE @nNextTransNo int
>
>UPDATE NextTransTable SET @nNextTransNo = CurrTransNo + 1, CurrTransNo = CurrTransNo + 1 FROM NextTransTable
>
>SELECT @nNextTransNo
>
>GO
>

>
>Short of implementing locks in SQL...since the setting of the variable and the updating of the record are done at what appears to be the same time - can anyone think of a better way to handle this?
>
>Thanks,
>Kevin