MySQL uses locks for concurrency control. Whenever a client/thread acquires a lock, it will have exclusive access to that table or row (depending on the granularity of the lock). Other clients however, will be prevented from writing and possibly reading to/from the locked resource. The two main existing locks are:

READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.

Also, different storage engines have different lock granularity. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.

Let’s try this out using a MyISAM table. We open two concurrent sessions and in the first lock the City table from the world database (get it …

A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL's GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.

This post was prompted by a message to the MySQL general emailing list some time ago, but I'm finally getting around to actually testing the theoretical solution I mentioned then. I can never just think my way through anything that involves locking and waiting... I have to test it.