Run SELECT Query Without Locking in MySQL

1. Overview

MySQL internally uses locks at different granularity levels to regulate multiple sessions trying to access and update the same schema objects (table, index) simultaneously. MySQL’s design objective is to provide a high concurrency level by enabling multiple user sessions to access the same schema objects concurrently while preventing operations that require exclusive access to the same data from occurring concurrently.

In this tutorial, we’ll learn about the different options for running a SELECT query without locking in MySQL. To demonstrate, we’ll use the Department table from the University database.

2. Running SELECT Using Select Expressions Only

We can run a SELECT query using only select expressions without referring to any schema object. When we run SELECT using only select expressions, MySQL doesn’t use locks for the query. Let’s run an example query:

SELECT 1, 
SQRT(4), 
JSON_QUOTE('[a, b, c]');
+---+---------+-------------------------+
| 1 | SQRT(4) | JSON_QUOTE('[a, b, c]') |
+---+---------+-------------------------+
| 1 |       2 | "[a, b, c]"             |
+---+---------+-------------------------+

This SELECT query doesn’t require MySQL to use any locks regardless of the storage engine used.

3. Choosing the Storage Engine

MySQL uses different types of locks for different types of storage engines.

3.1. Non-transactional Storage Engines

Accordingly, MySQL uses only table-level locking for non-transactional storage engines such as MyISAM, MEMORY, and MERGE. It uses table write locks, and table read locks to group write and read requests in separate queues; a write lock queue and a read lock queue respectively. We can’t run a SELECT query on a table in a non-transactional storage engine without requiring a lock. Furthermore, non-transactional storage engines give a higher priority to writes or updates than to reads or data retrievals. As a result, a SELECT query may need to wait to acquire a table-level lock while other queries on the same table are running.

2.2. InnoDB Storage Engine

InnoDB, the only transactional engine in MySQL, uses row-level locking on tables. As a resulting benefit, multiple user sessions can read from and write to the same table simultaneously without making other queries wait. MySQL supports locking reads and non-locking reads when retrieving data from a table in InnoDB. Including either of the two clauses, FOR UPDATE and FOR SHARE, makes a SELECT query a locking-read. Excluding these clauses makes a SELECT query a non-locking read.

By default, a plain SELECT query doesn’t require any locks in InnoDB. However, MySQL uses table-level intention locks for locking reads. It uses a shared mode lock when a SELECT query includes the FOR SHARE clause. Further, it uses an exclusive mode lock when a SELECT query includes the FOR UPDATE clause. The shared/exclusive locks acquired by SELECT .. FOR SHARE and SELECT … FOR UPDATE are “intention locks” at the table level. Furthermore, even a plain SELECT query could require a lock depending on the transaction characteristics.

Notably, we can mix transactional and non-transactional tables in the same SELECT query. A table in a transactional storage engine is called a transactional table. Conversely, a table in a non-transactional storage engine is called a non-transactional table.

Let’s explore how we can run SELECT queries without requiring locks when we use the InnoDB storage engine.

4. Turning on the Autocommit Mode

We can turn on the AUTOCOMMIT mode to run a SELECT query without using any locks. Afterward, any SELECT query that we run won’t acquire locks. The locking-read clauses, FOR UPDATE and FOR SHARE, are ignored when the default transaction characteristics are used.

5. Setting the Access Mode to READ ONLY

We can configure the transaction characteristics to support only non-locking reads. We set a transaction’s access mode transaction characteristic to READ ONLY to allow read-only SELECT queries. MySQL (InnoDB) sets the default access mode to READ WRITE, which allows both reads and updates. However, we can modify the access mode to READ ONLY at the global session, or only the next single transaction with the SET TRANSACTION SQL statement:

SET SESSION TRANSACTION READ ONLY;

However, setting the access mode to READ ONLY doesn’t convert locking reads into non-locking reads. It only prevents locking reads from running.

5.1. Running a Plain SELECT Query

We can set the next single transaction in the current session to use READ ONLY access mode by omitting the scope (GLOBAL or SESSION). The new access mode applies to the next single transaction, whether automatic or not. Let’s set the access mode for the next transaction to READ ONLY. Afterward, we can’t run any SELECT query that updates data. In other words, we can’t run a SELECT … FOR UPDATE statement even when the AUTOCOMMIT mode is ON. To demonstrate, let’s run a SELECT … FOR UPDATE statement:

SET TRANSACTION READ ONLY;

SELECT * 
FROM DEPARTMENT 
FOR UPDATE;

As a result of the READ ONLY access mode, we get an error message:

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction

However, we can run a plain SELECT query:

SET TRANSACTION READ ONLY; 

SELECT * 
FROM DEPARTMENT;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+

The plain SELECT query is run without acquiring any locks.

5.2. Running a SELECT … FOR SHARE Query

Furthermore, we can even run a SELECT query that includes the FOR SHARE clause. However, SELECT … FOR SHARE acquires a shared mode lock when we run it with AUTOCOMMIT mode turned OFF. Let’s demonstrate running a SELECT … FOR SHARE query by using two user sessions:

//First Session 
SET TRANSACTION READ ONLY; 

START TRANSACTION; 

SELECT * 
FROM DEPARTMENT 
FOR SHARE; 
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+

Let’s start a second transaction in a second user session and run the same SELECT … FOR SHARE statement:

//Second Session 

START TRANSACTION; 

SELECT * 
FROM DEPARTMENT 
FOR SHARE; 
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+

We can run the SELECT … FOR SHARE statement even though another user session has a lock on the same table because it acquires a shared mode lock each time it’s run. However, with a shared mode lock in use, we can’t run a statement that requires an exclusive lock. To demonstrate, let’s continue in the same transaction in the second session and run an UPDATE query:

//Second Session 

UPDATE DEPARTMENT 
SET code='MA' 
WHERE id=5;

However, this time, the UPDATE statement hangs and doesn’t get run to completion. The UPDATE statement needs an exclusive lock that it can’t get. After all, the same table has a shared mode table-level intention locks set.

6. Choosing a Transaction Isolation Level

We can fine-tune the locking strategy by using transaction isolation levels. Typically, a tradeoff is involved between read consistency and concurrency when balancing the two. MySQL (InnoDB) supports four transaction isolation levels:

• REPEATABLE READ
• READ COMMITTED
• READ UNCOMMITTED
• SERIALIZABLE

The transaction isolation levels apply to both automatic (AUTOCOMMIT mode ON) and non-automatic transactions. Plain SELECT queries don’t require locks when we set the transaction isolation level to REPEATABLE READ, READ COMMITTED, or READ UNCOMMITTED. This is one of the reasons a user may want to switch to using the transactional storage engine InnoDB as plain SELECT queries in non-transactional engines require a table-level lock.

Let’s remember that a plain SELECT statement isn’t the only type of query a user may want to run. The REPEATABLE READ may be the best transaction isolation level if a simple SELECT query is all a user typically runs. However, if a user also runs locking statements (SELECT .. FOR UPDATE, SELECT … FOR SHARE, UPDATE, and DELETE), the REPEATABLE READ transaction isolation level acquires more locks than the READ COMMITTED or the READ UNCOMMITTED.

A plain SELECT query is converted to SELECT … FOR SHARE when we use the SERIALIZABLE level with the AUTOCOMMIT mode turned off. Therefore, even a plain SELECT query requires some level of locks with the SERIALIZABLE level when we run the query within an explicit/implicit transaction. We turn on the AUTOCOMMIT mode to run a plain SELECT query without requiring locks when the transaction isolation level is SERIALIZABLE.

7. Using SELECT in Sub-Queries

We can include a SELECT statement within another query. A plain SELECT query can have different locking characteristics than the outer SQL statement when run as a sub-query. To demonstrate, let’s run a plain non-locking read within a locking outer read.

CREATE TABLE DEPARTMENT_copy 
AS SELECT * 
FROM DEPARTMENT; 

SELECT * 
FROM DEPARTMENT_copy 
WHERE code = (SELECT code FROM DEPARTMENT WHERE id=5) 
FOR UPDATE;

As a result, the outer SELECT query acquires an exclusive mode table-level intention lock on the DEPARTMENT_copy table, but the inner SELECT query doesn’t acquire a lock on the DEPARTMENT table or its rows.

Notably, the REPEATABLE READ performs a consistent,  non-locking read for a plain SELECT query, but not when the plain SELECT query is included as a sub-query within another SQL statement that doesn’t specify the FOR SHARE or the FOR UPDATE clause. Such SELECT sub-queries are nested within other SQL statements:

• INSERT INTO … SELECT
• UPDATE … (SELECT)
• CREATE TABLE … SELECT

We should expect inconsistent results because the SELECT sub-queries aren’t run in the REPEATABLE READ isolation level even though we set them to, but are promoted to the READ COMMITTED level. Therefore, if we want the sub-queries to run as non-locking SELECT queries, we need to set the transaction isolation to READ COMMITTED or READ UNCOMMITTED.

We can verify if a transaction is acquiring locks by enabling the InnoDB Lock Monitor so that it prints additional information about locks in SHOW ENGINE INNODB STATUS output:

SET GLOBAL innodb_status_output_locks=ON;

Afterward, we can run SELECT queries in new user sessions and find if a transaction acquires locks. The TRANSACTIONS section of the output lists locks acquired, if any:

SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 428086
 
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 428085, ACTIVE 358 sec
2 lock struct(s), heap size 1128, 6 row lock(s)
...
TABLE LOCK table `university`.`department` trx id 428085 lock mode IX
RECORD LOCKS space id 113 page no 4 n bits 88 index PRIMARY of table `university
`.`department` trx id 428085 lock_mode X
...

From the output, we may determine if a SELECT query is using locks.

8. Conclusion

In this article, we learned about running SELECT queries without using locks. Non-transactional storage engines such as MyISAM always use a table-level lock for each SELECT query. By using the transactional storage engine InnoDB, we can run plain SELECT queries without the FOR SHARE or the FOR UPDATE clause without requiring a lock. Further, we fine-tune the locking strategy in InnoDB by using an appropriate transaction access mode and transaction isolation level.