MySQL Named Locks: A Way to GET_LOCK and Beyond

Have you ever found yourself in a situation where you need to coordinate operations across multiple MySQL sessions?

Perhaps you’re building an application that requires exclusive access to a specific resource, or you need to prevent race conditions during critical updates.

These are the situations in which MySQL’s named locks come into play, offering a powerful mechanism to manage concurrency within your database.

What Is a Named Lock?

This is how I explain it to my kids:

Imagine you and your friends want to take turns playing with a favorite toy so that nobody fights over it. You invent a system: if someone is playing with the toy, they say a special word — like “MyTurn!” — so everyone knows the toy is busy. When they’re done, they say “I’m done!” so someone else can play.

MySQL’s named locks work like this “special word”:

• The “toy” can be anything — maybe drawing pictures, reading a book, or using a computer.
• The “special word,” such as'MY_deadlock', is just a name everyone agrees on to indicate who is allowed to use the toy (or do the special action) at that time.
• If you want a turn, you ask, “Can I use ‘MY_deadlock’?” If nobody else is using it, you get your turn; if someone is already using it, you have to wait for your turn.
• When you’re finished, you tell everyone, “I’m done with ‘MY_deadlock’!” so someone else can use it.

Here are some important things:

• The name doesn’t belong to anything. It simply allows everyone to take turns fairly.
• If someone forgets to say “I’m done” (release the lock), others have to keep waiting — even if nobody is playing.
• MySQL does not block anyone from doing other things; it only controls who is using the named lock word.
• If you use a new word, it’s a new waitlist for a new toy.

So, a named lock is like raising your hand and saying a magic word so your friends know it’s your turn.

Now that the concept is clear, let’s dive into the core functions that empower us to wield named locks effectively.

GET_LOCK: The Gateway to Exclusive Access

The GET_LOCK() function is your primary tool for acquiring a named lock. Here's how it works:

SELECT GET_LOCK('MY_deadlock', 3);

In this example, we’re attempting to obtain a named lock called ‘MY_deadlock’ for the current session. The 3 indicates that we're willing to wait up to three seconds to acquire this lock.

What Do the Return Values Mean?

• 1: Success. You've successfully acquired the lock.
• 0: Failure to acquire. This typically means another session currently holds the lock, and the timeout period has expired.
• NULL: The attempt failed for another reason (e.g., an internal error).

This function is incredibly useful for ensuring that only one session can proceed with a specific operation at a time, preventing data corruption and maintaining data integrity.

RELEASE_LOCK: The Relinquishing Control

Once we’ve finished with our critical operation, it’s crucial to release the lock to allow other sessions to acquire it. This is where RELEASE_LOCK() comes in:

SELECT RELEASE_LOCK('MY_deadlock');

When does a named lock get released?

• Explicitly, with RELEASE_LOCK(): This is the best practice and ensures you have full control.
• When the session terminates: All named locks held by a session are automatically released when that session closes.

MySQL also provides functions to inspect the status of named locks without attempting to acquire or release them.

IS_FREE_LOCK: Is the Coast Clear?

This function tells us whether a named lock is currently available:

SELECT IS_FREE_LOCK('MY_deadlock');

• 1: The lock is free and can be acquired.
• 0: The lock is currently held by another session.

IS_USED_LOCK: Who's Holding the Lock?

If we need to know which session is holding a particular lock, IS_USED_LOCK() is your friend:

SELECT IS_USED_LOCK('MY_deadlock');

• Connection ID: Returns the connection ID of the session that currently holds the lock.
• NULL: The lock is not currently held by any session.

Practical Use Case

Suppose we have a web application where users can “check out” a document for editing, and we want to ensure only one user can edit a specific document at a time:

Step 1: User A clicks “Edit” for document 42.

The system tries to acquire the lock when a user begins editing a document, so the application issues the GET_LOCK command:

SELECT GET_LOCK(CONCAT('edit_doc_', @doc_id), 5);

This statement tries to acquire a named lock (‘edit_doc_42’) for up to five seconds. The return of the command is 1, which means that the lock was granted and the editing session can proceed.

Step 2: User B tries to edit document 42 at the same time. The system tries to acquire the lock, so the application issues the GET_LOCK command:

SELECT GET_LOCK(CONCAT('edit_doc_', @doc_id), 5);

Since User A is still editing, User B receives 0 (timeout), and the application displays "Document is being edited by another user."

Step 3: When User A finishes editing, the application issues the RELEASE_LOCK command:

SELECT RELEASE_LOCK(CONCAT('edit_doc_', @doc_id));

This releases the named lock, allowing other users to acquire it for that document.

Step 4: User B tries, for the second time, to edit document 42. The system tries to acquire the lock, so the application issues the GET_LOCK command:

SELECT GET_LOCK(CONCAT('edit_doc_', @doc_id), 5);

The return of the command is 1, which means that the lock was granted for User B and the editing session can proceed.

Step 5: When User B finishes the editing, the application issues the RELEASE_LOCK command:

SELECT RELEASE_LOCK(CONCAT('edit_doc_', @doc_id));

This releases the named lock, allowing other users to acquire it for that document.

Conclusion

Named locks are powerful for various scenarios, including:

• Preventing duplicate entries: Before inserting a new record, you could acquire a lock to ensure no other session inserts the same data simultaneously.
• Implementing distributed mutexes: In a distributed application, named locks serve as a simple mutex to coordinate access to shared resources across multiple application instances.
• Controlling access to external resources: If our database interactions trigger operations on external systems, named locks can help serialize these operations.

By understanding and utilizing GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK(), we build more robust and concurrent applications on MySQL, effectively managing shared resources and preventing potential conflicts.