Optimistic updates for Edit Data operations in MySQL for Exc_MySQL

Opera

In this blog post regardingMySQL for Excelnew features included in the 1.2.x versions family, we are going to talk about a new option that is used along withEditMySQLDataoperations; very useful when editing data in a multi-user environment. If you are not familiar with editingMySQLdata it is very advisable that you visit our thorough blog post about that topic: How To - Guide to editingMySQLdata withinExcel.

Remember you can install the latest GA or maintenance version using theMySQL Installeror optionally you can download directly any GA or non-GA version from theMySQL Developer Zone.

MySQL for Excel allows inserting, deleting and updating a table's rows usingExcelas a friendly front-end; a snapshot of the selectedMySQLtable is imported into aExcelworksheet 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 MySQLtable. So far, so good.

In a multi-user environment, more than 1 person may be performing changes to the exact same records on a specificMySQLtable meaning the changes done by one person can be overwritten by another unless records are explicitly locked. By designMySQL for Exceldoes 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:

1. User 1 (orange) opens an Edit Session against a MySQL table.
2. User 2 (purple) also opens an Edit Session against the same MySQL table.
3. User 2 modifies a record where C1 =1, modifies C2 =b, C3 =jand C4 =z, then commits the modifications.
4. User 1 modifies the same record where C1 =1, modifies C2 =w, then commits that single modification.
5. User 2 retrieves again from the database the record where C1 =1and notices C2 =wand is left wondering why since he expected it to have a value ofb.

InMySQL for Excel1.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 Optionsbutton located in the Schema Selection panel or the DB Objects Selection one as shown below.

After clicking onOptionstheGlobal Advanced Optionsdialog will appear, under theSQL Queries Optionssection there is an option calledUse optimistic updates on all Edit Data sessionsthat if checked (which is by default) it will prevent the unintentional overwriting we mentioned above on all Edit Sessions inMySQL 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 menuUse Optimistic Update  For all sessionsorFor this sessionrespectively.

To understand better the mechanism of how the unintentional overwriting is done inMySQL for Excelwe 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 theMySQLtableactor_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 thePreview SQL statements before they are sent to the serverglobal option (look at the screenshot with the global options up above) we can inspect the SQL statements generated byMySQL for Excelto apply the modifications after clickingCommit Changes.  In the screenshot below you can notice how the WHEREclause of theUPDATEstatements reference just the primary key columns of the table. We will clickCanceland not commit these changes.

If then we turn back on optimistic updates for this Edit Session, and clickCommit Changesagain 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 WHEREclause of theUPDATE 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 hitApplyanother user is making modifications to theactor_copytable usingMySQL Workbench, and they change thelast_name column's value from"LEWIS"to"LOPEZ"of the record whereactor_id=4, and commit the change as seen in the screenshot below.

Then we commit our changes inMySQL for Excel. Since another user committed a change on the record whereactor_id=4, theWHEREclause of the firstUPDATEstatement 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 inMySQL 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 clickingRevert Dataon the Edit Session dialog thenRefresh Data from DB, then re-apply our changes on the conflicting cells and commit those changes again.

Optimistic updates makes Edit MySQL Data operations inMySQL for Excelmore friendly in multi-user environments and can prevent some data updating concurrency troubles. We hope you give this and the other new features inMySQL for Excela 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

Cheers!