Bug #110243 Suggest to add get_reader_lock/get_writer_lock for locking functions

• Submitted: 1 Mar 2023 0:35
• Modified: 1 Mar 2023 4:27
• Reporter: Fangxin Flou (OCA)
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S4 (Feature request)
• Version: all, 8.0
• OS: Any
• CPU Architecture: Any
• Tags: GET_LOCK

[1 Mar 2023 0:35] Fangxin Flou

Description:
https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html#function_get-lock

We are using locking functions for flow control between tasks, it works well. But we found that it's better to provide a read-write locking functions. Such as:

select get_reader_lock('abc', 10);
select get_writer_lock('abc', 10);
select release_reader_lock('abc');
select release_writer_lock('abc');

Which will help us to manager parallel tasks.

How to repeat:
N/A

Suggested fix:
N/A

[1 Mar 2023 4:27] MySQL Verification Team

Hello Fangxin Flou,

Thank you for the feature request!

regards,
Umesh

[2 Mar 2023 9:28] Jakub Lopuszanski

Hi!
Depending on use case, and if the set of lock "names" is finite, then you can create a `locks` table with one row per "lock", and with the `name` being the primary key of the table.
Then you can request write/exclusive access with:
BEGIN;SELECT 1 FROM locks WHERE `name`="abc" FOR UPDATE; 
or read/shared access with:
BEGIN; SELECT 1 FROM locks WHERE `name`="abc" FOR SHARE;
you can even use `innodb_lock_wait_timeout` per sesssion to implement "timed wait".

Can you describe your use case in sufficient detail so that it is clear why above scheme would not work?