MySQL HeatWave is the only fully managed MySQL database service that combines transactions, analytics, machine learning, and GenAI services, without ETL duplication. Also included is MySQL HeatWave Lakehouse, which allows users to query data in object storage, MySQL databases, or a combination of both. Users can deploy MySQL HeatWave–powered apps on a choice of public clouds: Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.
In multi-user database applications, concurrent updates to the same row or document are inevitable, leading to potential conflicts and data inconsistencies. The problem becomes even more complicated when the read and write operations are performed using separate stateless operations like REST requests. To address this, MySQL HeatWave introduced the lockless optimistic concurrency control, using ETAGs. This feature offers a modern and lightweight way to detect read/write conflicts.
This post explores how ETAGs work in the context of JSON Duality Views and demonstrates their use in safeguarding concurrent operations. JSON Relational Duality Views along with ETAGs are introduced in version 9.4 and made available in the MySQL HeatWave managed cloud service and elsewhere.
Why do we need lockless Optimistic Concurrency Control?
Imagine a user accessing data via a REST GET request for user’s mobile application and then later the user decides to update some information using a REST PUT request [Refer Jill in the diagram]. If the underlying data changes [Refer Jack in the diagram] between the two REST requests, the write (PUT) request from Jill will unknowingly discard Jack’s changes. In this scenario a typical database transaction does not work, primarily because, the resources cannot be locked for a non-deterministic time between Jill’s 2 REST requests. More importantly REST calls are stateless and there is no guarantee that they will reuse the same connection with the database where the transaction started.
Rather than locking records using a transaction, lockless optimistic concurrency control checks at the point of update whether the data has changed since it was last read. If so, the update is rejected, which allows the application to re-read the latest state of the data and then handle the conflict appropriately based on the application logic.
Simplifying Applications with Built-in Conflict Detection
Lockless optimistic concurrency control now comes integrated with MySQL JSON Duality Views. An ETAG is a hash value calculated from the object’s content, acting as a unique signature for each object. This enables the database to handle conflict detection automatically, allowing users to simplify their applications and concentrate on core business logic. Below, we’ll walk through the end-to-end modification workflow to illustrate how this benefits the user experience:
- The application begins by issuing a REST GET request to retrieve a JSON object from MySQL.
- Behind the scenes, a SELECT statement runs on the JSON Duality View, returning both the JSON object and its associated ETAG in the REST response.
- When the application wants to modify the object, it sends a REST PUT request:
- The modified JSON object, along with the ETAG originally retrieved, is submitted in the PUT request.
- For this an UPDATE statement is executed on the JSON Duality View.
- The database only applies the update if the supplied ETAG matches the current ETAG in storage, ensuring the object hasn’t changed since it was last read.
- If the ETAG doesn’t match, the update is rejected to prevent conflicting modifications.
- In the case of rejection, the application simply needs to fetch the latest version of the object and retry the modification.
Lets discuss this feature by creating relational tables “customer” and “orders” and then creating JSON Duality View customer_orders_dv using both tables. We will also insert some sample data into the JSON Duality View. For more details refer to our earlier article here.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product VARCHAR(100),
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'_id': customer_id,
'customer_name': name,
'orders': (
SELECT JSON_ARRAYAGG(
JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'order_id': order_id,
'product': product,
'amount': amount
)
)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
)
FROM customers;
mysql> INSERT INTO customer_orders_dv VALUES ( '{ "customer_name": "Alice",
"_id": 1,
"orders": [
{"order_id": 1, "product": "Laptop", "amount": 1299.99},
{"order_id": 2, "product": "Mouse", "amount": 19.99}
]
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3 Warnings: 0.
Let’s consider a scenario where two different applications are accessing the same JSON object. Both applications begin by reading the object from the JSON Duality View. Below, we illustrate the underlying SQL statement executed for each REST GET request, with each statement run in a separate connection to simulate the stateless nature of REST interactions.
mysql> SELECT * FROM customer_orders_dv WHERE data->'$._id' = 1;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 19.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "e6d40eabf2e070ffd2719c6755d50f1a" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
1 row in set (0.001 sec)
Once the object is read, the first applications modifies the amount field of orders to 1000 by executing UPDATE including ETAG fetched from SELECT statement above.
mysql> UPDATE customer_orders_dv SET data = '{
"_id": 1,
"orders": [
{
"amount": 1000,
"product": "Laptop",
"order_id": 1
},
{
"amount": 19.99,
"product": "Mouse",
"order_id": 2
}
],
"_metadata": {
"etag": "e6d40eabf2e070ffd2719c6755d50f1a"
},
"customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
Query OK, 1 row affected (0.007 sec)
Rows affected: 1 Warnings: 0.
Lets say the second application wanted to perform a similar modification, i.e., change the amount field to 2099.99. In this case the UPDATE statement is executed after the first application has already applied its changes.
mysql> UPDATE customer_orders_dv SET data = '{
"_id": 1,
"orders": [
{
"amount": 2099.99,
"product": "Laptop",
"order_id": 1
},
{
"amount": 19.99,
"product": "Mouse",
"order_id": 2
}
],
"_metadata": {
"etag": "e6d40eabf2e070ffd2719c6755d50f1a"
},
"customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
ERROR 6494 (HY000): Cannot update JSON duality view. The ETAG of the document
in the database did not match the ETAG '"e6d40eabf2e070ffd2719c6755d50f1a"' passed in.
As a result, the modification attempt by the second application is rejected, because the object has changed in the database and the ETAG no longer matches. To proceed, the application must re-fetch the latest version of the object and determine if the modification is still needed. If so, submitting an UPDATE with the new ETAG will allow the changes to be applied successfully.
mysql> SELECT * FROM customer_orders_dv WHERE data->'$._id' = 1;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 19.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "70f912fba716a2cb615d708e99d0f44c" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> UPDATE customer_orders_dv SET data = '{
"_id": 1,
"orders": [
{
"amount": 2099.99,
"product": "Laptop",
"order_id": 1
},
{
"amount": 19.99,
"product": "Mouse",
"order_id": 2
}
],
"_metadata": {
"etag": "70f912fba716a2cb615d708e99d0f44c"
},
"customer_name": "Alice"
}'
WHERE data->'$._id' = 1;
Query OK, 1 row affected (0.004 sec)
Rows affected: 1 Warnings: 0.
Using ETAG without JSON Duality Views
The ETAG is added as a built-in function in MySQL in 9.4, that generates a 128-bit hash (ETAG) value for a row by using specified column values. ETAG documentation can be found here. Users can use this function to customize their concurrency control mechanism for their specific application needs.
For example, below we describe a scenario with relational tables where ETAG matching is done only on a subset of columns. The ETAG is computed on students table on all columns except course column. Here concurrent stateless modification will be allowed even if the course changes for the student.
mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+
| student_id | name | course | ETAG(student_id, name) |
+------------+-------+--------+----------------------------------+
| 1 | Alice | CS101 | 8c7b29a51aab99d67d44c0ff559e746b |
+------------+-------+--------+----------------------------------+
mysql> UPDATE students SET course='ML100' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b';
Query OK, 1 row affected (0.005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+
| student_id | name | course | ETAG(student_id, name) |
+------------+-------+--------+----------------------------------+
| 1 | Alice | ML100 | 8c7b29a51aab99d67d44c0ff559e746b |
+------------+-------+--------+----------------------------------+
mysql> UPDATE students SET name='Peter' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b';
Query OK, 1 row affected (0.005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT student_id, name, course, ETAG(student_id, name) from students;
+------------+-------+--------+----------------------------------+
| student_id | name | course | ETAG(student_id, name) |
+------------+-------+--------+----------------------------------+
| 1 | Peter | ML100 | 2642513c12300911c71ed488a6785f0c |
+------------+-------+--------+----------------------------------+
mysql> UPDATE students SET name='Sam' WHERE student_id=1 AND ETAG(student_id, name) = '8c7b29a51aab99d67d44c0ff559e746b';
Query OK, 1 row affected (0.005 sec)
Rows matched: 0 Changed: 0 Warnings: 0 # Update failed
mysql> UPDATE students SET name='Sam' WHERE student_id=1 AND ETAG(student_id, name) = '2642513c12300911c71ed488a6785f0c';
Query OK, 1 row affected (0.006 sec)
Rows matched: 1 Changed: 1 Warnings: 0 # Update successful
With above examples, we formulated the SELECT and UPDATE statements such that ETAG is not sensitive to changes in course column. This helps users design their own custom semantics to reject updates based on their application logic. Please note it is always suggested to have a primary key as one of the argument in ETAG computation otherwise duplicate records can generate identical ETAGs. Hence, in our example we have included the student_id column as one argument along with name column.
Summary
With ETAG support for JSON Duality Views, MySQL brings a robust solution for lockless optimistic concurrency control in document-based JSON workflows. This approach improves data integrity while maintaining high performance in collaborative applications. Key benefits of using optimized concurrency control mechanism are:
- No explicit locking: Improves scalability.
- Lightweight: Only ETAG check required.
- Application awareness: Enables conflict handling (prompt user, merge, retry, etc.).
Learn more: MySQL JSON Duality View documentation
Try it out in MySQL 9.4!
- Feature available on Heatwave cloud service (Try free tier here)
- Feature available in Enterprise Edition (Download for free)
- Feature available in Community Edition with restriction (read-only views)