Skip to content

Latest commit

 

History

History
44 lines (25 loc) · 3.78 KB

2022-03-24-get-lock.md

File metadata and controls

44 lines (25 loc) · 3.78 KB
Raw

Proposal: Support GET LOCK

Abstract

This proposes an implementation of GET_LOCK() with some limitations.

Background

MySQL supports advisory locks (aka GET_LOCK()) as a way of serializing access to a resource. This is commonly used by applications such as ORMs to ensure that only one process can perform DDL at a time.

Advisory locks are session scoped (not transaction) locks. Up until MySQL 5.7 acquiring any new lock would free all previously acquired locks. This was because the locking system did not support deadlock detection internally.

Proposal

This proposes that we use an internal table (mysql.advisory_locks) with the lockName as the PRIMARY KEY. When a user acquires a lock with GET_LOCK(), we insert a row into the table as part of an internal pessimistic transaction. The internal transaction is never committed, and stays open until RELEASE_LOCK() is called, or the session is closed.

The timeout feature of GET_LOCK() can be supported by modifying the pessimistic lock timeout (innodb_lock_wait_timeout).

When RELEASE_LOCK() is called, the transaction is rolled back and the row is never actually inserted into the table. Thus, there are no garbage collection requirements on the internal table.

I refer to the internal pessimistic transaction as an attached session since it can not be part of the user's session, as advisory locks are session scoped and not transaction scoped. The user session that executes the GET_LOCK() statement is the user session.

Compatibility

The following known limitations exist with an implementation based on pessimistic locks and attached sessions:

  • Deadlock detection is not supported. This limitation is because each lock needs to be acquired in a different attached session. This could be a major problem, since on deadlock both sessions will wait until their timeout expires. With GET_LOCK(), users typically set the timeout to very large values.
  • It is not possible to tell if a lock is held by another session, without acquiring it. Thus, the IS_FREE_LOCK() function can not be implemented in pure-SQL.
  • It is not possible to tell which other session holds the lock (IS_USED_LOCK()). This is both because of the issue of SQL support, and because the connection ID could match the ID of a different TiDB server.
  • RELEASE_LOCK() can only return 1 (lock released) or 0 (no lock released). In MySQL it can also return NULL if the lock was not held by any sessions. For the same reason IS_FREE_LOCK() is not supported, TiDB will always return 0 in cases MySQL would have returned NULL.
  • The timeout for acquiring locks is capped at one hour (max-txn-ttl). In MySQL, it is possible to set an unlimited timeout, but we will have to document this as not supported.

These compatibility issues do not appear to be a problem for the ORMs we have looked at.

Alternative Proposals

Of the compatibility issues, not supporting deadlock detection is the most problematic.

An alternative proposal is that we could decide that "GET_LOCK() is compatible with MySQL 5.6 instead of MySQL 5.7+". It is difficult to say if this will affect applications, since many users were probably not aware when using MySQL 5.6 that subsequent calls to GET_LOCK() would release previous locks. This alternative has been discussed, and rejected.

Since we will ship GET_LOCK() without deadlock detection, we can document that it is possible to manually resolve the deadlock by killing one of the locked sessions. The attached sessions themselves will not show up in SHOW PROCESSLIST, but the user sessions will show up as in "SELECT GET_LOCK()" state for a long time and killing the user sessions will kill the attached sessions.