locked
Giving the same order to two different people at the same time RRS feed

  • Question

  • Hi,
    I have an order distribution system which is used by a few people. When they call to get an order from our application, we give that order to the rep by marking that order as "active", so that no one else gets it. But we are seeing a few cases where two people call the same order at the exact same time. How do we avoid that?

    It's all coded in SQL Server.

    Thank you
    Abhi

    Friday, September 16, 2016 1:49 PM

Answers

  • >I'll try with "locking" the record and see if it works.

    You do that by using a transaction, and adding the UPDLOCK hint to your table when reading.

    You then change the status to active, and then commit the transaction.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by h_abhijith Friday, September 16, 2016 3:11 PM
    Friday, September 16, 2016 3:07 PM

All replies

  • If you are not locking the record at edit time, then when you save the information, you need to check is the Status column has been update by another user.

    Then, do not overwrite the previous change in the record (Status='active') and display a message in your application that the order has already been placed in an Active status.


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Friday, September 16, 2016 1:53 PM
  • Thank you Thomas, I'm doing the second part where I'm checking if the order is being worked by the first user and displaying the message that it's already in an active status. I'll try with "locking" the record and see if it works.
    • Marked as answer by h_abhijith Friday, September 16, 2016 3:11 PM
    • Unmarked as answer by Naomi N Saturday, September 17, 2016 7:09 PM
    Friday, September 16, 2016 1:56 PM
  • >I'll try with "locking" the record and see if it works.

    You do that by using a transaction, and adding the UPDLOCK hint to your table when reading.

    You then change the status to active, and then commit the transaction.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by h_abhijith Friday, September 16, 2016 3:11 PM
    Friday, September 16, 2016 3:07 PM