Results 1 to 2 of 2

Thread: SQL record locking / sharing.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2017
    Posts
    26

    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.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width