WL#10645: X Protocol Crud.Find with row locking
Affects: Server-8.0
—
Status: Complete
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.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.