Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Record Locking - Invoice number
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

68 Posts

Posted - 06/20/2013 :  09:30:12  Show Profile  Reply with Quote
I have a table with a next invoice number field. The table is shared by other clients with their own next invoice number. Client A may have several users accessing the database. When it is time to create an invoice for client A. I want to read the next invoice number, create the invoice, then if all goes well increment the invoice number. during the time it takes to create the invoice (less than a second?) I want to prevent other users of Client A form reading the next invoice number, but I want to allow other clients (B,C...) to have access the the next invoice number table.

So I am looking for record level locking. If the invoice creation fails I need a reliable way to unlock the record.

I have come up with a 'manual' scheme that I could use if sql record locking is either not possible or not desirable. It goes like this.

I would add a locktime and lockkey field to the next invoice number table. Upon entry to the create invoice routine I would read the table and ensure the lockkey was blank. If not, I would inspect the locktime field, if it were over, say, 30 seconds ago I would assume it was a leftover lock from a failed create invoice attempt and proceed. I would then lock the table by updating the locktime and lockkey, then re-read the table to ensure my lock took effect. If so I would proceed. Upon success I would clear the lock.

So what is the best way to solve this problem?


Edited by - kpgraci on 06/20/2013 09:34:51

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 06/20/2013 :  11:39:34  Show Profile  Reply with Quote
The best way to solve this problem is to not even attempt it. Every time an invoice number is generated it should be unique - if the operation is not successful then the number is gone. That way no concurrency issues (no fancy locking necessary and no collisions).

I take it you have built in some sort of mask so that invoice numbers are sequential by client?
What is the datatype and what are some examples of the values?

Be One with the Optimizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000