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 signgle 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. ================================================================================ There is a contribution implementing some of the above ideas http://bugs.mysql.com/bug.php?id=67806 Here is the description for feature implemented in contribution: 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, so starting from 5.5 it became possible to take multiple locks in any order -- a deadlock, should it occur, can be detected and an error returned to the client that had closed the wait chain. High Level Specification ------------------------ 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(). A possible deadlock between multiple locks taken in reverse order, as well as between user level locks and metadata/MyISAM locks/global read lock is resolved using the MDL deadlock detector. The documented (and preserved) behaviour of GET_LOCK() is to return 0 in case of lock wait timeout and NULL in case of error. 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. Syntax changes -------------- There are no changes in syntax. The semantics of the GET_LOCK call has changed to not release locks implicitly. Incompatible changes -------------------- GET_LOCK() no longer implicitly releases the previous lock held by the connection. If GET_LOCK() is called multiple times on the same name, RELEASE_LOCK() has to be called the same amount of times to release the lock (no error, the locks are recursive). All locks waited on inside MDL, and requested by a connection which has disappeared are from now on released automatically. Implementation considerations ----------------------------- 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. In theory it could be possible to implement new locks purely using MDL API. However, to speed up certain operations (HAS_LOCK(), recursive acquisition of the same lock), and to avoid acquisition of multiple instances of MDL_ticket for different instances of the same lock (MDL_context has a list of tickets, and sometimes uses linear search on the list, so it's not a good idea to unnecessarily makes the context too large). The MDL API was extended with lock owner querying capability to implement HAS_LOCK().