Optimistic updates for Edit Data operations in MySQL for Excel
By Javier Trevi�o-Oracle on Jun 26, 2014
In this blog post regarding MySQL for Excel features included since version 1.2.0, we are going to talk about a new option that is used along with Edit MySQL Data operations; very useful when editing data in a multi-user environment. If you are not familiar with editing MySQL data it is very advisable that you visit our thorough blog post about that topic: How To - Guide to editing MySQL data within Excel.
MySQL for Excel allows inserting, deleting and updating a table's rows using Excel as a friendly front-end; a snapshot of the selected MySQL table is imported into a Excel worksheet and the Edit Session works with that detached copy of the data. Any changes done to that copy are translated into SQL statements that are applied against the selected MySQL table. So far, so good.
In a multi-user environment, more than 1 person may be performing changes to the exact same records on a specific MySQL table meaning the changes done by one person can be overwritten by another unless records are explicitly locked. By design MySQL for Excel does not lock a table when its data is imported to open an Edit Session because it is simply not practical, the session may be left open by a user for an unknown amount of time which can be very long, hence the disconnected nature of the Edit Session.
In all previous 1.x versions what could happen if 2 different persons were editing the exact same MySQL table would be the following as illustrated in the image below:
- User 1 (orange) opens an Edit Session against a MySQL table.
- User 2 (purple) also opens an Edit Session against the same MySQL table.
- User 2 modifies a record where C1 = 1, modifies C2 = b, C3 = j and C4 = z, then commits the modifications.
- User 1 modifies the same record where C1 = 1, modifies C2 = w, then commits that single modification.
- User 2 retrieves again from the database the record where C1 = 1 and notices C2 = w and is left wondering why since he expected it to have a value of b.
In MySQL for Excel 1.2.0 we introduced a new global option to allow optimistic updates to be used on Edit Sessions to avoid overwriting any data that may have been modified by another user between the moment the Edit Session retrieved/refreshed the data from the database and the moment the Edit Session changes are committed. The Global Options dialog can be accessed from the Options button located in the Schema Selection panel or the DB Objects Selection one as shown below.
After clicking on Options the Global Advanced Options dialog will appear, under the SQL Queries Options section there is an option called Use optimistic updates on all Edit Data sessions that if checked (which is by default) it will prevent the unintentional overwriting we mentioned above on all Edit Sessions in MySQL for Excel.
The use of optimistic updates can also be turned on/off for all Edit Sessions or a specific one by right-clicking the Edit Session floating dialog and selecting from the context menu Use Optimistic Update ► For all sessions or For this session respectively.
To understand better the mechanism of how the unintentional overwriting is done in MySQL for Excel we can take a look at the SQL statements generated without and with optimistic updates turned on. As an example, lets say we have an Edit Session open for the MySQL table actor_copy, we turn off optimistic updates for this single session (unchecking the option in the screenshot above), and then we change some data as we can see in the screenshot below.
Now having turned on the Preview SQL statements before they are sent to the server global option (look at the screenshot with the global options up above) we can inspect the SQL statements generated by MySQL for Excel to apply the modifications after clicking Commit Changes. In the screenshot below you can notice how the WHERE clause of the UPDATE statements reference just the primary key columns of the table. We will click Cancel and not commit these changes.
If then we turn back on optimistic updates for this Edit Session, and click Commit Changes again we will be able to see how the SQL statements for the same modifications look like using optimistic updates. In the screenshot below you can notice how the WHERE clause of the UPDATE statements reference all columns in the table, this way if any value of a record was changed by another user, the record will not be found and that UPDATE statement will not do anything.
Lets say that before we hit Apply another user is making modifications to the actor_copy table using MySQL Workbench, and they change the last_name column's value from "LEWIS" to "LOPEZ" of the record where actor_id = 4, and commit the change as seen in the screenshot below.
Then we commit our changes in MySQL for Excel. Since another user committed a change on the record where actor_id = 4, the WHERE clause of the first UPDATE statement generated with optimistic updates turned on (see 2 screenshots above) will not find the record it was supposed to, and as we expected that change will not be unintentionally overwritten by the changes done in MySQL for Excel. The cells that encountered optimistic updates conflicts will be colored orange and the information dialog that displays the operation results will show the conflicts as warnings as you can see below.
At this point all non-conflicting changes are committed to the database, but the conflicting changes are not. In order to fix the conflicts we need to locate the orange colored cells that depict the conflicting data, retrieve a fresh snapshot of data from the database by clicking Revert Data on the Edit Session dialog then Refresh Data from DB, then re-apply our changes on the conflicting cells and commit those changes again.
Optimistic updates makes Edit MySQL Data operations in MySQL for Excel more friendly in multi-user environments and can prevent some data updating concurrency troubles. We hope you give this and the other new features in MySQL for Excel a try!
Remember that your feedback is very important for us, so drop us a message and follow us:
- MySQL on Windows (this) Blog: https://blogs.oracle.com/MySqlOnWindows/
- MySQL for Excel forum: http://forums.mysql.com/list.php?172
- Facebook: http://www.facebook.com/mysql
- YouTube channel: https://www.youtube.com/user/MySQLChannel