User-level locks are controlled with the SQL functions
GET_LOCK(str,timeout) IS_FREE_LOCK(str) IS_USED_LOCK(str) RELEASE_LOCK(str)
They can be used at places where SQL statements accept SQL functions. Depending on their appearance in the select list, the where clause, the group by clause, etc, of select, update or other statements, these statements can be blocked at different code points. The set of blockable places is limited. Nevertheless, a couple of synchronization problems can be solved with user-level locks.
# Using InnoDB table with innodb_lock_wait_timeout=1 second. --connection conn1 # Take an share lock on t1. LOCK TABLE t1 IN SHARE MODE; --connection conn2 # Acquire the user level lock "mysqltest1". SELECT GET_LOCK("mysqltest1", 10); # INSERT must wait in background for the SQL lock on t1 to go away. send INSERT INTO t1 VALUES (1); --connection conn1 # Wait in background until the insert times out and releases the # user level lock. conn1 will then own the lock. send SELECT GET_LOCK("mysqltest1", 10); --connection conn2 # Wait for INSERT to timeout. --error ER_LOCK_WAIT_TIMEOUT reap; # Now let conn1 get the lock and continue. SELECT RELEASE_LOCK("mysqltest1"); COMMIT; --connection conn1 reap; # We do not need the lock any more. SELECT RELEASE_LOCK("mysqltest1"); # Commit releases the share lock on t1. COMMIT;
A good article about possible uses of user-level locks is from Martin Friebe. MySQL Internals mailing list, 10 Dec 2007: http://lists.mysql.com/internals/35220
One limitation of user-level locks is that a thread can have one lock at a time only. This limits the method to relatively simple cases.