How to Lock a Single Row in MySQL Baeldung on SQL

1. Overview

When using InnoDB in MySQL, we need to consider the rows that are locked by our queries. We may end up locking more rows than we need to, which unnecessarily blocks other queries trying to access the same rows. In this tutorial, we’ll look at how we can run SQL statements to lock only a single row.

2. A Primer on Locking in MySQL

For any DML (UPDATE, DELETE) SQL statement that changes data, MySQL locks every row scanned by a query, even those not changed. So to lock a single row, we need to filter on columns with a unique index. Let’s prove this with some experiments on SELECT … FOR UPDATE and SELECT … FOR SHARE.

Since a primary key is a unique index, this is a great way to narrow down query results to a single row. We’ll use the Department table, which has a primary key id, from the Baeldung University database to demonstrate single-row locking.

3. Using SELECT … FOR UPDATE

The SELECT … FOR UPDATE query performs a locking read, which can be run only when autocommit is turned off. This means that when we run a SELECT …. FOR UPDATE in one transaction, a second concurrent transaction cannot complete a DML statement that changes data, or even perform a locking read with intersecting rows.

3.1. Locking a Single Row

To demonstrate that it locks a single row, let’s start a transaction and run SELECT … FOR UPDATE on a single row:

// First Session
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR UPDATE;
+----+-------------+------+
| id | name        | code |
+----+-------------+------+
|  5 | Mathematics | MA   |
+----+-------------+------+

By running the statement, we’ve set an exclusive mode lock on a single row, Department#5. Another transaction can’t acquire a shared or exclusive lock on the same row of data.

Okay, let’s try to concurrently run another locking read in a separate transaction:

// Second Session
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR SHARE;

This statement hangs because the first transaction holds an exclusive lock. After we commit the transaction in the first session, the SELECT … FOR SHARE in the second session runs to completion.

The example confirms that the transaction in the first session holds a lock on the id#5 row. Next, let’s see if we can concurrently update other rows.

3.2. Updating Other Rows

Let’s try to verify that the first transaction holds a lock only for a single row.

To demonstrate, let’s again start a transaction and run the same SELECT … FOR UPDATE in the first transaction to acquire a lock on a single row.

Then, let’s start a second transaction in a different session and try to update a different set of rows:

// Second Session
START TRANSACTION;
UPDATE DEPARTMENT 
SET code = 'TEMP' 
WHERE id < 5 OR id > 5;

This statement runs to completion without hanging. Once we commit the two transactions, we can run a simple SELECT query in either session to see our changes:

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

As we can see, the UPDATE succeeded without the second transaction waiting for the first to finish.

The SELECT … FOR SHARE query performs a locking read as well. The type of the lock, however, is a shared lock in contrast to an exclusive lock acquired when we use SELECT … FOR UPDATE. Specifically, when we run a SELECT …. FOR SHARE in one transaction to lock a single row, we can’t perform the following actions in another transaction:

Update or delete that row
Run SELECT … FOR UPDATE on the row

What we mean by a shared lock is that we can run concurrent SELECT … FOR SHARE queries in other transactions; we demonstrate this later. SELECT … FOR SHARE replaces the SELECT … LOCK IN SHARE MODE statement, which remains available for backward compatibility.

4.1. Locking a Single Row

To demonstrate that it does lock a row, let’s start a transaction and run SELECT … FOR SHARE on a single row starting with the default data set:

// First Session
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR SHARE;
+----+-------------+------+
| id | name        | code |
+----+-------------+------+
|  5 | Mathematics | MA   |
+----+-------------+------+

By running the statement, we’ve set a shared mode lock on a single row, which is the row with id as 5. Let’s verify that we can run the same SELECT … FOR SHARE query in a second transaction, which can only be in a different session:

// Second Session
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR SHARE;
+----+-------------+------+
| id | name        | code |
+----+-------------+------+
|  5 | Mathematics | MA   |
+----+-------------+------+

Notably, we can run SELECT … FOR SHARE on the same row or set of rows in multiple transactions because it acquires a shared mode lock, not an exclusive lock.

However, we can’t update the same row or acquire an exclusive lock on the same row with SELECT … FOR UPDATE in the second transaction. To demonstrate, let’s try to run a SELECT … FOR UPDATE on the same row in the second transaction:

// Second Session
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR UPDATE;

This statement hangs because the first statement holds a lock, albeit a shared lock, on the row. After we commit the first transaction, the SELECT … FOR UPDATE in the second session runs to completion. This example confirms that the first transaction holds a lock on a row; however, it doesn’t tell us that it holds locks for only a single row.

4.2. Updating Other Rows

To verify that the first transaction holds a lock for only a single row, we can do the same experiment from earlier, but with SELECT … FOR SHARE.

We start a transaction and run the same SELECT … FOR SHARE in the first transaction. Then, if we try to update other rows of data in a different transaction with a different set of rows as we did for the SELECT … FOR UPDATE, it doesn’t hang since we’re updating rows that aren’t locked by the first transaction.

5. Using UPDATE, DELETE, and INSERT Statements

Even though we don’t perform the locking explicitly, when we use an UPDATE or DELETE statement to modify a single row, it locks the single row being modified until the transaction commits or rolls back. The same applies to an INSERT statement to add a single row; it acquires a single-row lock.

6. Access Mode Significance

MySQL’s access mode controls how users can connect to and interact with the database. So far, we’ve been using the default, READ WRITE. Since READ ONLY mode doesn’t allow writes, we can’t create exclusive locks. Let’s set a transaction to READ ONLY to see this in action:

SET TRANSACTION 
READ ONLY;

Thereafter, let’s start a new transaction and try to run a SELECT … FOR UPDATE statement:

START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR UPDATE;

We get an error message indicating that we can’t run this statement:

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

However, we can still run the SELECT … FOR SHARE statement, which only acquires a shared lock:

SET TRANSACTION 
READ ONLY;
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5 
FOR SHARE;
+----+-------------+------+
| id | name        | code |
+----+-------------+------+
|  5 | Mathematics | MA   |
+----+-------------+------+

7. Transaction Isolation Level Significance

MySQL’s transaction isolation levels control how multiple transactions can access the same data concurrently, ensuring the I in ACID (Atomicity, Consistency, Isolation, and Durability) principles. So far, we’ve been using the default, REPEATABLE READ. However, when we use the SERIALIZABLE isolation level, it does perform locking even for a simple query. Let’s set the transaction isolation level to SERIALIZABLE to see this in action:

SET TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * 
FROM DEPARTMENT 
WHERE id = 5;
COMMIT;

This time, it locks the single row id#5 till we end the transaction, even though we are only running a simple SELECT statement.

8. Conclusion

In this article, we learned how we can lock a single row of data in MySQL when using InnoDB. We don’t run any explicit LOCK statements to lock a single row. Accordingly, we can perform locking reads on a single row of data. Further, we can run UPDATE, DELETE, and INSERT statements to update, delete, and insert a single row, respectively. The common condition to lock only a single row is to use a unique index with a unique row.

As always, the SQL queries used in this article are available over on GitHub.

Full Archive

About Baeldung