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
=====

* <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
--------------

|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.                                |