relstorage.adapters.mysql.locker – Locker implementations

Locker implementations.

exception CommitLockQueryFailedError

Bases: UnableToAcquireCommitLockError

class MySQLLocker(options, driver, batcher_factory, version_detector)

Bases: AbstractLocker

MySQL locks.

Commit and Object Locks

Two types of locks are used. The ordinary commit lock and the object locks are standard InnoDB row-level locks; this brings the benefits of being lightweight and automatically being released if the transaction aborts or commits, plus instant deadlock detection. Prior to MySQL 8.0, these don’t support NOWAIT syntax, so we synthesize that by setting the session variable innodb_lock_wait_timeout.

Note that this lock cannot be against the object_state or current_object tables: arbitrary rows in those tables may have been locked by other transactions, and we risk deadlock.

Also note that by default, a lock timeout will only rollback the current statement, not the whole transaction, as in most databases (this doesn’t apply to NOWAIT in MySQL 8); to release any locks taken earlier, we must explicitly rollback the transaction. Fortunately, a lock timeout only rolling back the single statement is exactly what we want to implement NOWAIT on earlier databases. In contrast, a detected deadlock will actually rollback the entire transaction.

The ensure_current argument is essentially ignored; the locks taken out by lock_current_objects take care of that.

Shared and Exclusive Locks Can Block Each Other On Unrelated Rows

We use two lock classes for object locks: shared locks for readCurrent, and exclusive locks for modified objects.

MySQL 5.7 and 8 handle this weird, though. If two transactions are at any level besides SERIALIZABLE, and one locks the odd rows FOR UPDATE the other one blocks trying to lock the even rows FOR UPDATE or in shared mode, if they happened to use queries like WHERE (zoid % 2) = 1. This is surprising. (It’s not surprising in SERIALIZABLE; MySQL’s SERIALIZABLE is quite pessimistic.)

This is because (quoting https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html) “SELECT ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.” While “SELECT ... FOR UPDATE sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels.” The complex WHERE clause does range queries and traversal of the index such that it winds up locking many unexpected rows.

The good news is that the query we actually use for locking, SELECT zoid FROM ... WHERE zoid in (SELECT zoid from temp_store), doesn’t do a range scan. It first accessess the temp_store table and does a sort into a temporary table using the index; then it accesses object_state or current_object using the eq_ref method and the PRIMARY key index in a nested loop (sadly all MySQL joins are nested loops). This locks only the actually required rows.

We should probably add some optimizer hints to make absolutely sure of that.

Pack Locks

The second type of lock, an advisory lock, is used for pack locks. This lock uses the GET_LOCK and RELEASE_LOCK functions. These locks persist for the duration of a session, and must be explicitly released. They do not participate in deadlock detection.

Prior to MySQL 5.7.5, it is not possible to hold more than one advisory lock in a single session. In the past we used advisory locks for the commit lock, and that meant we had to use multiple sessions (connections) to be able to hold both the commit lock and the pack lock. Fortunately, that limitation has been lifted: we no longer support older versions of MySQL, and we don’t need multiple advisory locks anyway.

hold_pack_lock(cursor)

Try to acquire the pack lock.

Raise an exception if packing or undo is already in progress.

on_store_opened(cursor, restart=False)

A callback that must be called when a store connection is opened or restarted.

This implementation calls _on_store_opened_set_row_lock_timeout() when the store connection is initially opened.

release_commit_lock(cursor)

Auto-released by transaction end.

release_pack_lock(cursor)

Release the pack lock.

lock_timeout(cursor, timeout, restore_to=None)

ContextManager that sets the lock timeout to the given value, and returns it to the DEFAULT when done.

If timeout is None, makes no changes to the connection.