MySQL Bugs: #1118: Allow multiple concurrent locks with GET

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:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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().

Thank you for your feedback,

As you might see in our pubic ToDo list in the manual, we already have this feature where.

Does MySQL plan to add this feature in the future version?  What future version do we expect to have this feature (6.0)?

Thanks,
Huan

This is well needed feature. Please implement it asap.

I can't believe this still ahsn't been implemented

please please please can we have this  ASAP .. please!

check http://kostja-osipov.livejournal.com/46410.html

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!

See also: http://bugs.mysql.com/bug.php?id=67806

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.

 http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock 
=====

Hello!

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.

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.