GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

Question

I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say I have one procedure p1 which requests a lock with GET_LOCK("lock", 0); so that multiple calls to this procedure don't interfere with each other. The procedure releases the lock before terminating. I have another procedure p2 which requests a lock using GET_LOCK("lock", 10);.

Suppose there is a flood of calls to p1, and somewhere in the middle, p2 is called, like this:

...
CALL p1();
CALL p1();
CALL p2();
CALL p1();
#lock released here from first p1() call: who gets it?
...

Theoretically, the second procedure shouldn't be affected much: it waits for the lock to be released and since GET_LOCK in p1 has a timeout of 0, p2 should be the only one requesting the lock when it is released. However, I don't think I can expect this ideal behavior to be true. GET_LOCK from the first procedure ends up taking some small but non-zero amount of time of course, and since the lock isn't assigned in order of request, is its possible for the lock to be passed down successive calls to p1 and p2 has to wait?

Answer 1

OBSERVATIONS

In your question, you said

I read from the documentation that the behaviour of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined.

You are overlooking something far more basic

MySQL Documentation clear says the following on GET_LOCK()

A negative timeout value means infinite timeout.

If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

The Documentation says nothing about a zero(0) timeout value. Thus, that behavior, in itself, is simplified. If it acquires the lock, it immediately returns 1. If it does not acquire the lock, it immediately returns zero(0).

You said p2 has a 10-second timeout (because you said you are using GET_LOCK("lock",10)).

If you are expecting p1() to pause execution, using a non-positive will not accomplish this.

SUGGESTION

You may want to execute a loop in your Stored Procedure to attempt to acquire the lock with some arbitrary number of seconds to pause between lock attempts:

SET @seconds_to_pause = 5;
SET @lock_acquired = GET_LOCK('lock',0);
WHILE @lock_acquired = 0 DO
    SET @x = SLEEP(@seconds_to_pause);
    SET @lock_acquired = GET_LOCK('lock',0);
END WHILE;

Give it a Try !!

YOUR ORIGINAL QUESTION

is it possible for the lock to be passed down successive calls to p1 and p2 has to wait?

If you do not have a loop to acquired the lock as I suggested,the answer would have to be no.