Original task description ========================= We shall allow one to request multiple of locks Via serie of GET_LOCK statements, it would be even nice to allow several locks in single function call: GET_LOCK("lock1","lock2",0); GET_LOCK("lock3",0) Implementing this option one shall take care of possible deadlocks. Also in such case we shall allow RELEASE_LOCK to release all locks which thread has. It would be also convenient to be able to get list of locks which current thread owns for debugging purposes. Current task description/differences from original proposal =========================================================== There is a contribution implementing some of the above ideas http://bugs.mysql.com/bug.php?id=67806 Particularly: +) It allows to request multiple user-level locks via serie of GET_LOCK calls. GET_LOCK no longer releases user-level locks which were previously acquired. +) It replaces custom user-level lock implementation with one based on MDL lock manager. As result deadlocks between different connections acquiring user-level locks, metadata locks and waiting for table flushes are detected and reported as errors. 0) GET_LOCK("lock1","lock2",...) syntax is not supported (but this mostly seems like a syntax sugar). -) RELEASE_LOCK still releases only individual locks. There is no simple way to release all locks in connection. -) There is no simple way to get list of locks which current connection has. Work-around is to use p_s.metadata_locks. Decision has been made to accept this contribution, possibly with some extensions. Some use-cases in which multiple user-level locks are important =============================================================== User-level locks are often used: 1) to organize mutual exclusion when accessing some resource in cases when table/row-level locks are not appropriate. 2) to implement waits for some condition to change. Think of a queue in a producer-consumer setup. The queue itself is stored in a table in such a setup, but to avoid polling this table often/too much consumer waits for producer to insert into the queue on user-level lock. In cases when one connection needs to work with more than one resource/ condition ability to acquire and hold multiple user-level locks becomes necessary. Additionaly, code working with resources/conditions might have complex structure, so one might end-up in situation when one module which has acquired on user-level lock calls another module which needs to acquire another user-level lock. In this case the fact that the second module will release user-level lock acquired by the first module and thus break its invariants/atomicity as a side-effect might easily cause unexpected errors. So for better module isolation it would be nice if one connection can acquire different user-level locks independently without them affecting state of each other. User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html http://dev.mysql.com/doc/refman/5.7/en/miscellaneous- functions.html#function_get-lock http://dev.mysql.com/doc/refman/5.7/en/miscellaneous- functions.html#function_release-all-locks http://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
FR-1) It should be possible to request multiple user-level locks for the same connection via serie of GET_LOCK calls. FR-2) GET_LOCK call should not release previously acquired user-level locks in connection. FR-3) Deadlocks between different connections acquiring user-level locks should be detected. See QQ in HLS below. FR-4) IS_FREE_LOCK() and IS_USED_LOCK() functions continue to work as before. FR-5) User-level locks should continue to be independent of transactions. I.e. explicit or implicit transaction commit/ rollback should not release them. FR-6) New function RELEASE_ALL_LOCKS() is added which allows to release all user-level locks for connection.
Introduction ------------ GET_LOCK() function in MySQL allows a connection to acquire at most one user level lock. Taking a new lock automatically releases the old lock, if any. The limit of one lock per session existed since early versions of MySQL didn't have a deadlock detector for SQL locks. MDL patches in MySQL 5.5 added a deadlock detector for MDL locks and waits for table flushes. User-level locks don't use this infrastructure yet. The main idea ------------- The main idea of the implementation is to delete all old and outdated code which implements user level locks, and forward user level lock requests to the metadata locking subsystem. A new metadata type was introduced for user level locks - "user". Instances of "USER" locks are mutually exclusive. High-level syntax changes ------------------------- The only syntax change is that new function RELEASE_ALL_LOCKS() is introduced. This is niladic function (function which doesn't take any arguments). Changes to semantics -------------------- GET_LOCK() no longer implicitly releases the previous lock held by the connection. This feature makes it possible not only to take distinct user level locks locks in the same connection, but to take the same lock twice. In this case, the lock is granted and each instance of the same lock needs to be released afterwards with RELEASE_LOCK() (i.e. locks are recursive). To release all locks in connection one can use RELEASE_ALL_LOCKS() function. Similarly to RELEASE_LOCK() this function returns number of locks released (and 0 if connection didn't have any locks). A possible deadlock between multiple locks taken in reverse order, as well as between user level locks and metadata/waits for table flushes are detected and resolved using the MDL deadlock detector. Waits for user-level lock will be preferred as victim over waits for locks typically acquired by DDL, so we don't abort DDL in case of deadlock involving user-level locks and DDL. Deadlock errors are not normally expected from DDL by users. Waits for locks acquired by DML will be preferred as victim over waits for user-level locks, so we prefer to abort DML in case of deadlock involving user-level locks and DML. User-level locks are explicitly requested by user, so they are probably important for them. OTOH users expect deadlocks from DML transactions and for DML statements executed in @@autocommit=1 mode back-off and retry algorithm hides deadlock errors. Note that currently when ER_LOCK_DEADLOCK error is returned current transaction is always rolled back. Since we don't want to make user-level locks dependent on transactions and vice versa (e.g. release user-level locks on transaction commit/rollback or rollback transaction on user-level lock induced deadlock) we need either to: 1) Use different error code and message (e.g. ER_USER_LOCK_DEADLOCK). 2) Return NULL in case of deadlock from GET_LOCK() and maybe report a note about cause of the problem. We will follow approach 1) to make thing consistent with how we handle other problematic scenarios, e.g. when query is killed (see more below). Variable @@lock_wait_timeout does not affect user level lock waits, since GET_LOCK() API requires that an explicit timeout is always provided: it is used instead. Note that waits for user-level locks are atomagically aborted if connection which requested them disconnects. This is not so for MDL locks. Changing behavior of generic MDL locks to match current user-level lock behavior doesn't sound like a good idea if consider cases when we still want to acquire locks and do something even if connection has ceased to exist (e.g. writes to general/slow_log). So we will keep this automatical-abort-on-disconnect behavior but only for user-level locks. MDL subsystem needs to be extended to do this. Since IS_USED_LOCK() function needs to return id of connection which owns the lock the MDL API has to be extended with capability to query lock owner. With old implementation when a wait for user-level lock was aborted due to query or connection being killed GET_LOCK() function always returned NULL and statement using this function might have succeeded or failed with ER_QUERY_INTERRUPTED error, depending if statement tried to do anything else after calling GET_LOCK(). With new implementation statement which called GET_LOCK() and was killed will always fail with ER_QUERY_INTERRUPTED due to slightly different KILL error handling by MDL subsystem. Changes to handling of user-lock names -------------------------------------- Old code disallowed only NULL and empty lock names. It returned NULL if such a name was passed to one of functions. There was no limit on the name length. Since MDL subsystem imposes limits on the length of key used to identify objects new implementation needs to introduce limit on user-level lock name length. New limit is 64 characters (this is in line with limit on name length of other objects such as tables, routines, etc). ER_USER_LOCK_WRONG_NAME error is emitted when one of functions accepting user-level lock name as argument gets name which is longer than 64 character. Also behavior is changed to return the same error for NULL or empty ('') lock name. Old code assumed that user-level lock names passed to GET_LOCK/ RELEASE_LOCK/.., functions were always in utf8 charset (even in cases when they were not) and compared them in case-insensitive fashion. This means that in practice lock names were correctly handled only if they were fully ASCII or utf8. New implementation will convert lock name to utf8 from its original charset and perform case-insensitive comparison. Since MDL subsystem does binary comparison of keys identifying objects this means that we need to use lowercased version of user-level lock name as MDL key for the lock. Backward compatibility ---------------------- In future we might add an option which will switch server back to old behavior of GET_LOCK() if there will be complaints from users/ Support. We won't add such option initially. Note that compatibility when replicating from older master to a newer slave is not an issue in this case since user-lock functions are not statement replication-safe anyway. Monitoring/debugging -------------------- Since user-level lock is converted to just another type of metadata lock it becomes possible to get list of locks acquired for connection for debugging purposes using performance_schema.metadata_locks table. Monitoring for user-level locks can be done using p_s tables/instruments in the same way as for metadata locks.