Write Conflict Woes

[adversus]

Good afternoon, Byte'rs!

I'm running into an annoyance that, utilizing the framework I am, has been causing me grief. In a nutshell:

-Access 2003 Compiled ADE application
-SQL Server 2005 backend
-15 concurrent users

Normally our application has very specific workflows that handle how users get assigned specific records they're responsible for updating. This works well, because it pushes criteria to a Stored Proc that runs on the server, grabbing available records and putting them in a user's pipeline.

However, we've come across a situation that required us to bypass this process, and assign records to my users manually (by assigning, that means updating a text field with their initials which the application uses for workflow).

The problem we're having is that if two users go into the "Open" pipeline to manually grab a record, if two users grab the same record and start making udpates, when the second person goes to update they recieve the write conflict error.

I'm trying to come up with a way to have Access "look" to see if the record is updated before any changes are entered, but short of having a master LOCK / UNLOCK button that stores the locked status in a separate locking table, I'm coming up empty.

Any suggestions? :)

[NeoPa]

I guess not understanding what you are referring to by "pipeline" is causing me to find the question a bit confusing.

Is it a standard linked table?

How does your structure fit together (a brief explanation please)?

[adversus]

Yeah, sorry about that.

The "pipeline" is a user's queue, it's basically a recordset pulled down into the application for specific criteria, whatever the user selects (usually something like, "give me data set 1, data set 2, where the assign_box is my initials").

In this case I've got tables joined together. Updates are only being applied to the Right table (using a LEFT JOIN) that's created from a stored procedure and that SP is used as the record source of the form.

So essentially what I'm trying to do is prevent two users from modifying the same record.

[NeoPa]

Maybe I'm still not getting something.

If your SP that provides the data for the pipeline as seen by each individual is in control of which data is sent, presumably on the basis that each record is only owned by one individual, then how can it send the same record concurrently to two different requestors?

Even assuming this is not quite working as expected then surely this is a problem with the SP rather than the Access portion of the project.

Maybe you can explain what I'm missing. It may be simply "the whole picture" of course, but let's see where we can get.

[adversus]

The problem is that user's can bypass their own pipeline and go out into a queue that does not have any assignments. In other words, records that are not already flagged by a user.

The problem I'm having is when this happens and two users modify the same record out of this data set, that the problem occurs.

[NeoPa]

So the pipeline you're having problems with is a different one from the one you described in post #3 :S

[adversus]

Fair point. That's what I get for typing furiously in between meetings :)

Basically, how do I prevent two users who retrieved the same record from the server from causing a write conflict if they both do updates before the record gets refreshed/saved from the server.

[NeoPa]

Well again, I don't see that this is a particularly Access issue here. I still don't have much of an understanding of the relevant pipeline, but guessing from what else you've said, it must be a queue of some sort provided by the SP that has no user level criteria associated.

This takes us back to the basics. If you want to provide such protection with your SP then fine, though we cannot generally help with that here (We don't even know what algorithms you're using - apart from it being non-Access in nature).

As far as Access record-locking goes, that is selected from the Options screen of the Tools menu. I'm really not sure that it even effects ODBC tables though.

Am I on the right track here? I'm not getting a sense that communication is going very well.

[adversus]

Yeah I apologize for that. It's been one of those weeks. I'm usually way easier to understand :)

Let me start over to clarify:

The form in question (and it is only one form) is bound to a stored procedure, lets call it "SP1". SP1 accepts several parameters, which it uses as criteria for a series of SELECT statements to retrieve the particular data a user requests. For example:

CREATE PROCEDURE [dbo].[SP1]
   (@p_cParm1 CHAR(15) = NULL
   ,@p_cParm1 CHAR(75) = NULL
   ,@p_vAssigned VARCHAR(4) = NULL)
AS
 
BEGIN
   SELECT * FROM dbo.some_table
   WHERE (@p_cParm IS NULL OR some_field = @p_cParm1)
   AND (@p_cParm2 IS NULL OR some_field2 = @p_cParm2)
   AND (@p_vAssigned IS NULL OR LTRIM(RTRIM (assgn_cd)) = @p_vAssigned)
END

It's much longer and more complex than this, with more parameters, but this is the gist. Essentially the SP will return to access a record set from the SELECT statement, which is built with the criteria passed. In our case, the Assigned parameter may or may not be passed, and if it's left NULL, all records that match Parm1 and Parm2 are returned, regardless of the Assigned field status.

Now assume two users call this SP within minutes of each other without supplying the Assigned parameter. They may get the same record(s). Access retrieves the record set and uses this to populate the form (using the form's .InputParameters property). The same record is now displaying on two user's applications.

One user then modifies the record, and closes the form or navigates away which causes Access to push the update to the server. The second user does the same, and recieves a Write Conflict error, because they both had pending edits before one user saved.

The part I'm stuck at is figuring out a mechanism to have the application (i.e. Access) do one of the following:

1. Keep track of when a record becomes "locked" for editing, which can be displayed to the user, or
2. Something else I can't think of

Clear as mud? :)

[ADezii]

@adversus
I have an idea, but I'm not exactly sure as to whether or not it will work. This simple process will be illustrated below. The majority of the code will be encapsulated within a Function, a simple Call to the Function will suffice. The general idea is to:

1. Create a Clone of the Current Form's Recordset which is based on the Stored Procedure.
2. Sync the Clone to the Form's Current Record by equating Bookmarks.
3. Attempt to Edit the Clone. If the Record is Locked, an Error should occur, the Function returns True (is Locked), and then the Function is exited. If not, then the Function returns False.
4. Check the return Value of the Function being sure to pass the Current Form as an Argument to the Function.
5. Sounds good in theory, I'll let you actually try it and let me know if it actually works! (LOL)!
6. Function Definition:
   

   Public Function fIsRecordLocked(frm As Form)
   Dim rst As DAO.Recordset
   On Error GoTo HandleErr
    
   'Clone the Form's Recordset and synch up to the
   'Form's Current Record
   Set rst = frm.RecordsetClone
   rst.Bookmark = frm.Bookmark
    
   'If the Current Record is locked, then the next
   'statement should produce an Error that we will trap.
   'The Error Value should be 3188.
   rst.Edit
    
   fIsRecordLocked = False
    
   ExitHere:
     Exit Function
    
   HandleErr:
     fIsRecordLocked = True    'Probably Locked (3188)
       Resume ExitHere
   End Function

7. Typical Call to fIsRecordLocked():
   

   If fIsRecordLocked(Me) Then
     '..Record is Locked
   Else
     'Record not Locked
   End If

8. Kindly let me know how this turns out.

[adversus]

ADezii, thanks for the direction. I didn't think about using clone record set to check for record locks, so I'll try this and see what happens. I may not get to it today, but I'll try within a few days and get back to you :)

[adversus]

ADezii, I just tried your solution and every test results in a Locked result. I'm running it in a test environment where I'm the only user, so it's generated the locked result even with only one user in the system.

[adversus]

Aha, it's failing every time on the record locks, it's returning locked each time because it's erroring on:

Set rst = frm.RecordsetClone

Type Mismatch.

Because my Form is based on a SQL Stored Procedure, I couldn't use DAO. So I changed the recordset to an ADODB object and it resolved the Type Mismatch, however I'm having trouble finding the equivelant method for of "Edit" for ADO recordsets.

[NeoPa]

@adversus
Aha. This seems to be the ongoing question of how and when to lock data. Either method has some problems associated with it :

1. Lock it when you express an interest in updating (on first read) and the user may go off to lunch leaving it locked indefinitely.
2. Lock it when you are ready with the data inside the code and it is perfectly possible for multiple users to have an update interest in the same record. This way leads to upset users if their updates are dropped in the bit bucket.

This is (no mistake about it) a fairly complicated subject with many considerations and related issues. Updating with deltas just one example of solutions to this problem (minimises rather than solves the problem tbh).

Having said that, I would expect any system (SQL Server; Access; Oracle; etc) to have their own inbuilt locking mechanism implemented. I'm afraid I'm no expert on how each has been implemented as I simply haven't had much need for it in my experience.

I do know that recordset updates in code require a command (.Edit & .AddNew I believe) that locks access to that record before updates are allowed. I think .Update saves and releases the record. How SPs in SQL Server (T-SQL based) handle the same I don't know I'm afraid.

While it's possible to implement similar locking mechanisms in code, I wouldn't suggest it generally. I would expect it to be considerably slower than any inbuilt mechanisms, and quite complicated to implement. In fact this whole subject can be researched on the web. I'm sure their are articles that explain all the details. Not something one could easily knock up in 5 minutes you understand.

I'm sorry I couldn't be more help.

[ADezii]

@adversus
With ADO, try setting a Field on the Form equal to itself, then invoke the Update Method. This should be interpreted as an Edit, and no harm will be done either way. If the Record is Locked an Error will occur and the Field will never be Updated while if the Record is not Locked it will be Updated to itself.

rst![LastName] = frm![txtLastName]
rst.Update