none
How synchronize check on a column and update it RRS feed

  • Question

  • Hello,

    I have a column called IsOpened which is meant to allow access to the record for one process.

    To implement this, I need to select the recording based on id, then check if IsOpened is false, and if it is update it to true such that other processes won't touch this recording.

    I don't know exactly how to implement checking and updating the column.

    The default built-in concurrency mechanism(optimistic concurrency) can actually help me with this.

    If if get and error when updating the row, it tells me IsOpened was already changed by someone else.

    The problem is that it's possible for other processes to update another column, without using the record by first setting the IsOpened to true.

    So I cannot use the optimistic concurrency model for checking and updating IsOpened, because the row might got changed due to updating another column different then IsOpened.

    What are other options I have?

    I thought about transactions, however in my case I don't understand how exactly it can help me.

    I am using JET database engine with C#.

    Thanks.

    Sunday, August 22, 2010 7:31 PM

Answers

  • Hi Andrewns,

    I think you can first check the IsOpened field after you retrieve the records from database, and if it is false, update it to true immediately. If another process update this same record whose IsOpened field has already been changed to "True", then a concurrency exception will be thrown and that record will not be updated by other process, this will not influence the other columns.  Another way is to use pessimistic currency model. A user who updates a row establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. But the performance of locking of records is a little low because it requires additional server resources. You can refer to the following article which talks about use pessimistic locking in your applications with transaction.

    http://articles.techrepublic.com.com/5100-10878_11-1049842.html
    http://www.learnvisualstudio.net/content/videos/2473_pessimistic_concurrency_using_transactions.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 9:27 AM
    Moderator

All replies

  • Hi Andrewns,

    I think you can first check the IsOpened field after you retrieve the records from database, and if it is false, update it to true immediately. If another process update this same record whose IsOpened field has already been changed to "True", then a concurrency exception will be thrown and that record will not be updated by other process, this will not influence the other columns.  Another way is to use pessimistic currency model. A user who updates a row establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. But the performance of locking of records is a little low because it requires additional server resources. You can refer to the following article which talks about use pessimistic locking in your applications with transaction.

    http://articles.techrepublic.com.com/5100-10878_11-1049842.html
    http://www.learnvisualstudio.net/content/videos/2473_pessimistic_concurrency_using_transactions.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 9:27 AM
    Moderator
  • Hi Andrewns, 

    I'm writing to follow up the post. Does the above suggestion work ?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 26, 2010 3:36 AM
    Moderator
  • Hi Andrewns,

    I am marking the reply as answer. If you have questions, please feel free to let me know.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 30, 2010 1:20 PM
    Moderator