In MySQL, a lock refers to a special flag that is included in a database table. A lock prevents other sessions from accessing the same table simultaneously. A MySQL session can connect and access or release the lock for itself. This means a session cannot acquire or release locks for other sessions. Once a lock is released, additional sessions can access and modify the table.
This article will explore the fundamentals of locks in MySQL and how we can create one and acquire and release a lock to a database table.
Create Sample Table
Before proceeding with the creation and acquisition of locks in MySQL, let us create a sample table we can use to illustrate this code.
Code snippet:
How to Create Lock in MySQL
Once we have the sample table created and ready to go, we can proceed and discuss how to create a lock in a MySQL table.
As you guessed, to create a lock in MySQL, we use the LOCK TABLES statement.
The syntax is as shown below:
In the statement above, we start with the LOCK TABLES keyword followed by the table name we wish to lock.
We then specify the type of lock we wish to create for the set table. Supported lock types:
- READ [LOCAL] –Session can read the table but not write to it.
a. This type of lock can be used by multiple sessions concurrently as no alterations are applied to the table.
b. Similarly, other sessions with no READ lock can also read into the table. - [LOW PRIORITY] WRITE – this type of lock allows the session to read and write to the table.
a. Unlike the READ lock, only a single client can acquire a WRITE lock to a given table.
b. MySQL will also block any lock requests by other sessions while this lock is being held.
Create Read Lock
We can create a read lock to the table we created earlier by running the code as shown:
This should create a READ lock to the table allowing only read operations. We can verify this by inserting a record into the table.
Since the table is locked with a READ lock, MySQL will return an error as shown:
To insert into the table, you can release the lock by using the UNLOCK statement.
This should unlock the tables and allow you and other sessions to perform the target operations.
MySQL Check if Named Lock is Free
Luckily, MySQL provides us with a simple function that allows us to determine if a named lock is free or not.
The function syntax is as shown below:
Where str refers to the name of the lock, you wish to check if it’s free. If free, the function returns 1. Hence no session is using the lock. Otherwise, the function returns 0, meaning the lock is in use.
Another function you need to know is the IS_USED_LOCK() function. The syntax is as shown:
The function determines whether the stated lock is free. If true, return session’s connection identifier. Else, return NULL.
Conclusion
This article explored how to work with Locks in the MySQL database. We discussed how you could create an acquire a lock to a specific table, how to unlock a table, and how to check if a lock is free or not.
