Titus wrote:
>
> We recently migrated a medioum sized genetic laboratory database
> from Access to MySQl. Of course, for the time being, we are stuck
> with our Access front end, using Access linked tables to our MySQL
> backend. Everything seems to be 'reasonalbly' okay after a month.
>
> However, we occasionally get a message from the front end that
> "This record has been changed by another user since you started
> editing it. etc. etc."
>
> This occurs in a "Write Conflict" message box from Access. We
> aren't having much luck running this problem down. Can anyone
> shed some light on it and save us some time?
>
> Thanks for even -considering- this a MySQL problem.
>
> titus sends
Are you using MyISAM tables?
I've found that if we have a number of users ( 5 or more ) connected to
the same table, we start to get these errors.
From memory, MyISAM tables use *table* level locking. The idea here is
that table-level locking is faster ( and simpler ) than row-level
locking. When a user running MS Access goes to edit / add a record, they
record a *record* lock. However MySQL locks the whole *table* and
reports back to Access that it has a *record* lock. Now another user
goes to edit / add a record, and you get your error message ( original
post ).
Solution: Use InnoDB tables, which give row-level locking.
If it's any consolation, we get the same errors with SQL Server ( which
is kinda strange considering SQL Server is supposed to do row-level
locking ).
What I do is try MyISAM tables to start with, and as more people start
to use the table ( and get the above error ), I convert them to InnoDB
tables, and don't get any more problems.
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
