Home » Learn – MySQL Table Locking

Learn – MySQL Table Locking

  • by

Learn – MySQL Table Locking

A table lock is a mechanism that prevents unauthorised access to the data stored in the table. MySQL allows a client session to expressly acquire a table lock in order to share the table’s contents with other sessions. MySQL also supports table locking, which prevents unauthorised changes to the same table within a specified time period.  In MySQL, a session can only acquire or release locks on the table for itself. As a result, one session will not be able to acquire or release table locks for other sessions.

It’s worth noting that table locking requires TABLE LOCK and SELECT rights.  In MySQL, table locking is primarily used to tackle concurrency issues. It will be utilised during the execution of a transaction, that is, reading a value from a table (database) and subsequently writing it to the table (database).

There are two types of locks available in MySQL.

 

READ LOCK: 

This lock restricts user’s access to table’s data. 

WRITE LOCK: 

This lock allows user to read and write into table at the same time. 
It’s worth noting that MySQL’s default storage engine is InnoDB. 
Because MySQL uses row-level locking for InnoDB tables, the InnoDB storage engine does not require explicit table locking. 
As result, we may do several read and write operations on the same table at the same time without having to wait for each other. 
Table locking is used by all other storage engines except MySQL. 
Before we get into the table locking concept, let’s make new table called “test table” with the following statement:

CREATE TABLE test_table (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Message VARCHAR(80) NOT NULL,
PRIMARY KEY (Id)
);

MySQL LOCK TABLES Statement

The syntax that allows us to explicitly obtain table lock is as follows: 
TABLE NAME [READ WRITE] LOCK TABLES; 
After the LOCK TABLES keywords, we specify the table name on which we want to acquire lock in the above format. 
We can choose between READ and WRITE for the lock type.
We can also use list of comma-separated table names with lock types to lock several tables in MySQL. 
Consider the following syntax:

LOCK TABLES tab_name1 [READ | WRITE],
tab_name2 [READ | WRITE],...... ;

MySQL UNLOCK TABLES Statement

The following is the syntax that allows us to release a lock for a table in MySQL

mysql > UNLOCK TABLES;

LOCK TYPES

Let us understand the lock types in detail.

READ Locks:

The READ lock has the following features: 
MySQL allows several sessions to get READ lock on table at the same time. 
All other sessions, on the other hand, can read the table without obtaining the lock.
They can’t write to table if the session has the READ lock on it. 
Because the READ lock can only read data from the table, this is the case. 
Other sessions that do not obtain READ lock are unable to write data into the table until the READ lock is released. 
The write operations are held in waiting state until the READ lock is freed.
MySQL implicitly releases all types of locks on the table when the session is terminated normally or abnormally. 
This feature applies to the WRITE lock as well. 
Let’s use the provided case as an example to show how READ locks work in MySQL. 
In the first session, we’ll connect to the database and use the CONNECTION ID() function to get the current connection id.

mysql > SELECT CONNECTION_ID();

See the below output:

Next, we will insert few rows into the test_table using the below statement

mysql > INSERT INTO test_table (name, message)
VALUES('Peter', 'Hi'),
('Joseph', 'Hello'),
('Mark', 'Welcome');

Now, verify the data into the table using the below statement

mysql > SELECT * FROM test_table;

We should see the output as follows:

Now, we will execute the LOCK TABLE statement to acquire a lock onto the table

mysql > LOCK TABLE test_table READ;

After that, we will try to insert a new record into the test_table as follows

mysql > INSERT INTO test_table (name, message)
VALUES ('Suzi', 'Hi');

We’ll get the following output, with the message “Table ‘test table’ was locked with READ lock and can’t be changed” from MySQL.
As result, once the READ lock on the table is acquired, we cannot write data to the table in the same session.
Now let’s have look at how the READ lock works in separate session. 
We’ll start by connecting to the database and looking at the connection id:
Next, we’ll run query on the test table, which will yield the following results:
Then, enter few rows into this table as follows

mysql > INSERT INTO test_table (name, message)

VALUES ('Stephen', 'Hello');

The insert operation from the second session is in the waiting state, as seen in the above output. 
It’s because of the READ lock, which the first session has already obtained on the table and has yet to be released. 
In the first session, we may see extensive information about them by using the SHOW PROCESSLIST statement.
Finally, in the first session, we must release the lock by using the UNLOCK TABLES statement. 
In the second session, we may now do the INSERT operation.
Locks that can be written 

WRITE lock has the following characteristics:

It’s the session that has control over table’s lock and can read and write data from it.
It is the only session that uses lock to access the table. 
And until the WRITE lock is freed, no other sessions can access the table’s data. 
Let’s look at an example to see how MySQL handles WRITE locks in the given case. 
In the first session, we’ll use the following command to get WRITE lock.

mysql > LOCK TABLE test_table WRITE;

Then, we will insert a new record into the test_table as follows

mysql > INSERT INTO test_table (name, message)
VALUES ('Stephen', 'How R U');

The above statement worked. Now, we can verify the output using the SELECT statement:

Again, we will attempt to access (read/write) the table from the second session:

INSERT INTO test_table (name, message)
VALUES ('George', 'Welcome');
SELECT * FROM test_table;

We can observe that these procedures have been placed in condition of waiting. 
Using the SHOW PROCESSLIST command, you can get further information about them:
Finally, the first session’s lock will be released.  We can now carry out the pending operations.

Write Lock vs. Read Lock

Because many threads can acquire it at the same time, read locks are akin to “shared” locks.
Because another thread cannot read write lock, it is referred to as “exclusive” lock.
On the table, we can’t give read and write locks at the same time.
The priority of Read lock is lower than that of Write lock, ensuring that updates are made as quickly as possible.