Bug #1118 Allow multiple concurrent locks with GET_LOCK()
Submitted: 22 Aug 2003 1:18 Modified: 24 Sep 2014 6:21
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[22 Aug 2003 1:18] Dean Ellis
GET_LOCK() currently releases any existing lock upon subsequent calls, reducing the usefulness of this method of implementing application locks.  It may be desirable for one part of the application to wait for (and block) other portions of the application, which could identify themselves with unique lock tags, by attempting to acquire all of those seperate locks.

How to repeat:
Not applicable, but:

Connection1: SELECT GET_LOCK('lockone',0); SELECT GET_LOCK('locktwo',0);

Connection2: SELECT GET_LOCK('lockone',0);

Connection2 will acquire the lock because connection1's second GET_LOCK() call releases 'lockone'.

Suggested fix:
Allow connections to acquire multiple locks via GET_LOCK(), either by specifying multiple lock tags in the call or by changing the current behaviour which releases the existing lock upon subsequent calls to GET_LOCK().
[27 Aug 2003 5:34] Peter Zaitsev
Thank you for your feedback,

As you might see in our pubic ToDo list in the manual, we already have this feature where.
[2 May 2009 18:56] Huan Bui
Does MySQL plan to add this feature in the future version?  What future version do we expect to have this feature (6.0)?

[25 Mar 2010 13:12] Shankar B G
This is well needed feature. Please implement it asap.
[30 Mar 2011 9:53] Nathanael Inkson
I can't believe this still ahsn't been implemented
[28 Apr 2011 20:30] Roger U
please please please can we have this  ASAP .. please!
[5 Dec 2012 18:05] MySQL Verification Team
check http://kostja-osipov.livejournal.com/46410.html
[5 Mar 2013 7:21] Martin M
Please guys, do something about this.

Why does the second lock clear the first one? Why could one not have two named locks? I happen to be in a situation where I need exactly that.

Looking at the comments (from years ago...) it seems like I'm not the only one who would love to have this 'feature' if we can call it that.

Thank you!
[20 Nov 2013 17:58] Morgan Tocker
See also: http://bugs.mysql.com/bug.php?id=67806
[22 Sep 2014 16:01] Leandro Morgado
Looks like this very old Feature Request has finally been implemented in 5.7.5 (not GA yet):
Before 5.7.5, only a single simultaneous lock can be acquired and GET_LOCK() releases any existing lock.

In MySQL 5.7.5, GET_LOCK() was reimplemented using the metadata locking (MDL) subsystem and its capabilities were extended. Multiple simultaneous locks can be acquired and GET_LOCK() does not release any existing locks. It is even possible for a given session to acquire multiple locks for the same name. Other sessions cannot acquire a lock with that name until the acquiring session releases all its locks for the name.

As a result of the MDL reimplementation, locks acquired with GET_LOCK() appear in the Performance Schema metadata_locks table. The OBJECT_TYPE column says USER LEVEL LOCK and the OBJECT_NAME column indicates the lock name. Also, the capability of acquiring multiple locks introduces the possibility of deadlock among clients. An ER_USER_LOCK_DEADLOCK error is returned when this occurs.

[24 Sep 2014 6:21] Dmitry Lenev

This feature request was addressed in MySQL Server 5.7.5, GET_LOCK() now allows to acquire several locks and doesn't release currently held user locks. So I am closing this bug report.
[23 Oct 2015 7:07] Simon Mudd
The problem is that in the 12 years since this feature request was opened people or code may depend on previous behaviour.  Making the change in 5.7 without providing backwards compatibility or implementing the new behaviour in a new function like GET_LOCK2() means this change in 5.7 may cause surprises.