Solved

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

Posted on 2016-07-19
17
112 Views
Last Modified: 2016-07-25
Hi Experts,

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

We have a form bound to a large SQL table that is often is use by more then use user for updating a particular record at the same time, and currently users are getting a write conflict error message and changes are being discarded.

Now my question is, if I dont want to split those fields in question between multiple tables, and I dont want to force a save record after each field update, what are my other options?

PS, The table in question has a timestamp field already.
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41719851
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:
1
 
LVL 4

Author Comment

by:bfuchs
ID: 41719978
@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
0
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41720170
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')

Open 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

Open 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

Open 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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41720241
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.
0
 
LVL 9
ID: 41721385
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
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41721907
@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
0
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41722111
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.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41722138
@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
0
 
LVL 9
ID: 41723829
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.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41723861
Hi,

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

What else can I do to simulate this behavior?

Thanks,
Ben
untitled.png
0
 
LVL 9
ID: 41723867
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
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41723945
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
0
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41723984
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,
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41724018
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
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41724035
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
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 500 total points
ID: 41724818
Hi Ben,

I'll be mostly out of contact for the next week, so I'll leave some comments here.

We don't use ADPs, so I can't help with specifics about them.  I will say that if you're maintaining an important application that's in ADP, you may want to consider migrating it to something that's currently supported.  ADPs have been deprecated for years now.  Even if you moved it to ACCDB, you could have the same user experience on a current platform, and the coding would be similar too.  I've written a presentation called Best of Both Worlds that covers this, including concurrency considerations.  Or you could consider rewriting it as an ASP.NET web application, thought that would involve much more work and the UI would be different.

Regarding RowVersion/Timestamp fields, yes, they are required if you have either nullable Bit fields or floating point fields, in order to avoid "false" concurrency errors.  We recommend you don't use those data types anyway.  For yes/no values, we use Small Integer, and for numbers we use decimal types.  Then we're free to choose whether or not to use RowVersion.

Regarding concurrency in general, I'm questioning why you're running into so many conflicts.  In most systems, users don't update the same records at the same time very often.  For example, an Order or Customer record is rarely updated by two different users simultaneously.  What kind of record is this that multiple people are updating at the same time?  Is it some kind of multi-purpose table that should have fewer columns and more rows?  In other words, are you actually trying to solve a database design problem?

Cheers,
Armen
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41728555
Thanks Armen!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question