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?
Best Answer
OBSERVATIONS
In your question, you said
You are overlooking something far more basic
MySQL Documentation clear says the following on GET_LOCK()
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 usingGET_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:
Give it a Try !!
YOUR ORIGINAL QUESTION
If you do not have a loop to acquired the lock as I suggested,the answer would have to be no.