27.7.2 Updatable JSON Duality Views (MySQL Enterprise Edition)

27.7.2.1 JSON Duality Views—Concurrency

27.7.2.2 Requirements for Table Annotations

27.7.2.3 Limitations on DML Operations

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.

Inserting objects for JSON duality views on self referencing tables and circularly referencing tables is supported.

Generated statements for DML operations on JSON duality view are executed as sub-statements of DML operations on view.

Sub-statement execution does not use any new metadata locks or row locks.

If any sub-statement fails, all sub-statements are rolled back.

For a projected column with AUTO_INCREMENT, a value for the column must be specified explicitly. If not, then it must be possible to deduce the column value from the join condition; otherwise, the operation is rejected with an error.

For execution of all generated sub-statements, triggers defined on the base tables of a JSON duality view are executed.

For base tables of JSON duality views linked by refenetial constraints, sub-statement execution includes execution of any foreign key referential actions which may be defined; failure of a foreign key cascading operation causes the DML operation to be rejected with an error.

DML operations on JSON duality view and their sub-statements are replicated consistently. Should execution of any sub-statement fail, any other sub-statements which are part of this operation are not replicated.