Solved

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

Posted on 2016-07-19
17
77 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
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
Comment Utility
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
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

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
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
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
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
Comment Utility
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 3

Author Closing Comment

by:bfuchs
Comment Utility
Thanks Armen!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now