Write Conflict with mySQL backend tables

[spectrolab]

Hi guys,

I have a bit of an issue with our db. It has a mySQL backend served locally to our LAN with multiple users accessing. SInce I upgraded to the latest version of mySQL server, on one of the forms we use to input data, if you try and update a record, sometimes, not always, it comes up with a write conflict error.

Let me explain, each record has some of the fields completed in first instance (no problem with this part), then about 30 minutes later, the user comes back and puts the data in the last field, This happens about 1000 times a day, but we get a write conflict on a few occasions, say 20 times per day, even though there was nothing in this field originally and hasn't been changed prior to filling this final field. This final field becomes locked and the only way we can put the data in is use a mySQL GUI (such as Navicat).

Any help would be much appreciated!

 

[Banana]

Few questions:

1) Do you have a timestamp column?
2) Do you run any code behind the form that may perform updates or otherwise edit data directly?
3) When the user put that data in last field 30 minutes later, did he leave the application running with that record open the whole time?
4) What engine are you using? MyISAM or InnoDB?

Sorry to ask so many questions but it's helpful in troubleshooting.

 

[spectrolab]

Thanks for the reply Banana,

In answer to your queries:

There is no time stamp in the table, but, I use an audit trail for the table (taken from Allen Browne's code) and this runs in the background, this is the code:

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)

Call AuditDelEnd("audTmpLOIData", "audLOIdata", Status)

End Sub

Private Sub Form_AfterUpdate()

Call AuditEditEnd("tblLOIData", "audTmpLOIData", "audLOIData", "ResultID", Nz(Me!ResID, 0), bWasNewRecord)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tblLOIData", "audTmpLOIData", "ResultID", Nz(Me.ResID, 0), bWasNewRecord)

End Sub

Private Sub Form_Delete(Cancel As Integer)

Call AuditDelBegin("tblLOIData", "audTmpLOIData", "ResultID", Nz(Me.ResID, 0))

End Sub
Private Sub cmdCloseLOI_Click()
On Error GoTo Err_cmdCloseLOI_Click

    
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
    DoCmd.OpenForm "frmLIMSMainMenu"
    
Exit_cmdCloseLOI_Click:
    Exit Sub

Err_cmdCloseLOI_Click:
    MsgBox Err.Description
    Resume Exit_cmdCloseLOI_Click
    
End Sub

This does not affect the data in the field directly, at least I don't think so. The table I am having issue with is tblLOIdata

3. The application remains open all the time on the user computer, but the form uses a query to select the records for a particular dataset, sometimes the form is not closed, sometimes it is between updates.

4.The engine is InnoDB.

Thanks very much for your help.

 

[Banana]

Tangent: One of reason I moved to MySQL was for free transaction logging. Thus you really don't need audit log if you can just use MySQL's binary logging to do that work for you "free". Just a FYI, thought Allen's code works quite great for situations where there's no existing logging mechanism.


1. Some people advise adding a timestamp to your MySQL table which may help Access with keeping track of whether a field has changed. Mind, some report it wasn't necessary, but it's one thing you may want to experiment with.

2. Is tblLOIData a local table or MySQL table, and is that the form's recordsource?

3. Alright, one possibility I'd look into is whether a lock has been requested for that row the user is currently on, and leaving it idle long enough causes brief disconnect which is automatically reconnected when the user returns so it's now a new process, which MySQL has no way of knowing whether it was the same user and same record- from its POV, it can look like a different user is trying to access the same row.

4. I forgot to ask- what is the MySQL's version? If it's 5.0 and above, then InnoDb supports MVCC, so we should verify that Access is not requesting for any locks because it's unnecessary in that context.

 

[spectrolab]

Thanks for your help yet again,

In answer to your queries,

1. I will look into timestamps, I haven't really used them much, I moved to mySQL a year ago as it was becoming unwieldy in access for multi-user access and mySQL seemed to work.

2. The table tblLOIData is a mySQL table and is the recordsource for the form.

3. I will look into that.

4. It is version 5.1, came with our new version of Mac server, (I know, using access on a mac in a windows environment, not the best idea).
What is MVCC, I haven't heard of that before, guess I'll have to do some reading!

Thanks again.

 

[Banana]

Regarding #2, if your code acts upon the same table, tblLOIData, as it is being used by your form, it can be erroneously interpreted as write conflict because Access/Jet has no way of knowing if the code acting upon came from the same user (it could be perceived as just another request). Mind, I don't see anything in your pasted code above that would do that and knowing Allen Browne's stellar quality, I am sure the code for audit trial merely reads from tblLOIData and writes into another table which is generally fine. If you modified Allen's code or have code in another module that may write or edit to tblLOIData, then that's your reason why you have write conflict.

Regarding 4, Multiple Version Concurreny Control (MVCC) is a type of lock management. Basically what it means is that InnoDB no longer requires us to request for a lock to read a row as it was the case in other RDBMS. However, if Access request for a lock, not only it is unnecessary but also creates the potential for conflicts. Unfortunately, it's been a while I worked so I can't remember *exactly* what I did to verify that there was no lock requests made. One way to verify this is to turn on MySQL's general log on and use a program like TextPad or Notepad ++ to open and read the log as you run the application and read the SQL statements that Access sends to MySQL.

I'm sorry I couldn't tell you *specifically* where the locks come from, hence telling you about several places to look into.

Finally, how is that a bad idea? I develop Access application on Mac computer using VM. Wouldn't have it any other way!