1

I need to put numbers in sequential order (1, 2, 3, 4...) in a table column. These are invoice numbers, they need to be consecutives, no gaps, no repeated. Is a mandatory requirement, in fact is a fiscal requirement, so I can't skip it.

My current aproach is to use a second "Numbers" table with 2 columns (Id, LastNumber) with one record on it, Id = 1. This is what I'm doing now: 1. SELECT (LastNumber + 1) as Number from Numbers with (xlock, rowlock) where Id = 1 2. assign the number, do other inserts and updates to other tables. 3. UPDATE Numbers set LastNumber = @Number where Id = 1 --@Number is the number retrieved in step 1 4. End of Transaction

I'm trying to use locking, and don't know if I am correct, what is the most efficient solution to do this? As I stated above, is a mandatory requeriment, the number must be consecutives, no gaps, no repeated. The table is used in an app from several clients in a network. I use Sql Server 2008 R2.

I have found this similar question: Sequential Invoice Numbers SQL server

the solution given there is with some T-SQL code that get the max number in the table, but what happens if 2 clients called this code at the same time? 2 repetead numbers will be generated?

Thanks

Community
  • 1
  • 1
lafirma
  • 13
  • 5
  • http://stackoverflow.com/questions/751270/best-way-to-get-the-next-id-number-without-identity this may help – Devesh Oct 23 '14 at 03:50

1 Answers1

0

You'll want a transaction. By doing the update first, you're effectively locking the row from other shared locks until your transaction is completed, then returning the new number.

BEGIN TRAN
    UPDATE Numbers SET Number = Number + 1 WHERE Id = 12
    SELECT Number FROM Numbers WHERE Id = 12
COMMIT
Jason W
  • 13,026
  • 3
  • 31
  • 62
  • Thanks, in fact I use BEGIN TRAN as the first step. I made a mistake and omit this first step when I wrote the question. But I am using it. What you mean is that even if I'm using lock hints they will not work? – lafirma Oct 23 '14 at 03:48
  • I'm not saying lock hints don't work, but I usually see them cause more trouble than they solve. If you don't get them exactly right, it can get messy. I don't think they are necessary here. You can prove this works by having 2 query windows, run begin tran and then just the update statement, and then in second window try to select from the table. If you use `sp_who`, you'll notice your second query will be blocked (not deadlocking though), since the shared read lock being requested is not being granted until your commit runs. – Jason W Oct 23 '14 at 04:07