Documentation Home
MySQL and Windows
Related Documentation Download this Excerpt

MySQL and Windows  /  ...  /  Multiple Edit Sessions

5.5.2 Multiple Edit Sessions

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 UPDATE statement 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.

Tip

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:

  1. In your worksheet while still in edit mode, locate the orange-colored cells that show the conflicting data.

  2. Retrieve a fresh copy of data from the database by clicking Revert Data on the edit session dialog then Refresh Data from DB.

  3. Apply changes to the conflicting cells again and then commit changes.

For general information about working with edit sessions, see Section 5.5.1, “Edit Session Overview”.