CODEWITHSUNDEEP
phpmysql
Alana Storm
Alana Storm

Reputation: 166106

Determine Why a Call to `GET_LOCK` Failed

Is there a way to determine why a call to MySQL's GET_LOCK function failed (i.e. returned 0?), or tell me why my naive understanding of the problem below is incorrect?

Per the manual

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name)

I'm debugging an application that attempts to get a lock using the following SQL

SELECT GET_LOCK('thinkup_2_b2_dev.crawler', 1) AS result

Whenever this code runs from a PHP context, result is always zero. This happens on my local development machine, and happened the first time I ran the application. In other worlds, I'm relatively sure there's no previous lock.

This is buoyed by the fact that, when I connect to the database with a third party tool (specifically, SequelPro) and run the following command

SHOW FULL PROCESSLIST;

I don't see any mention of the lock (it's my understanding that these sorts of locks will show up when I run SHOW FULL PROCESSLIST). All I see is the single row (Which I believe corresponds to the connection I'm currently using.)

9617 root localhost thinkup_2_b2_dev Query 0 NULL SHOW FULL PROCESSLIST

(Based on some independent tests, it appears that SHOW FULL PROCESSLIST doesn't show these sort of locks)

Is there a way to get MySQL to tell me why it's returning zero from a call to get_lock? If not, does anyone have any ideas for debugging this problem further?

Upvotes: 0

Views: 1145

Answers (1)

markusjm
markusjm

Reputation: 2562

As the documentation states, the function returns zero only when it times out. This would suggest that the timeout of 1 second was hit even if no session holds the lock. To verify that this is indeed the case, I'd recommend trying with a larger timeout (e.g. 10 seconds).

If the function returns zero even with a larger timeout, then there is a systematic problem with the particular lock. If the timeout of 1 second is indeed too short and the 10 second timeout solves it, I'd recommend opening a bug report as it is not expected behavior.

Upvotes: 1

Related Questions