MySQL get_lock timeout misunderstanding?

Question

Am I missing something here. I'm looking at using GET_LOCK to allow two applications to co-ordinate which data to wok on.

From reading the manual it suggests that I need to apply a timeout to the GET_LOCK command.

e.g.

SELECT GET_LOCK('testlock1',10);

So I'm thinking that this means I am creating a lock called testlock1 that will remain in place until either I release it, or 10 seconds has passed.

If I then open another session (simulating a second application), and try to run SELECT GET_LOCK('testlock1',10); it just returns a 0.

No matter how long I leave it, the lock remains in place.

I would presume the full process would be something like:

SET @id = "SELECT id from dbname.tablename WHERE . . . "
SELECT GET_LOCK(CONCAT(@id,'-dbname-tablename'),10); 
# DO STUFF HERE . . .
UPDATE dbname.tablename . . . WHERE id = @id;
RELEASE GET_LOCK(CONCAT(@id,'-dbname-tablename'));

But if something happens, and the RELEASE_LOCK doesn't run (and the session remains open), that row will, in essence, be locked from any further updates. Hence the reason I presumed the timeout was there?

Answer 1

The timeout applies to how long GET_LOCK will wait to acquire the lock (before timing out), in the event the lock is already held by another client. It does not indicate how long a LOCK will be held as you mention

Answer 2

About the only use for GET_LOCK is for coordination that needs to survive for more than a few seconds. Otherwise, InnoDB's transactions is a much better way to prevent collisions, especially at the row level.

Something like this is what you are looking for:

BEGIN;
SELECT ... FOR UPDATE;
do stuff, including modification of the row(s) SELECTed
COMMIT;

And check for errors at each step. "Deadlocks" can happen in unexpected ways. If you get a deadlock error, start over at the BEGIN.