Last post Jan 11, 2018 03:51 AM by cbassett
Nov 15, 2017 05:26 PM|cbassett|LINK
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?
Nov 15, 2017 06:10 PM|PatriceSc|LINK
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.
Nov 16, 2017 09:33 AM|Cathy Zou|LINK
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:
Nov 16, 2017 02:23 PM|DA924|LINK
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.
Nov 25, 2017 04:13 PM|cbassett|LINK
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.
Nov 26, 2017 03:32 AM|DA924|LINK
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.
Dec 15, 2017 05:18 PM|PatriceSc|LINK
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 ?
Jan 11, 2018 03:51 AM|cbassett|LINK
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.