Bug #67806 Multiple user level lock per connection
Submitted: 4 Dec 2012 20:58 Modified: 1 Oct 2014 14:19
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, MySQL, user level locks
Triage: Needs Triage: D5 (Feature request)

[4 Dec 2012 20:58] Konstantin Osipov
Please allow multiple user level locks per connection.

How to repeat:
See a contributed patch here, in particular, test cases in func_misc.test: 

Suggested fix:
See contribution.
[4 Dec 2012 20:59] Sveta Smirnova
Thank you for the reasonable feature request.
[2 Apr 2013 9:09] Konstantin Osipov

Here's the patch ported to mysql-server branch.
[3 Apr 2013 12:08] Konstantin Osipov
Multiple user locks per connections.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 1.diff (text/x-patch), 47.63 KiB.

[11 Apr 2013 12:12] Ståle Deraas
Hi Kostja,

Thanks a lot for the contribution! We will work to have it included in trunk.

[26 Nov 2013 14:08] Veniamin Gvodikov
affect me
[10 Dec 2013 10:20] Eugene Kirdzei
affect me
[10 Dec 2013 10:27] Alexey Rybak
Several days ago we celebrated 1st year anniversary of this patch. I still hope to see multiple user-level locks working correctly in MySQL. Please, accept this contribution.
[10 Dec 2013 12:24] German Zvonchuk
affects me
[10 Dec 2013 14:04] Euigene Klimov
affected me
[12 Dec 2013 6:59] Alexey Manuhin
affects me
[22 Sep 2014 16:43] Leandro Morgado
In 5.7.5 (not GA yet) documentation, we can find this: 
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.

[1 Oct 2014 14:19] Dmitry Lenev

The patch based on this contribution became part of MySQL Server 5.7.5 Development Milestone Release. Documentation pages were updated accordingly (see http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html).

So I am closing this feature request.

Thank you a lot for your contribution once again!