locked
reduce (or eliminiate) possibliity of 2+ users updating same record at the same time RRS feed

  • Question

  • User-734459410 posted

    Does ADO.NET E-F prevent two users from updating the same record in a table at the same time (ie. saving the record at the same time, thus clobbering eachother's changes)?

    If not, how can I reduce this or eliminate this possibility with E-F?

    Wednesday, November 15, 2017 5:26 PM

All replies

  • User753101303 posted

    Hi,

    See https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application for a detailed explanation.

    The basic idea is to handle a "row version" that is automatically updated by the db whenever a row is changed. You can then tell EF to include this version information in the WHERE clause for update/delete statements causing no row to be found it it has been changed since you loaded your version of this row. You can then handle as corresponding exception as you want.

    Wednesday, November 15, 2017 6:10 PM
  • User-707554951 posted

    Hi cbassett,

    A concurrency conflict would happen when two sources try to modify a database at the same time.

    There are two ways in which we can handle concurrency conflict.

    1. Pessimistic Concurrency

    2. Optimistic Concurrency

    Pessimistic Concurrency deals with database locking 

    Optimistci concurrency gives a better performance as no lock required and locking of records requires additional server resource.

    For more information about understanding concurrency conflict and how to handling Concurrency conflicts in ef. 

    You could refer to the following links:

    http://www.c-sharpcorner.com/UploadFile/tirthacs/handling-concurrency-in-mvc-and-ef/

    https://www.codeproject.com/Articles/1183935/Concurrency-Handling-In-Entity-Framework-Databas

    Best regards

    Cathy

    Thursday, November 16, 2017 9:33 AM
  • User1120430333 posted

    Myself, I put a timestamp column on the table record. A record is read  for update, and the timestamp is help in memory. The user tries to save the record, then the existing record in the table is read. If the timestamp on the existing record when compared to the timestamp held in memory do not match, then one knows that the record was updated before user could save their changes. Of course the user would need to get the current record for update and apply changes to it. 

    Thursday, November 16, 2017 2:23 PM
  • User-734459410 posted

    This seems like a good idea, and I thought about it (I already have a Timestamp type field in the table for other reasons, mainly so that the two databases I'm working with, the one in SQL and the other one which is based on a different version of SQL (Pservasive, which the two DB systems don't synchronize hence my reason for the Timestamp already in the table) but the problem I also found later after thinking about it is that the if the person retrieves the record after it has been updated by another person, yet another person could update the record in the time it takes the second guy (the guy who was originally waiting to update the record) to submit their changes to be saved.  It's an unlikely occurance, especially in a small system like mine, where we might have 25-30 users on the system at most, at a time, but it is still of concern, as you don't want one person to save changes only to have another person wipe out those changes one second later if they both tried to save changes at/around the same time.

    I'm wondering if there is a way I can lock the row through ADO.NET E-F to prevent this, or so at least the transaction can be written completely before someone else tries to change it.

    Saturday, November 25, 2017 4:13 PM
  • User1120430333 posted

    It's an unlikely occurance, especially in a small system like mine, where we might have 25-30 users on the system at most, at a time, but it is still of concern, as you don't want one person to save changes only to have another person wipe out those changes one second later if they both tried to save changes at/around the same time.

    It will never happen if the check is made against user-id and timestamp. The user can pull-up the same record on two different machines the user is logged-on,  update and save the record, and the update and save the record on the other machine where the user would be stopped. So the difference maker in the user-id and timestamp check, because if either one changes, one knows that the record was updated prior to the current user could submit their change.

    It's a tried and proven method I have used since my IBM CICS mainframe old school days. 

    Sunday, November 26, 2017 3:32 AM
  • User753101303 posted

    Hi,

    You tried ? It should cause an exception as EF will use both the pk and the timestamp value (which is automatically updated whenever the row is changed at least with SQL Server) so the row shouldn't be found anymore  if it has been changed since loaded.

    Which db are you using ?

    Friday, December 15, 2017 5:18 PM
  • User-734459410 posted

    Sorry, I haven't tried this yet, as the project was put hold for the time being.  I'll look into it at a later date.  For now, I'll probably just close this thread.

    Thursday, January 11, 2018 3:51 AM