WL11282: Support new locking modes NOWAIT and SKIP LOCKED

nmariz · nmariz · commit f18d043e5848 · 2018-03-16T11:06:18.000Z
This WL adds NOWAIT and SKIP_LOCKED support to ReadStatement.lock_shared()
and ReadStatement.lock_exclusive() introduced in MySQL 8.0.

A new enumerator was added to specify the lock contention to be used in
the lock methods.

Tests were added for regression.
diff --git a/docs/mysqlx/tutorials.rst b/docs/mysqlx/tutorials.rst
@@ -7,3 +7,4 @@ Tutorials
    tutorials/getting_started
    tutorials/transactions
    tutorials/creating_indexes
+   tutorials/locking
diff --git a/docs/mysqlx/tutorials/locking.rst b/docs/mysqlx/tutorials/locking.rst
@@ -0,0 +1,192 @@
+Locking
+=======
+
+Shared and Exclusive Locks
+--------------------------
+
+The X DevAPI supports locking matching rows, for the :func:`mysqlx.Collection.find()` and :func:`mysqlx.Table.select()` methods, which allows safe and transactional document/row updates on collections or tables.
+
+There are two types of locks:
+
+- :func:`mysqlx.ReadStatement.lock_shared()` permits the transaction that holds the lock to read a row.
+
+- :func:`mysqlx.ReadStatement.lock_exclusive()` permits the transaction that holds the lock to update or delete a row.
+
+Examples
+^^^^^^^^
+
+**Setup**
+
+Assuming the existence of ``test_schema.test_collection`` collection.
+
+.. code-block:: python
+
+   [{
+       "_id": "1",
+       "name": "Fred",
+       "age": 21
+    },{
+       "_id": "2",
+       "name": "Sakila",
+       "age": 23
+    },{
+       "_id": "3",
+       "name": "Mike",
+       "age": 42
+   }]
+
+Get the session and collection objects.
+
+.. code-block:: python
+
+   # client 1
+   session_1 = mysqlx.get_session("root:@localhost:33060")
+   schema_1 = session_1.get_schema("test_schema")
+   collection_1 = schema_1.get_collection("test_collection")
+
+   # client 2
+   session_2 = mysqlx.get_session("root:@localhost:33060")
+   schema_2 = session_2.get_schema("test_schema")
+   collection_2 = schema_2.get_collection("test_collection")
+
+**Shared lock**
+
+.. code-block:: python
+
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id = '1'").lock_shared().execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = '2'").lock_shared().execute()  # should return immediately
+    collection_2.find("_id = '1'").lock_shared().execute()  # should return immediately
+
+    # client 1
+    session_1.rollback()
+
+    # client 2
+    session_2.rollback()
+
+**Exclusive Lock**
+
+.. code-block:: python
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id = '1'").lock_exclusive().execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = '2'").lock_exclusive().execute()  # should return immediately
+    collection_2.find("_id = '1'").lock_exclusive().execute()  # session_2 should block
+
+    # client 1
+    session_1.rollback()  # session_2 should unblock now
+
+    # client 2
+    session_2.rollback()
+
+**Shared Lock after Exclusive**
+
+.. code-block:: python
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id = '1'").lock_exclusive().execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = '2'").lock_shared().execute()  # should return immediately
+    collection_2.find("_id = '1'").lock_shared().execute()  # session_2 blocks
+
+    # client 1
+    session_1.rollback()  # session_2 should unblock now
+
+    # client 2
+    session_2.rollback()
+
+**Exclusive Lock after Shared**
+
+.. code-block:: python
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id in ('1', '3')").lock_shared().execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = '2'").lock_exclusive().execute()  # should return immediately
+    collection_2.find("_id = '3'").lock_shared().execute()     # should return immediately
+    collection_2.find("_id = '1'").lock_exclusive().execute()  # session_2 should block
+
+    # client 1
+    session_1.rollback()  # session_2 should unblock now
+
+    # client 2
+    session_2.rollback()
+
+Locking with NOWAIT and SKIP_LOCKED
+-----------------------------------
+
+If a row is locked by a transaction, a transaction that requests the same locked row must wait until the blocking transaction releases the row lock. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.
+
+To avoid waiting for other transactions to release row locks, ``mysqlx.LockContention.NOWAIT`` and ``mysqlx.LockContention.SKIP_LOCKED`` lock contentions options may be used.
+
+**NOWAIT**
+
+A locking read that uses ``mysqlx.LockContention.NOWAIT`` never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.
+
+Example of reading a share locked document using :func:`mysqlx.ReadStatement.lock_shared()`:
+
+.. code-block:: python
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id = :id").lock_shared().bind("id", "1").execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = :id").lock_shared(mysqlx.LockContention.NOWAIT) \
+                .bind("id", "1").execute()
+    # The execution should return immediately, no block and no error is thrown
+
+    collection_2.modify("_id = '1'").set("age", 43).execute()
+    # The transaction should be blocked
+
+    # client 1
+    session_1.commit()
+    # session_2 should unblock now
+
+    # client 2
+    session_2.rollback()
+
+**SKIP_LOCKED**
+
+A locking read that uses ``mysqlx.LockContention.SKIP_LOCKED`` never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.
+
+Example of reading a share locked document using :func:`mysqlx.ReadStatement.lock_exclusive()`:
+
+.. code-block:: python
+
+    # client 1
+    session_1.start_transaction()
+    collection_1.find("_id = :id").lock_shared().bind("id", "1").execute()
+
+    # client 2
+    session_2.start_transaction()
+    collection_2.find("_id = :id").lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) \
+                .bind("id", "1").execute()
+    # The execution should return immediately, no error is thrown
+
+    # client 1
+    session_1.commit()
+
+    # client 2
+    collection_2.find("_id = :id").lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) \
+                .bind("id", 1).execute()
+    # Since commit is done in 'client 1' then the read must be possible now and
+    # no error is thrown
+    session_2.rollback()
+
diff --git a/lib/mysqlx/__init__.py b/lib/mysqlx/__init__.py
@@ -33,7 +33,7 @@
 
 from .compat import STRING_TYPES, urlparse, unquote, parse_qsl
 from .connection import Session
-from .constants import SSLMode, Auth
+from .constants import Auth, LockContention, SSLMode
 from .crud import Schema, Collection, Table, View
 from .dbdoc import DbDoc
 from .errors import (Error, InterfaceError, DatabaseError, NotSupportedError,
@@ -269,7 +269,7 @@ def get_session(*args, **kwargs):
     "Session", "get_session",
 
     # mysqlx.constants
-    "constants",
+    "Auth", "LockContention", "SSLMode",
 
     # mysqlx.crud
     "Schema", "Collection", "Table", "View",
diff --git a/lib/mysqlx/constants.py b/lib/mysqlx/constants.py
@@ -1,4 +1,4 @@
-# Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.
+# Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
 #
 # This program is free software; you can redistribute it and/or modify
 # it under the terms of the GNU General Public License, version 2.0, as
@@ -55,5 +55,7 @@ def create_enum(name, fields, values=None):
 Auth = create_enum("Auth",
                    ("PLAIN", "EXTERNAL", "MYSQL41"),
                    ("plain", "external", "mysql41"))
+LockContention = create_enum("LockContention",
+                             ("DEFAULT", "NOWAIT", "SKIP_LOCKED"), (0, 1, 2))
 
-__all__ = ["SSLMode", "Auth"]
+__all__ = ["SSLMode", "Auth", "LockContention"]
diff --git a/lib/mysqlx/protocol.py b/lib/mysqlx/protocol.py
@@ -397,6 +397,9 @@ def send_find(self, stmt):
             msg["locking"] = \
                 mysqlxpb_enum("Mysqlx.Crud.Find.RowLock.SHARED_LOCK")
 
+        if stmt.lock_contention > 0:
+            msg["locking_options"] = stmt.lock_contention
+
         self._writer.write_message(
             mysqlxpb_enum("Mysqlx.ClientMessages.Type.CRUD_FIND"), msg)
 
diff --git a/lib/mysqlx/statement.py b/lib/mysqlx/statement.py
@@ -34,6 +34,7 @@
 from .errors import ProgrammingError, NotSupportedError
 from .expr import ExprParser
 from .compat import STRING_TYPES
+from .constants import LockContention
 from .dbdoc import DbDoc
 from .result import SqlResult, Result
 from .protobuf import mysqlxpb_enum
@@ -709,6 +710,29 @@ def __init__(self, target, doc_based=True, condition=None):
         super(ReadStatement, self).__init__(target, doc_based, condition)
         self._lock_exclusive = False
         self._lock_shared = False
+        self._lock_contention = LockContention.DEFAULT
+
+    @property
+    def lock_contention(self):
+        """:class:`mysqlx.LockContention`: The lock contention value."""
+        return self._lock_contention
+
+    def _set_lock_contention(self, lock_contention):
+        """Set the lock contention.
+
+        Args:
+            lock_contention (:class:`mysqlx.LockContention`): Lock contention.
+
+        Raises:
+            ProgrammingError: If is an invalid lock contention value.
+        """
+        try:
+            # Check if is a valid lock contention value
+            _ = LockContention.index(lock_contention)
+        except ValueError:
+            raise ProgrammingError("Invalid lock contention mode. Use 'NOWAIT' "
+                                   "or 'SKIP_LOCKED'")
+        self._lock_contention = lock_contention
 
     def is_lock_exclusive(self):
         """Returns `True` if is `EXCLUSIVE LOCK`.
@@ -726,20 +750,28 @@ def is_lock_shared(self):
         """
         return self._lock_shared
 
-    def lock_shared(self):
-        """Execute a read operation with SHARED LOCK. Only one lock can be
+    def lock_shared(self, lock_contention=LockContention.DEFAULT):
+        """Execute a read operation with `SHARED LOCK`. Only one lock can be
            active at a time.
+
+        Args:
+            lock_contention (:class:`mysqlx.LockContention`): Lock contention.
         """
         self._lock_exclusive = False
         self._lock_shared = True
+        self._set_lock_contention(lock_contention)
         return self
 
-    def lock_exclusive(self):
-        """Execute a read operation with EXCLUSIVE LOCK. Only one lock can be
+    def lock_exclusive(self, lock_contention=LockContention.DEFAULT):
+        """Execute a read operation with `EXCLUSIVE LOCK`. Only one lock can be
            active at a time.
+
+        Args:
+            lock_contention (:class:`mysqlx.LockContention`): Lock contention.
         """
         self._lock_exclusive = True
         self._lock_shared = False
+        self._set_lock_contention(lock_contention)
         return self
 
     def group_by(self, *fields):
@@ -849,6 +881,7 @@ def get_sql(self):
                                         having=having, order=order_by))
         return stmt
 
+
 class InsertStatement(WriteStatement):
     """A statement for insert operations on Table.
 
diff --git a/tests/test_mysqlx_crud.py b/tests/test_mysqlx_crud.py
