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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Record Locking - Invoice number

Author  Topic 

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2013-06-20 : 09:30:12
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?

kpg

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-20 : 11:39:34
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
TG
Go to Top of Page
   

- Advertisement -