Summary

Concurrent access to a collection can synchronized by using row locking which can be enabled on X Protocol level. X Protocol supports waiting for other transaction, still it is missing the support for the NO WAIT and SKIP LOCKED row locking modes which were added to SQL in 8.0.

Goals

User must be able to skip locked documents in a collection (rows in innodb table). User must be able to fail the query when locked documents access to a row would result in a lock.

Notes

• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
• https://dev.mysql.com/worklog/task/?id=10645

Functional requirements

Lock operation

1. User must be able to wait for row lock created by other session.
2. User must be able to skip locked rows.
3. User must be able to abort a query when processing of an row would result in waiting.

X Protocol

1. Client application must be able to check if "skip locked" and "nowait" options are supported.

Protocol

Mysqlx.Crud.Find already supports row locking. Current locking behaviour forces one transaction to wait until another one is "commited". MySQL Server supports other actions for row locks:

• NOWAIT - in case when a transaction would need to wait for row lock, the current query must be aborted with ER_LOCK_NOWAIT
• SKIP LOCKED - in case when a transaction would need to wait for row lock, the row is skipped

The worklogs adds "locking_option" optional field to Mysqlx.Crud.Find which modifies the waiting behaviour in following way:

• Mysqlx.Crud.Find is missing the field - wait until session that locked the row is released (backward compatibility)
• locking_option=NOWAIT - stop the current query and return Mysqlx.Error with error ER_LOCK_NOWAIT.
• locking_option=SKIP_LOCKED - skip locked rows

Setting "locking_option" without "locking" field is not supported and must return and error ER_X_BAD_LOCKING.

Changes in X Protocol:

diff --git a/plugin/x/protocol/mysqlx_crud.proto
b/plugin/x/protocol/mysqlx_crud.proto
index a8bc911e614..f2b3aed7f34 100644
--- a/plugin/x/protocol/mysqlx_crud.proto
+++ b/plugin/x/protocol/mysqlx_crud.proto
@@ -125,6 +125,7 @@ message UpdateOperation {
 // :param grouping: column expression list for aggregation (GROUP BY)
 // :param grouping_criteria: filter criteria for aggregated groups
 // :param locking: perform row locking on matches
+// :param on_locked_row: defines what to do, when locked row is processed
 // :Returns: :protobuf:msg:`Mysqlx.Resultset::`
 message Find {
   enum RowLock {
@@ -132,6 +133,12 @@ message Find {
     EXCLUSIVE_LOCK = 2;  // Lock matching rows so no other transaction can    read or write to it
   };

+  enum RowLockOptions {
+    NOWAIT = 1;      // Fail the query when locked row is processed
+    SKIP_LOCKED = 2; // Skip locked rows
+  };
+
   required Collection collection = 2;

   optional DataModel data_model = 3;
@@ -143,6 +150,7 @@ message Find {
   repeated Mysqlx.Expr.Expr grouping = 8;
   optional Mysqlx.Expr.Expr grouping_criteria = 9;
   optional RowLock locking = 12;
+  optional RowLockOptions locking_options = 13;

   option (client_message_id) = CRUD_FIND; // comment_out_if PROTOBUF_LITE
 };

When client set "locking_option" to "NOWAIT", then he must expect that ER_LOCK_NOWAIT error may occur in middle of sending rows.

SQL generation

Compatibility

Compatibility assurance lies on client side. Clients that use new features (in this case XMessage - field) must ensure backward compatibility by follow the procedure described under:

WL#10237 - X Protocol expectations for supported protobuf fields

"locking_options" needs to be tested by opening an expectation block with flow:

Client -> Server: Mysqlx.Expect.Open([+field_exists=17.13])
Client <- Server: Mysqlx.Ok
...

Error and Warnings

X Plugin must respond with and error when receives "Mysqlx.Crud.Find" message contains "on_locked_row" field and its missing "locking" field.

client -> server: Mysqlx.Crud.Find(...locking_options=ANY...)
client <- server: Mysqlx.Error(ER_X_BAD_LOCKING)