Detection of active lock not correct? #57
Description
Hello,
I am investigating an issue where a lock on our DB server was somehow left open for a long time. Not sure of the cause of that, but that is unrelated. In our case we resolved that by rebooting the DB server - ouch.
However I would like to report this issue for your consideration.
Just after the query to obtain the lock is run here https://github.com/stefangabos/Zebra_Session/blob/master/Zebra_Session.php#L551 there is a check to see if the number of rows returned from that query is != 1. If there is not one row returned it is assumed that the lock acquisition has failed.
However, in my investigation I found that you do get a row back, but it has a value of 0 instead of one, like so:
mysql> select get_lock('session_729ce4f4c284455d49aa3a99fee8b0db59850320', 1);
+-----------------------------------------------------------------+
| get_lock('session_729ce4f4c284455d49aa3a99fee8b0db59850320', 1) |
+-----------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
In this case, the lock was acquired.
If I leave that connection open and in another MySQL thread I try the same, I still get one row back, but with 0 for the result.
mysql> select get_lock('session_729ce4f4c284455d49aa3a99fee8b0db59850320', 1);
+-----------------------------------------------------------------+
| get_lock('session_729ce4f4c284455d49aa3a99fee8b0db59850320', 1) |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
1 row in set (1.00 sec)
There is a 1 second delay as it waits to acquire the lock.
The documentation https://dev.mysql.com/doc/refman/8.4/en/locking-functions.html says:
Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill).
Your thoughts on this would be appreciated. I'm happy to make a PR to resolve if you think I'm right.
By the way I've seen this behaviour on the following MySQL servers:
- 5.7.36-0ubuntu0.18.04.1-log
- 5.7.44-48
- 10.11.2-MariaDB-1:10.11.2+maria~ubu2204
Thanks,
Bob.