Why mutexes are required in MySQL

What is a mutex?

In computer programming, a mutex (mutual exclusion object) is a program object that is created so that multiple threads in the application can take turns sharing the same resource, such as access to a file. Typically, when a program is started, it creates a mutex for a given resource at the beginning by requesting it from the system and the system returns a unique name or ID for it. After that, any thread needing the resource must use the mutex to lock the resource from other threads while it is using the resource. If the mutex is already locked, a thread needing the resource is typically queued by the system and then given control when the mutex becomes unlocked (the thread that will get the mutex depends upon the thread scheduling algorithm applicable to the mutex context in question). This is needed to avoid race condition and to employ synchronization among threads to keep the database system consistent all the time.

What is a Race Condition?

A race condition occurs when two or more threads can access shared data and they try to change the data at the same time. Because the thread scheduling algorithm can swap between threads at any time, you don't know the order in which the threads will attempt to access the shared data. Therefore, the result of the change in data is dependent on the thread scheduling algorithm, that is the threads are "racing" to access/change the data.

I have read an article, where a very good analogy has been mentioned that relates to the above.

To manage a conference, three entities are used:

• Coordinator: He/She will manage the speakers.
• Speakers: Persons that are going to talk on the topic/agenda.
• Key: It is an object to handle communication between speakers.

The speaker holding the key is the only person who is allowed to talk. If you do not hold the key you cannot speak. You can only indicate that you want the key and wait until you get it before you speak. Once you have finished speaking, you can hand the key back to the coordinator who will hand it to the next person (picked up through thread scheduling algorithm) to speak. This ensures that people do not speak over each other (race condition), and also have their own space to talk.

Replace coordinator with OS, speakers with thread and key with the mutex.

MySQL is a well known, open source application that uses multiple threading to handle client connections and client requests. MySQL uses various mutex to make sure that multiple clients requesting the same resource (log files, table row etc.) get access to it one at a time.

mutex is one of the way how MySQL implements synchronization for multiple threads. Other methods are COND (conditions) and rwlock (read write lock). rwlock is an enhanced version due to which it is possible that two or more threads are reading a shared resource at the same time but only a single thread can be writing a shared resource at one point of time.

It may be possible that you have seen a mention of Thread Safe and Thread Unsafe keyword in the download of application.

Thread safe means that the associated application code implements mutex and hence ok to be used as multiple threaded application whereas Thread Unsafe means that the associated application result can be erratic in multiple threading.