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 tofind().lockExclusive()
.lockShared()
has the same semantics asSELECT ... 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 asSELECT ... 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 anUPDATE
statement for those rows. Other transactions are blocked from updating those rows, from doingSELECT ... 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 anER_LOCK_NOWAIT
errorSKIP_LOCKED
- if the function encounters a row lock it skips the row and continuesDEFAULT
- 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.