By design, MySQL for Excel does not lock a table when you open an edit session and the table data is imported. Instead, the edit session feature relies on a model in which you have your own copy of the table data (a snapshot) to modify. The model also implies that your coworkers also have their own copy of the data. When multiple edit sessions perform operations on the same table at the same time, it is possible to overwrite the changes made by another session.
To prevent unintended overwrites when multiple edit sessions are in use, be sure the Use optimistic updates on all Edit Data sessions option is selected (see Edit Sessions Tab). This option is selected by default and should not be deselected when the MySQL database is on a remote host that does not guarantee exclusive access of data to you.
The optimistic updates feature works by ensuring that the
WHERE clause of the
UPDATE statements reference all
columns in the table. If any value of a record was changed by
another session, while you are also making changes, the record is
not found and that
will do nothing when you commit the changes to the database. In
contrast, with optimistic updates deselected, the
WHERE clause of the
UPDATE statements reference just
the primary key columns of the table.
To view the statements generated by MySQL for Excel, select the Preview SQL statements before they are sent to the server option before beginning your edit session (see SQL Queries Tab).
With optimistic updates enabled, non-conflicting changes are committed to the database, but the conflicting changes are not committed. To fix the conflicts, do the following:
In your worksheet while still in edit mode, locate the orange-colored cells that show the conflicting data.
Retrieve a fresh copy of data from the database by clickingon the edit session dialog then .
Apply changes to the conflicting cells again and then commit changes.
For general information about working with edit sessions, see Section 4.1, “Edit Session Overview”.