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: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
