MySQL Bugs: #95551: MySQL GET_LOCK function is assigning lock to more than one thread

Bug #95551	MySQL GET_LOCK function is assigning lock to more than one thread
Submitted:	28 May 2019 12:50	Modified:	29 May 2019 12:36
Reporter:	Anant Mishra	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.6, and 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GET_LOCK, locking

Description:
I am using MySQL GET_LOCK in my project to get centralized lock in distributed server system. I have got an issue where the root cause is that in case of multiple concurrent requests, MySQL is assigning lock to more then on thread concurrently(i.e. GET_LOCK is returning true, even though other thread has acquired the lock).
I have verified it by comparing the response of GET_LOCK, and IS_FREE_LOCK responses. Though IS_FREE_LOCK was returning false, GET_LOCK returned true.
For work around I have used IS_FREE_LOCK to identify, whether lock is free or not. Once IS_FREE_LOCK returns true, then I am calling GET_LOCK to get the lock.

PS: I have used these functions from java.

How to repeat:
Call MySQL GET_LOCK and IS_FREE_LOCK in concurrent environment. You may see that IS_FREE_LOCK is returning false, but GET_LOCK will assign lock to thread.

Suggested fix:
We should just implement same logic in GET_LOCK, which is present in IS_FREE_LOCK to identify whether lock is free or not

Hello,

Thank you for your bug report.

I am the author of the original GET_LOCK() implementation. What you are describing is the changed behaviour, which is totally incompatible with the original one. Original GET_LOCK() used its own infrastructure, while the new one uses MDL locks. This is documented in our Reference Manual, where everything is explained, providing that you read it.

Here is the most important part of it:

----------------------------------------------------------

• Previously, GET_LOCK() permitted acquisition of only one named lock at a time, and a second GET_LOCK() call released any existing lock. Now GET_LOCK() permits acquisition of more than one simultaneous named lock and does not release existing locks.
Applications that rely on the behavior of GET_LOCK() releasing any previous lock must be modified for the new behavior.
• The capability of acquiring multiple locks introduces the possibility of deadlock among clients. The MDL subsystem detects deadlock and returns an ER_USER_LOCK_DEADLOCK error when this occurs.
• The MDL subsystem imposes a limit of 64 characters on lock names, so this limit now also applies to named locks. Previously, no length limit was enforced.
• Locks acquired with GET_LOCK() now 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.
• A new function, RELEASE_ALL_LOCKS() permits release of all acquired named locks at once.
----------------------------------------------------------

Unfortunately, that means that you have to change your application(s), so as to use IS_FREE_LOCK() before taking any locks.

Theoretically, one can ask for a feature request that old behaviour is reverted, but I do not think that it would be accepted.

Sorry, but this, new, behaviour is here to stay.