X DevAPI supports MySQL locking through the
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
both the Collection.find() and Table.select() methods. For more
background information on locking, see
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
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
UPDATEstatement 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 transactions 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
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
NOWAIT- if the function encounters a row lock it aborts and generates an
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:
autocommitmode means that there is always a transaction open, which is commited automatically when a SQL statement executes
by default sessions are in autocommit mode
you disable autocommit mode implicitly when you call
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