X DevAPI User Guide for MySQL Shell in Python Mode
X DevAPI supports MySQL locking through the
lockShared() and
lockExclusive() methods for the
Collection.find() and Table.select() methods. This enables you to
control row locking to ensure safe, transactional document updates
on collections and to avoid concurrency problems, for example when
using the modify() method. This section describes how to use the
lockShared() and
lockExclusive() methods for both the
Collection.find() and Table.select() methods. For more background
information on locking, see
Locking Reads.
The lockShared() and
lockExclusive() methods have the following
properties, whether they are used with a Collection or a Table.
Multiple calls to the lock methods are permitted. If a locking
statement executes while a different transaction holds the
same lock, it blocks until the other transaction releases it.
If multiple calls to the lock methods are made, the last
called lock method takes precedence. In other words
find().lockShared().lockExclusive() is
equivalent to find().lockExclusive().
lockShared() has the same semantics as
SELECT
... LOCK IN SHARE MODE. Sets a shared mode lock on
any rows that are read. Other sessions can read the rows, but
cannot modify them until your transaction commits. If any of
these rows were changed by another transaction that has not
yet committed, your query waits until that transaction ends
and then uses the latest values.
lockExclusive() has the same semantics as
SELECT
... FOR UPDATE. For any index records the search
encounters, it locks the rows and any associated index
entries, in the same way as if you issued an
UPDATE statement for those
rows. Other transactions are blocked from updating those rows,
from doing
SELECT
... LOCK IN SHARE MODE, or from reading the data in
certain transaction isolation levels. Consistent reads ignore
any locks set on the records that exist in the read view. Old
versions of a record cannot be locked; they are reconstructed
by applying undo logs on an in-memory copy of the record.
Locks are held for as long as the transaction which they were acquired in exists. They are immediately released after the statement finishes unless a transaction is open or autocommit mode is turned off.
Both locking methods support the NOWAIT and
SKIP LOCKED InnoDB
locking modes. For more information see
Locking Read Concurrency with NOWAIT and SKIP LOCKED. To use
these locking modes with the locking methods, pass in one of the
following:
NOWAIT - if the function encounters a row
lock it aborts and generates an
ER_LOCK_NOWAIT error
SKIP_LOCKED - if the function encounters a
row lock it skips the row and continues
DEFAULT - if the function encounters a row
lock it waits until there is no lock. The equivalent of
calling the lock method without a mode.
When working with locking modes note the following:
autocommit mode means that
there is always a transaction open, which is commited
automatically when an SQL statement executes.
By default sessions are in autocommit mode.
You disable autocommit mode implicitly when you call
startTransaction().
When in autocommit mode, if a lock is acquired, it is released after the statement finishes. This could lead you to conclude that the locks were not acquired, but that is not the case.
Similarly, if you try to acquire a lock that is already owned by someone else, the statement blocks until the other lock is released.