WL#5906: read before write removal (RBWR)
Status: Complete — Priority: Medium
An UPDATE or DELETE in MySQL Server is currently implemented as a loop where it reads one row, evaluates the WHERE clause and then performs the update if the read row matches. This is a very generic mechanism but inefficent for the simple update and delete case and especially for a networked storage engine like NDB where the read is as expensive as the actual update due to network round trip time. Another drawback is that it prevents batching of update or deletes. Several of our storage engines would be able to optimize away the read before the UPDATE when the following conditions are met: * WHERE clause is using a full primary or unique key. * The SET clause is constant, ie. no SET a=a+1. This does not apply to DELETE since it has no SEt clause. * The rows to update has not been read already, ie filesort would read all rows and then iterate the sorted rows for update. * IGNORE is not used * The table has no trigger(could probably say "no UPDATE/DELETE trigger"?) Aka. "WL#3686 Avoid read before PK/UK update/deletes without expressions"
The MySQL Server would need to be modified to detect the given conditions and call a new handler functionin order to tell the handle to turn on "no read before update" mode. The handler should at this time be given the option to say "not supported on this table" if it need to add more restrictive checks than the above. For example NDB will not support RBWR delete if table has blobs. The update loop will start and a read will be issued(as usual) but the handler can return immediately with a "fake" row. For example UPDATE SET b=1 WHERE a=1 would cause the handler to return fake row "1" wether that row exists or not. Since the above conditions are met, mysqld will not do anything else with the row, but continue and start to define the update. The loop then continues and read the next row which handler will return "fake" row again until no more rows to update. When mysqld is leaving the update loop and have completed all calls to turn off any outstanding batches active in the handler, it's time to contact the handler again in order to get the result of the update. Since the handler returned a row, the mysqld will always think there was one row to update, but since only the handler knows how many rows it actually updated in the storage engine, that count need to be retrieved with a new handler function call. If batching was turned off there will always be zero or one row modified. if batching was on, it may be zero to number of updates rows modifed.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.