WL#11417: DevAPI : Support locking modes: NOWAIT and SKIP LOCKED

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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

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

Field "locking" Field "on_locked" Generated SQL
- - No change
- Any value Error ER_X_BAD_LOCKING
EXCLUSIVE_LOCK - No change - SELECT ... FOR UPDATE
SHARED_LOCK - No change - SELECT ... FOR SHARED
SHARED_LOCK NOWAIT SELECT ... FOR SHARED NOWAIT
EXCLUSIVE_LOCK NOWAIT SELECT ... FOR UPDATE NOWAIT
SHARED_LOCK SKIP_LOCKED SELECT ... FOR SHARED SKIP LOCKED
EXCLUSIVE_LOCK SKIP_LOCKED SELECT ... FOR UPDATE SKIP LOCKED

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)
Property Value
Error name ER_X_BAD_LOCKING
Error value 5169
Error text Invalid "find" message, "locking" field is required when
"locking_options" was set.