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.
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 OptimizerTG |
|
|
|
|
|