8.3 Working with Locking

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.

Locking considerations

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.