How to prevent write conflict while 2 users editing a record?

The first thing would be to detect that a conflict exists between the data on screen and the data persisted in the database. This can be achieved by implementing optimistic concurrency using a ROWVERSION column.

By doing so, when a user performs a save, the system can detect whether or not someone else has already updated a record or not. If an overlap/conflicting update is detected, a "merge" UI can be developed that can have both sets of values (values currently on screen and the values from the updated record). The user can then choose which of the two values to keep and save the record into the database.

I have a few posts that I wrote on optimistic concurrency to help you get started:

• A conceptual introduction on Optimistic Concurrency (https://nakulvachhrajani.com/2010/12/21/optimistic-concurrency-a-conceptual-introduction/)

• A key difference between ROWVERSION and TIMESTAMP (https://nakulvachhrajani.com/2012/07/26/0181-sql-server-ddl-rowversion-vs-timestamp-a-key-difference/)

• ROWVERSION is not a Unique value (https://nakulvachhrajani.com/2012/07/30/0182-sql-server-rowversion-uniqueness-myth-possibility-of-duplicate-values-exists/)

• What is the current ROWVERSION value? (https://nakulvachhrajani.com/2012/08/02/0183-sql-server-minactiverowversion-what-is-the-currently-active-rowversion-value/)

@Nakul,

The first thing would be to detect that a conflict exists between the data on screen and the data persisted in the database

The issue here is two users updating the table, not a conflict with data that already exists,

If an overlap/conflicting update is detected, a "merge" UI can be developed that can have both sets of values (values currently on screen and the values from the updated record)

Do you have a demo of this?

The user can then choose which of the two values to keep and save the record into the database.

Actually since they are updating the same record but not same fields, I would like a solution that preserves both.

Thanks,
Ben

SQL Server uses Locking mechanism to control concurrency. When you update a record, SQL Server requires X lock on this record. If another user want to update same record, it must wait until X lock is released on the record. I am not sure how you are expecting but SQL Server serves requests based on FIFO theory (First In First Out).

So you can use BEGIN TRANSACTION / COMMIT to ensure concurrency in SQL Server. Let's see my example
-- Create example table ---

CREATE TABLE TestConcurrency (ID int, Value char(1))
GO
INSERT INTO TestConcurrency
VALUES(1,'A')
,(2,'B')

Select allOpen in new window

-- Open new tab for new session in SSMS and execute UPDATE statement to update Value ='C'  where ID =1 ----
-- I called it as Session 57

BEGIN TRANSACTION
   UPDATE TestConcurrency
   SET Value ='C'
   WHERE ID =1

Select allOpen in new window

-- Open another tab for new session. I called it as Session 58. Execute UPDATE statement to update Value ='D'  where ID =1 ----

BEGIN TRANSACTION
   UPDATE TestConcurrency
   SET Value ='D'
   WHERE ID =1

Select allOpen in new window

Now you can see that the UPDATE statement of Session 58 can not finish until Session 57 finish because X lock is required on the record has ID = 1.

Next, run COMMIT statement on Session 57. You can see Session 58 finish immediately and data is update to 'D' value.
SQL_Locking.png

The issue here is two users updating the table, not a conflict with data that already exists,

Actually since they are updating the same record but not same fields, I would like a solution that preserves both.

I am slightly confused now. The statements above are contradictory - The question and the 2nd comment references updating the same record and hence the discussion on concurrency.

Do you have a demo of this?

No, but this should help: https://msdn.microsoft.com/en-in/data/jj592904.aspx.

Edit: The screenshot in the Wiki page here: https://en.wikipedia.org/wiki/Help:Edit_conflict indicates a conflict resolution page that allows the user to merge the stored data v/s data on the UI.

Hi bfuchs,

This is a tough issue that may be difficult to solve.  Databases really don't tolerate concurrent changes very well, and by using a now deprecated ADP you are limited in how creative you can be.

I will add that SQL Server supports partial row concurrency.  In other words, if user 1 has a form where fields A & B can be edited, and user 2 has a form where fields C & D can be edited, then they can both make their edits concurrently without a conflict, if:

- The forms limit their recordsets to only the fields that will be edited - the "other" fields may not be included in the field list, and
- The table must not have a RowVersion (aka TimeStamp) field, because its presence ensures full row concurrency.

This may not address your issue, since it seems both users need to use the same form to update the same fields - in which case I don't have a good solution for you.  You could look into Pessimistic locking, which would prevent the User 2 from even beginning to edit the record until User 1 saves it.  But that approach has its own problems too.

Sorry I couldn't help more,
Armen

@Dung,

So you can use BEGIN TRANSACTION / COMMIT to ensure concurrency in SQL Server.

My situation here is that I'm using MS Access as FE and this deals with all commands to the BE, therefore not sure if I can apply this type of solution unless I re-design the entire app to be completely unbound and all changes will be done by code, something I would try to avoid at the moment.
@Nakul,

The question and the 2nd comment references updating the same record

Thats correct.
Currently due to my pc issue I have problems viewing your links, just wondering are they offering a solution to keep changes of both users?

@Armen,

You could look into Pessimistic locking, which would prevent the User 2 from even beginning to edit the record until User 1 saves it.  But that approach has its own problems too.

That is something I would look into, wondering what are the problems I can expect..and where do I set this up?

Thanks,
Ben

Microsoft supports BEGIN TRANSACTION in MS Access. Please refer https://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx

In your situation, I am not sure what you are expecting when two users update data of a record at same time? For some examples:
+ User1 updates value of Field1 to 'A' with ID =1 at same time User2 updates value of Field1 to 'B' with ID =1. In this situation, what value of Field1 you want? As I said above, BEGIN TRANSACTION method always ensure concurrency, if A user  is updating data, no one can update the records until A user finish.

+ User1 updates value of Field1 to 'A' with ID =1 at same time User2 updates value of Field2 to 'B' with ID =1. In this situation, what you are expecting?

In case you want to track what data of a record is changed in MS Access, you can build your own Capture Data Change  method. Or if you use SQL Server, it supports some CDC features.

@Dung,

+ User1 updates value of Field1 to 'A' with ID =1 at same time User2 updates value of Field2 to 'B' with ID =1. In this situation, what you are expecting?

Really as stated above, I would prefer a solution that saves both changes, field1 & field2.
However if that not possible, at the very least I would like a message to come up as soon as user2 tries to change something saying that "this record is currently locked by user so and so..", in this way user dont end up wasting their time in order to find out at the end that...

Again the links you're providing seems to be referring to programmatically updates using recorsets, while I'm talking of Access build mechanism to talk directly with SQL w/o code intervention..

Thanks,
Ben

I'm not sure about ADP forms, but in regular Access forms you can set the Record Locks property at the form level.  "No Locks" means Optimistic, and "Edited Record" means Pessimistic.

Hi,

Looks like ADP doesn't have this property-:(
See attached.

What else can I do to simulate this behavior?

Thanks,
Ben
untitled.png

Hi Ben,

I was referring to the property sheet in design view of the form.  Can you see it there?  I'm not sure about its name in code.

Armen

Hi,

Yes I first looked there and didnt see anything, however some properties are only accessible by code and this is why I only posted that, wondering if you found some things to be the other way around (and it will throw an error no such property)?

- The table must not have a RowVersion (aka TimeStamp) field, because its presence ensures full row concurrency.

In general I knew that adding timestamp column causes less write conflicts, especially for tables with bit columns in them, therefore I'm afraid removing it may cause more harm than good..

Thanks,
Ben

Hi,

Actually, I am confusing and can't capture your idea here. At the beginning of this thread, you said that

We have an ADP Project (A2003) connected to SQL Server 2005.

We have a form bound to a large SQL table.....

But then

I'm talking of Access build mechanism to talk directly with SQL w/o code intervention..

What database engines are you using? Neither Access or SQL 2005?  It means that your application is connecting to Access for updating data and then continue updating data on SQL Server 2005 from Access.

Regardless of the ways, you should intervene the code to control user concurrency.
If Access
         Then use the way that Armen Stein suggested
If SQL Server
         Then use BEGIN TRANSACTION / COMMIT or ROWVERSION or Timestamp....

Thanks,

Hi Dung,

When I wrote form bound to SQL table I meant the following, the forms record source property is set to that table, and being that the case all what I have to do is just designing the form and putting the controls (which are also bound to the table fields) on the form, and by users typing in info and leaving the record everything gets automatically saved, there is no begin transaction / commit evolved here whatsoever..

You must be referring to an unbound access form where all these come into play..

If Access
         Then use the way that Armen Stein suggested

As stated above, I dont see that option being avail in ADP.

Thanks,
Ben

Hi Experts,

I will not be in tom at work, however please dont hesitate to post your add comments/suggestions and will resume on sunday.

Have a nice weekend!

Thanks,
Ben

Thanks Armen!