In the wake of some recent discussion about the operation of GET_LOCK(), I've written the following as an attempt to clarify how it works. I'd appreciate any comments or corrections. Thanks.

GET_LOCK(str,timeout)

GET_LOCK() is used in conjunction with RELEASE_LOCK() to perform advisory (cooperative) locking. You can use the two functions to write applications that cooperate based on the status of an agreed-upon lock name.

GET_LOCK() is called with a lock name indicated by the string str and a timeout value of timeout seconds. It returns 1 if the lock was obtained successfully within the timeout period, 0 if the lock attempt failed due to timing out, or NULL if an error occurred. The timeout value determines how long to wait while attempting to obtain the lock, not the duration of the lock; once obtained, the lock remains in force until released.

The following call acquires a lock named "my lock", waiting up to 10 seconds for it:

GET_LOCK("my lock", 10)

The lock applies only to the string itself. It does not lock a database, a table, or any rows or columns within a table. In other words, the lock does not prevent any other client from doing anything to database tables, which is why GET_LOCK() locking is advisory only - it simply allows other cooperating clients to determine whether or not the lock is in force. A client that has a lock on a name blocks attempts by other clients to lock the name (or attempts by other threads within a multi-threaded client that maintains multiple connections to the server). Suppose client 1 locks the string "my lock". If client 2 attempts to lock the same string, it will block waiting for the lock until client 1 releases it or until the timeout period expires. In the former case, client 2 will acquire the lock successfully; in the latter case, it will fail.

Since two clients cannot lock a given string at the same time, applications that agree on a name can use the lock status of that name as an indicator of when it is safe to perform operations related to the name. For example, you can construct a lock name based on a unique key value for a row in a table to allow cooperative locking of that row.

A lock is released explicitly by calling RELEASE_LOCK() with the lock name.

RELEASE_LOCK("my lock")

RELEASE_LOCK() returns 1 if the lock was released successfully, 0 if the lock was held by another connection (you can only release your own locks), or NULL if no such lock exists.

Any lock held by a client connection is also automatically released when the connection terminates, or if the client issues another GET_LOCK() call (a client connection can lock only one string at a time). In the latter case, the lock being held is released before the new lock is obtained, even if the lock name is the same.

GETLOCK(str,0) may be used as a simple poll to determine without waiting whether or not a lock on str is in force. (This will of course lock the string if it is not currently locked, so remember to call RELEASE_LOCK() as appropriate.)