-
Dec 3rd, 2021, 03:55 PM
#1
Thread Starter
Junior Member
SQL record locking / sharing.
Hi folks.
It's been many years since I wrote any code in anger, but I was asked a question yesterday of particular interest and I didn't have an immediate answer. Neither could I find a 'correct standard' for achieving it.
Years ago I used to write stock control and asset tracking systems. Record locking didn't come up all that often as items with serial numbers can't be scanned in multiple places at the same time. There was one system where the customer wasn't bothered with serial numbers, they only wanted to track part numbers, that was a little more tricky. Where people were assigning stock to orders I had to employ a very manual record locking process which worked fine but it was a bit of a ball ache.
The question yesterday was "How would you handle a situation where 3 tele workers were trying to assign the last 2 items in stock and inform them of the live stock count?" I explained how I would have handled it years ago, (brute force record off limits) but couldn't say how it would be done these days. I can't imagine SQL Server hasn't changed in all this time.
So, how do people achieve this today? Has SQL Server come up with any clever tricks for this situation, or is it still down to the developer to decide how they want to handle it?
I've asked this here as I was predominantly a VB programmer, so is the guy who asked me yesterday, so if anyone were to post any snippets VB would be appreciated. However I'm not asking for code, just an overview of correct current practices.
Many thanks.
-
Dec 4th, 2021, 07:08 AM
#2
Re: SQL record locking / sharing.
SQL has "transactions" that allow a single user to interact with a set of tables in the database while naturally locking out other users.
BEGIN TRAN is the SQL statement that "starts" the transaction.
Any tables (actually rows) that are touched are "locked" to that user.
COMMIT is the SQL statement that "stops" the transaction - all rows that were touched are now "unlock".
During the transaction you have to:
1) Check stock for availability
2) If available, record your "taking" of that stock
You have to keep this transaction fast and unfettered by the user interface. If you open the transaction and wait for some user event to close it, you lock your database against everyone.
There are also ways to INSERT the "taking of stock" and the "CHECKING" of stock availability in one SINGLE SQL statement, but tricks like this are still basically doing the same steps as above.
The programmer, through UI design, always has impact on how the check and lock and write sequence will run out.
Have you worked with the concepts of Optimistic vs Pessimistic locking?
https://vladmihalcea.com/optimistic-...istic-locking/
Last edited by szlamany; Dec 4th, 2021 at 07:18 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|