WL#10645: X Protocol Crud.Find with row locking

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

Row locking support for Mysqlx.Crud.Find to prevent concurrent access errors.

To allow consistent updates on a row or document that was previously read, one 
needs to lock such rows against updates from other sessions, otherwise the final 
contents of the row will not be the expected one.

MySQL supports the SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE 
extensions to SELECT, that can be used for this purpose.

FR1 - By default, when a Find request has no locking mode specified, it should 
perform no row locking.

FR2 - If a row locking mode of SHARED is specified, the Find should be translated 
to a SELECT .. LOCK IN SHARE MODE query.

FR3 - If a row locking mode of EXCLUSIVE is specified, the Find should be 
translated to a SELECT .. FOR UPDATE query.

Protocol
========

The Mysqlx.Crud.Find message shall be changed as follows:

--- a/rapid/plugin/x/protocol/mysqlx_crud.proto
+++ b/rapid/plugin/x/protocol/mysqlx_crud.proto
@@ -116,8 +116,14 @@ message UpdateOperation {
 // :param order: sort-order in which the rows/document shall be returned in
 // :param grouping: column expression list for aggregation (GROUP BY)
 // :param grouping_criteria: filter criteria for aggregated groups
+// :param locking: perform row locking on matches
 // :Returns: :protobuf:msg:`Mysqlx.Resultset::`
 message Find {
+  enum RowLock {
+    SHARED_LOCK = 1;   // Lock matching rows against updates
+    EXCLUSIVE_LOCK = 2;  // Lock matching rows so no other transaction can read 
or write to it
+  };
+
   required Collection collection = 2;
 
   optional DataModel data_model = 3;
@@ -128,6 +134,7 @@ message Find {
   repeated Order order = 7;
   repeated Mysqlx.Expr.Expr grouping = 8;
   optional Mysqlx.Expr.Expr grouping_criteria = 9;
+  optional RowLock locking = 12;
 };
 
 // Insert documents/rows into a collection/table


SQL Mapping
===========

If locking = SHARED_LOCK

Append "LOCK IN SHARE MODE" to the generated SQL query.


If locking = EXCLUSIVE_LOCK

Append "FOR UPDATE" to the generated SQL query.


Compatibility
=============

Compatibility assurance lies on client side. Clients that use new features (in
this case XMessage - field) must ensure backward compatibility.