WL#1159: Allow multiple locks in GET_LOCK()

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.