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.