27.7.2 Updatable JSON Duality Views (MySQL Enterprise Edition)

In MySQL Enterprise Edition, updates on JSON duality views update their parent tables. Consider as an example a JSON duality view dv1 which is defined on a base table student. We perform the following INSERT statement on dv1:

INSERT INTO dv1 VALUES ('{"_id":1, "name":"Manu", "department":"Computer Science"}');

This affects the parent table (student) as if we had executed the statement shown here:

INSERT INTO student VALUES(1, "Manu", "Computer Science");

Updatable JSON duality views in MySQL Enterprise Edition support optimistic concurrency control (OCC), which allows writing to JSON documents only if no other session has modified them concurrently, using ETAG() values stored in the etag field of the _metadata sub-object in the JSON documents. The etag field represents a hash of the document's current state excluding (by default) _metadata.

Concurrency is handled as follows:

The user reads data (using SELECT), storing it locally.
The user modifies the local copy of the data, leaving the generated etag value unchanged.
Execution of an UPDATE statement reconstructs the object (including metadata) using SELECT and persists any changes only if the reconstructed state (that is, the result of ETAG() on the reconstructed object) matches the state last read.
If the etag values do not match, MySQL raises an error, which applications can handle by re-reading the data and retrying the operation if desired.

See the descriptions of the individual tables for more information.

You should be aware that one INSERT, UPDATE, or DELETE statement on a duality view may lead to multiple insert, update, or delete operations on the view's base tables.

The etag value serves as a control value only, and is not replicated; it is generated at SELECT or UPDATE execution time.

BLOB types are stored as binary but represented in base64-encoded format when projected as SELECT output. This means that the etag value can be different when run with the same input as a BLOB, and as hand-crafted base64-formatted string.