Learning Journey in SQL

From A to SQL: Using Table Locks for Data Integrity Maintenance

Welcome to my “From A to SQL” series, designed to provide you with the foundational knowledge base for SQL and data analysis. If you are new here, this series has been growing steadily, and I have covered a wide variety of topics so far, from creating a database to backing it up. You can find my latest article, on the topic of views, here.

In this chapter, I will be walking you through a concept called table locking, and discussing the two types of locks that can be added to a table. Any examples will use the business database that I have been creating along the way. If you want to follow along and would like the previous code, simply head to the other articles, staring at chapter 2.

Photo by Sergey Zolkin on Unsplash

So, what is table locking?

Let me set a scene. When working on a personal project, you may be the only person creating and updating a database. However, let’s imagine you have paired up with someone and you both are now connected to the same database, and want to perform different actions on the same tables. You may want to perform a batch update of data on a table, but you are worried that your project partner might accidentally change the table structure or data at the same time. How do we stop this from happening? In come the table locks.

A table lock, as the name suggests, is a lock we can add to a table to prevent another user or session from accessing tables or making data changes for a period of time. It’s an essential practice to learn, especially when dealing with management of transactions, and it’s primarily used to ensure data integrity in the database. Data integrity just means that the data in the database is accuracy, consistent and complete, and matches what we expect.

Are there different types of table locks?

There are two types of table locks: read and write lock. Let’s explore both of these in more detail.

1. Read Lock

The session or user which has added the Read Lock onto a table, can only see or read the data in the table, but cannot modify it. Any attempts to modify the table or the data will result in an error. Other users or sessions also cannot modify the data. However, they will not experience an error, however these changes are not made straight away, rather place in a holding queue, which are implemented once the read lock is released.

2. Write Lock

The session or user which has added the Write Lock onto a table has special access to this table. Only this session or use can read the data and modify it. Any other user or session will not be able to read the data or modify it while the lock is in place.

How do I add a table lock?

The process of adding a table lock is very simple. The syntax for a read lock is as follows:

LOCK TABLE table_name READ;

Meanwhile, if you want to add a write lock, use the following syntax:

LOCK TABLE table_name WRITE; 

If you want to add multiple locks at the same time, use the following syntax, choosing the correct lock type for each table:

LOCK TABLE
table_name [READ | WRITE] , 
table_name2 [READ | WRITE], 
table_name3 [READ | WRITE] ;

Can I check what processes are in the holding queue?

I mentioned earlier that if a table has a read lock, but a different user attempts to write data to that table, they won’t be able to modify it, however their changes will go into a holding queue. You can check what processes are in this queue by using the following syntax:

SHOW PROCESSLIST;

Optionally you can say SHOW FULL PROCESSLIST if you want more detail about the state of the process.

How can I check which tables are locked?

It can be useful to check which tables are locked, especially when locking multiple tables at once. You can use the following basic syntax to bring up a list of tables that are locked in your database:

SHOW OPEN TABLES; 

The output will show you four columns: database, table, in_use and name_lock.

To be able to refine our results, to show only particular tables or only those which are in use, we can use the following syntax:

SHOW OPEN TABLES
    [{FROM | IN} database_name]  [LIKE 'pattern' | WHERE expr] ;

Don’t be overwhelmed by this! There are some options here which you can specify. First, you can specify a specific database, by writing IN database_name. Secondly, you can specify tables by using the LIKE operator. Last, you can use the WHERE clause, to return only tables which are locked by writing WHERE in_use = 1.

How do I release a lock?

Again, the process of releasing a table lock is very simple. The syntax for this is as follows:

UNLOCK TABLES; 

Now let’s use our new knowledge in an example.

Ready? Set? LOCK.

To begin, we are going to use our Business database, and create an audit table, to enable us to log an audit message and audit time.

USE Business;


CREATE TABLE audit_log (
    audit_key INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    audit_message VARCHAR(200) NOT NULL, 
    audit_time DATETIME NOT NULL DEFAULT NOW() 
);

INSERT INTO audit_log
(audit_message) 
VALUES('Audit 1 complete');

SELECT 
    *
FROM
    audit_log;

If we select everything from our table, we can see the following result:

SQL output grid. Image created by author.

Now, let’s lock the table with a read lock, and try to add some more data to the table.

LOCK TABLE audit_log READ;

INSERT INTO audit_log
(audit_message) 
VALUES('Audit 2 complete');

SQL action output message. Image created by author.

As you can see, an error appears stating that we can’t add data while a read lock is in place.

Let’s now switch to a different user and see what happens when we try to insert some data. I have created a user called ‘otheruser’ and connected to the Business database. If you would like a guide on how to do this, please let me know in the comments below. Let’s see what the outcome is.

USE Business;

INSERT INTO audit_log
(audit_message) 
VALUES('Audit 2 complete');

SQL action output message. Image created by author.

There is no error, but the task is placed in a holding queue.

Let’s switch back to the main user and see if we can confirm which tables are locked and see what’s in the holding queue.

SHOW FULL PROCESSLIST;

SQL output grid. Image created by author.

This returns the output I described above, showing that there is an Insert statement waiting in the queue started by the ‘otheruser’ user. We can also double-check the correct tables are locked.

SHOW OPEN TABLES in Business;

SHOW OPEN TABLES in Business WHERE in_use = 1;

SQL output grid. Image created by author.

SQL output grid. Image created by author.

Running this first statement, we can see all tables in the business database. Where in_use is 0, the table is open, and where in_use is 1, the table is locked. We can utilise the WHERE clause and refine our result to just locked tables. As expected, the audit_log table is locked.

Now let’s release the lock on the table and see what happens to our data.

UNLOCK TABLES;

SELECT 
    *
FROM
    audit_log;

SQL output grid. Image created by author.

We can see that after unlocking the table, the Insert statement was able to run, and our second message was input into the audit log table.

Congratulations! You are another step closer to advancing your SQL skills and knowing more about maintaining data integrity through the use of table locks. Let me know in the comments if you would like me to cover any specific topics in upcoming chapters.

Keep your eyes peeled for the next sequel of my learning journey :)

Go from SELECT * to interview-worthy project. Get our free 5-page guide.