How to Lock a Row for SELECT in MySQL?

Quick summary ↬ In database management, ensuring the integrity of data during concurrent operations is a common challenge. While row-level locking in MySQL is often associated with UPDATEs, there are scenarios where even a SELECT operation requires locking to prevent data inconsistencies. This is especially important in high-concurrency environments like banking, inventory management, or billing systems.
How to Lock a Row for SELECT in MySQL?
Posted , by Denis Sokol

When exploring row-level locking in MySQL, most examples focus on use cases for updates, deletes, or inserts, leaving the question of how to lock rows specifically for SELECT queries less addressed. However, locking for SELECT can be equally important in scenarios involving asynchronous operations.

Imagine a billing system where multiple processes access the same account balance. If one process reads outdated data while another modifies it, subsequent transactions might use inconsistent information, leading to errors or even data corruption. By locking rows during a SELECT, you can ensure that the next transaction reads the correct and updated balance, maintaining data consistency in a high-concurrency environment.

Understanding how to achieve this is critical for developers working with systems that demand precision in data handling, such as financial platforms, inventory management tools, or real-time monitoring systems. In this blog, we’ll dive into the mechanics and explain how to implement row-level locks for SELECT operations effectively.

Use Case: Billing System Challenge

At iSocket Systems, a challenge was encountered while working on the billing system, where the user balance needed to be decremented during asynchronous operations, such as sending SMS notifications through high-speed queues. In these scenarios, it was necessary to read the balance first to verify if sufficient funds were available, which clearly required a read lock. Another similar situation involved API rate limit counters, where the counter needed to be checked before incrementing to ensure it hadn’t already been exceeded. Both use cases highlight the need for proper handling of SELECT locks to maintain data consistency during concurrent operations.

Documentation Is Key, Or Not?

The answer to how to lock rows for SELECT operations can indeed be found in the MySQL documentation. For example, here it states:

SELECT ... FOR UPDATE

Other transactions are blocked from […] reading the data in certain transaction isolation level.

From this, it’s clear that we can lock even read operations if you use SELECT ... FOR UPDATE, but  not SELECT ... FOR SHARE. However, the documentation provides limited detail beyond this point, and much of the information is ambiguous. This makes it challenging to fully understand how and when to apply such locks effectively in real-world scenarios. The documentation says about “certain transaction isolation level”.

This brings us to the following documentation, which outlines isolation levels, but it doesn’t provide a direct answer to the question of which isolation level, combined with SELECT FOR UPDATE, will actually lock a SELECT statement. One might assume it should be REPEATABLE READ, but if you carefully consider the description, it talks about "Consistent reads within the same transaction," which doesn’t directly address our use case. The SERIALIZABLE isolation level is only briefly mentioned, essentially as an extension of REPEATABLE READ, intended for automatic interpretation of SELECT into SELECT FOR SHARE, which doesn’t fit our requirements. As you can see, the documentation is ambiguous and unclear. So, let’s move on to the tests instead.

Testing the Locking Mechanism

For the tests, I created a simple bash script that you can use in any environment, regardless of your frameworks. All you need to do is set up a table in your database. And just a heads-up, we’re working with InnoDB here! If you're using MyISAM, this won't work.

#!/bin/bash

# https://www.denis.es/blog/how-to-lock-row-for-select-in-mysql/


DB_HOST="10.0.0.5"
DB_PORT="3306"
DB_USER="lock_test"
DB_PASSWORD="And yes, I know it is insecure"
DB_NAME="lock_test"
TABLE_NAME="transaction_test"

ISOLATION_LEVEL="REPEATABLE READ"  # READ COMMITTED, REPEATABLE READ, SERIALIZABLE

LOCK_WAIT_TIME=20

mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD -e "
USE $DB_NAME;
SET TRANSACTION ISOLATION LEVEL $ISOLATION_LEVEL;
START TRANSACTION;
SELECT * FROM $TABLE_NAME WHERE id = 1 FOR UPDATE;
#SELECT * FROM $TABLE_NAME WHERE id = 1;

SELECT SLEEP($LOCK_WAIT_TIME);  # This will hold the lock for LOCK_WAIT_TIME
COMMIT;
"

Create a simple table:

CREATE TABLE `lock_test` (
  `id` bigint UNSIGNED NOT NULL,
  `user_id` int UNSIGNED DEFAULT NULL,
  `balance` decimal(16,9) NOT NULL DEFAULT '0.000000000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;


INSERT INTO `lock_test` (`id`, `user_id`, `balance`) VALUES
(1, 1, '40.589483350');

Run the script with the proper permissions and access, first in one terminal and immediately in a second terminal, and you’ll witness "magic" in action. While the first terminal is executing, the second will be waiting. And it's not waiting for the update; it's actually waiting for the SELECT query to complete!

I won’t be showing screenshots or videos here, but I encourage you to try it on your own. Experiment with changing isolation levels – you’ll see that with any of the three options provided, the behavior remains the same: the SELECT query is locked!

What if You Just Need to Read in Parallel Without Locking?

Typically, users want to see their balance on a dashboard, which is a simple SELECT operation that doesn't modify the balance. If it's not critical for the user to see the most up-to-date balance (i.e., a few seconds or milliseconds behind while processing a pending deduction), you can perform a regular SELECT, even if another operation is running with SELECT FOR UPDATE.

To test this, make a copy of the script: in one, uncomment the SELECT FOR UPDATE line and in the other, leave it as a regular SELECT. Run the first script in one terminal, and the second with a plain SELECT in another terminal. You’ll notice that the second terminal can immediately read, even while the transaction is still executing.

Handling Database Queries with ORM Libraries: Sequelize and Query Builders

In modern frameworks, manual SQL queries are often not necessary. Instead, libraries like Sequelize (for Node.js) or various Query Builders are commonly used to manage interactions with the database. These tools simplify database queries by allowing developers to work with JavaScript objects, rather than writing raw SQL. 

In this case, you need to identify the specific library or tool in your framework that will implement the functionality discussed in this post. For example, if you're working with Node.js, Sequelize will have the following construction for discussed purposes:

lock: t.LOCK.UPDATE

Phalcon's builder will have the following:

"for_update" => true

If you have any questions or would like to dive deeper into this topic, feel free to find this post on LinkedIn. We can continue the discussion and explore further solutions there!

Bonus

If you want to run several instances of the same script you can wrap them:

#!/bin/bash


INSTANCE_COUNT=10

for i in $(seq 1 $INSTANCE_COUNT); do
  /root/transaction_test.sh &
  echo "Instance $i run"
done
wait
echo "All instances done"

 

Posted