locked
Con-current Users RRS feed

  • Question

  • Hi Everyone,

    Now that I've built a few small application with SQL Back end, I've decided to build an application which will have multiple users accessing the same SQL database record at the same time.  I need to avoid things like "Dirty reads" where two user access the same records at the same time and the both trying to change the same field to different values. I would like to maybe lock the record once a user have retrieve it.  If a second user try to access the same record or records, I want to notify the second user that they record is currently in use. How do I implement such handle?

    In addition, I will be also be working with Dataset that fill a gridview. User will be editing the gridview record and then update the dataset which will update the sql table. How do I lock all the records that has retrieve by the dataset?

    Thanks

    Anthony

    Monday, January 6, 2014 3:08 PM

Answers

All replies

  • Hi Anthony,
    According to your description, you could create new filed named like "IsUsed" (Boolean), when you want access the corresponding records, you could query the value of "IsUsed" filed whether is used, if it returns false, this record isn’t used, and then you should modify the value of the "IsUsed" filed as true , when you are finished, set back this filed as false.
    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Edited by Marvin_Guo Tuesday, January 7, 2014 6:09 AM
    Tuesday, January 7, 2014 6:09 AM
  • Hi

    I don't consider locking the record when retrieved is good idea. Bcoz if there too many concurrent users then your application performance will be effected due to locks.

    The better approach is have a column called Rowver of type timestamp in your Sql Server Database.

    Whenever a record (Row in terms of Sql Server) is modified then the Rowver (timestamp) is updated automatically.

    We can use this feature is variety of ways. Below is one example

    When you retrieve a record, retrieve it along with the value of Rowver (timestamp). Before modifying (updating) the record check the Rowver value, if it matches with the value you retrieved then go ahead with modification, but if it does not matches with the value you retrieved then you can abort the modification operation tell the user that record has been modified by another user and ask him whether he wants to see the recent changes or ignore it and continue with his modification operation.

    There are many things to be considered if you are designing a multi-user application. If you need more details regarding it then let me know.

    Tuesday, January 7, 2014 9:10 AM
  • Hi Jeetendra,

    I really appreciate your advice about the timestamp implementation and I was actually considering this before I asked this question but as I stated, I'm new to programming and want to know of the different approach today's programmers are using to  handle concurrent users. With that being said, I would really appreciate if you can share with me more details regarding multi-user application. 

    Can you can share some detail about UPDLOCK, HOLDLOCK & NOLOCK and the impact it may have on multi-user application.  In addition, my application uses Entity Framework 4.0 and I'm not sure if EF support concurrent user implementation.  I can easily upgrade the Framework to 4.3.1 since I'm using VS 2010 for development.

    Once again, thank you for taking the time to share your ideas with me.

    Thanks,

    Anthony

    Friday, January 10, 2014 3:46 PM
  • Hi Anthony

    I found two interesting articles with which you can start

    http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve

    http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

    These articles will guide you with which you can start building your application. But it is you who have to decide what is fit for your requirements. I recommend to start building and keep testing your application with large data and concurrent users. If your application is under performing then you can come here again and learn of about specific issues about the things you implemented. By this you will learn more practically.

    Best of luck.

    • Marked as answer by zXSwordXz Monday, January 13, 2014 7:38 PM
    Saturday, January 11, 2014 4:06 PM
  • Thank you very much.  These article should get me on the right track and provide me with some key fundamentals of concurrent users on SQL.
    Monday, January 13, 2014 7:39 PM