none
Best practices: Database Concurrency (VB.NET 2015 + SQL Server 2008) RRS feed

  • Question

  • Hi, firstly thank's for your attemption. I am working on a system that will be used by several users:

    Let's suppose the following situation:

    * A user (User A) double-clicks on a list of records in a datagridview (suppose a list of Customers)

    * That action shows The Insert/Update Customers screen with the data of the referenced Customer (it looks for it through an SQL server query by the CustomerID)

    * If at that time, another user (User B) performs the same action, opening the same Winform whit the same IDCustomer, both will be enabled to save the changes by pressing the SAVE button.

    * If User A changes the type of Customer and save the changes, the SQL record will be modified, but if User B subsequently changes the Customer's last name of the Customer and presses save, the system will record all the values ​​that are present on the screen of the Customer. The Customer will have the original customer TYPE again.

    Is there any recommended way or best practice to block the unwished changes?

    I hope you can help me Regards.

    Thursday, May 17, 2018 12:32 PM

Answers

  • What you are referring to is called "concurrency" and there are many many ways to address it. If you are updating your data through a DataSet this will be handled automatically and you simply handle the exception that occurs when the concurrency issue arises.

    Probably one of the easiest ways to handle this if you are using ExecuteNonQuery is to add a timestamp column to your table which will be changed each time a row is updated. If the timestamp column changes after the last read of a row then you can prevent the Update from occurring by comparing the current timestamp in the UI with that in the row of the database table. Just include it in the WHERE clause of the UPDATE statement and check the number of rows updated, which is returned by the call to ExecuteNonQuery. If the value is zero, then you will need to see if that row is still available (it has not been deleted) and then notify the user that the record has been changed and re-display it.

    I generally avoid row locking mechanisms since they can cause confusion and other issues.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, May 17, 2018 2:24 PM
  • Is there any recommended way or best practice to block the unwished changes?

    Put a user-id and timestamp columns on the records, and populate when record is persisted to the table.

    Hold the user-id and timestamp from the record being worked on.

    When trying to update the record, you read the current record from the database again and compare held user-id and timestamp against the ones read on the current record from the database table. And if they do not match, then you know that a user has changed the record and persisted the record to the table, before current user could save their changes to the record, which eliminates that last one wins scenario and is called optimistic concurrency checking.

    https://blogs.msdn.microsoft.com/marcelolr/2010/07/16/optimistic-and-pessimistic-concurrency-a-simple-explanation/

    • Marked as answer by CM16 Thursday, May 17, 2018 5:35 PM
    Thursday, May 17, 2018 2:47 PM
  • Also note that the DataAdapter has a built-in method of Optimistic Concurrency that compares all row values prior to updates.  If your data set can be efficiently consumed through Data/Table Adapters then this could be a relatively easy solution for you.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by CM16 Thursday, May 17, 2018 5:56 PM
    Thursday, May 17, 2018 2:53 PM
    Moderator

All replies

  • Hello,

    Look at SqlDepenency class. High level, when any changes are made to a table or tables you have setup with for SqlDependency you will be alerted of changes. From there you would write code that informs that user someone else made changes. How you handle the conflict is up to you would could range from denying both the opportunity to save changes to email both to reconcile changes.

    Other than that you would (and I don't recommend this) be to have code in a Timer component to poll for changes.

    See also a start-up example.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, May 17, 2018 1:38 PM
    Moderator
  • What you are referring to is called "concurrency" and there are many many ways to address it. If you are updating your data through a DataSet this will be handled automatically and you simply handle the exception that occurs when the concurrency issue arises.

    Probably one of the easiest ways to handle this if you are using ExecuteNonQuery is to add a timestamp column to your table which will be changed each time a row is updated. If the timestamp column changes after the last read of a row then you can prevent the Update from occurring by comparing the current timestamp in the UI with that in the row of the database table. Just include it in the WHERE clause of the UPDATE statement and check the number of rows updated, which is returned by the call to ExecuteNonQuery. If the value is zero, then you will need to see if that row is still available (it has not been deleted) and then notify the user that the record has been changed and re-display it.

    I generally avoid row locking mechanisms since they can cause confusion and other issues.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, May 17, 2018 2:24 PM
  • Is there any recommended way or best practice to block the unwished changes?

    Put a user-id and timestamp columns on the records, and populate when record is persisted to the table.

    Hold the user-id and timestamp from the record being worked on.

    When trying to update the record, you read the current record from the database again and compare held user-id and timestamp against the ones read on the current record from the database table. And if they do not match, then you know that a user has changed the record and persisted the record to the table, before current user could save their changes to the record, which eliminates that last one wins scenario and is called optimistic concurrency checking.

    https://blogs.msdn.microsoft.com/marcelolr/2010/07/16/optimistic-and-pessimistic-concurrency-a-simple-explanation/

    • Marked as answer by CM16 Thursday, May 17, 2018 5:35 PM
    Thursday, May 17, 2018 2:47 PM
  • Also note that the DataAdapter has a built-in method of Optimistic Concurrency that compares all row values prior to updates.  If your data set can be efficiently consumed through Data/Table Adapters then this could be a relatively easy solution for you.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by CM16 Thursday, May 17, 2018 5:56 PM
    Thursday, May 17, 2018 2:53 PM
    Moderator
  • As others describe is what you are talking about concurrency. 

    In .Net  System.Data it is always optimistic concurrency, which means that first is executed and then at writing time  is checked if there are 2 users who thwart each other. That can be in many ways. Some do it by comparing if a timestamp at reading time is already changed at write time. Others take important fields and leave non important fields aside. Values are always important because those can only be calculated. 

    Falsification with concurrency is one of the earliest samples in computer fraud.

    However, there is no best practise. Some say if an street address changes than the last one wins. Others say, if the boss changes an address, that is the one which wins. 

    Therefore you have first to make your rules around the data, before that you start handle them and therefore is not a general rule. However, beginners do it mostly just by the rule: "Every last changer where is whatever concurrency reported has to read and update the changes again ".    


    Success
    Cor



    Thursday, May 17, 2018 3:38 PM
  • Thank's to everybody, i got a lot information to read y learn. I gess that i will use the optimist concurrency, why i have in all tables a "last Modification Date/Time" column, and i can get this value on the form load and compares with the same column when i press Save
    Thursday, May 17, 2018 5:35 PM
  • Hi CM16:

    Just letting you know that I've updated the title of your thread to better reflect the question.  This will help others who might search for the same issue.  The term "registry" is related to the Windows Registry so the change to "database concurrency" helps keep the thread on topic in search results.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 17, 2018 5:41 PM
    Moderator
  • Thanks for the advise Reed. Happens that some hours ago i did don´t knowed how to name that situation and my natural language isn't english. Regards.
    Thursday, May 17, 2018 5:55 PM
  • Thanks for the advise Reed. Happens that some hours ago i did don´t knowed how to name that situation and my natural language isn't english. Regards.
    Sorry, I guess my explanation regarding the use of a timestamp wasn't clear enough.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 17, 2018 6:57 PM
  • Thanks for the advise Reed. Happens that some hours ago i did don´t knowed how to name that situation and my natural language isn't english. Regards.

    That is OK.  We moderators are here to help with simple language issues like that.

    You might try re-reading the answer from Paul P Clement IV, as he was really the first one to tell you what your problem was and to suggest using a time-stamp for comparison (he deserves credit for his answer).

    -edit-

    On that note, Karen's reply was valid as well, though perhaps a bit to complex for you to implement at this point in your development.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Thursday, May 17, 2018 7:01 PM
    Moderator
  • Thanks Reed, I appreciate that. :-)

    I was just concerned that the language issue was a barrier to my explanation (or maybe I could have re-worded it).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 17, 2018 7:13 PM
  • Thanks Reed, I appreciate that. :-)

    I was just concerned that the language issue was a barrier to my explanation (or maybe I could have re-worded it).


    Paul ~~~~ Microsoft MVP (Visual Basic)


    No problem, and I'm guessing that was the case as well.  Here it was a single sentence compared to a paragraph and that could have been intimidating to a non-native English speaker.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 17, 2018 7:25 PM
    Moderator
  • Honestly, i readed all responses carefully, and all of them were very useful to me. Thank's to everybody again
    Thursday, May 17, 2018 7:38 PM