MySQL SELECT FOR UPDATE: Complete Guide to Row Locks

1. Introduction

MySQL is a relational database management system widely used around the world, but among its features, techniques for ensuring ‘data integrity’ and preventing ‘conflicts from concurrent updates’ are extremely important. In particular, in scenarios where multiple users or systems operate on the same data simultaneously, failing to implement appropriate exclusive control can lead to unexpected malfunctions or data corruption.

One of the primary methods to address these challenges is ‘SELECT … FOR UPDATE’. This is a MySQL syntax for applying a lock (exclusive control) to specific rows, and it’s commonly used in practical business scenarios such as ‘simultaneously reducing inventory’ or ‘issuing serial numbers without duplication’.

In this article, we will explain ‘SELECT … FOR UPDATE’ from the basics to practical usage, precautions, and advanced application examples, including specific sample code, in an easy-to-understand way.
If you want to operate your database safely and efficiently, or if you’re interested in best practices for exclusive control, please read to the end.

2. Basics and Prerequisites of SELECT FOR UPDATE

“SELECT … FOR UPDATE” is a syntax in MySQL for placing an exclusive lock on specific data rows. It is mainly used when multiple processes or users might edit the same data simultaneously. Here, we explain the basic matters and prerequisites that you should understand to use this feature safely.

First, as a major prerequisite, “SELECT … FOR UPDATE” is only effective within a transaction. In other words, you need to start a transaction with BEGIN or START TRANSACTION and execute it within that scope. Even if used outside a transaction, the lock will not function.

Furthermore, this syntax can only be used with the InnoDB storage engine. It is not supported in other engines such as MyISAM. InnoDB supports advanced features such as transactions and row-level locking, making exclusive control possible.

Additionally, you need appropriate privileges (usually SELECT and UPDATE privileges) for the target tables or rows of the SELECT. If you lack the privileges, you may not be able to acquire the lock or may encounter an error. Summary

• “SELECT … FOR UPDATE” is only effective within a transaction
• Targets InnoDB engine tables
• Appropriate privileges (SELECT and UPDATE) are required

If these prerequisites are not met, the row lock will not function as expected. First, make sure to understand this mechanism correctly before writing actual SQL statements.

3. How It Works and the Lock Mechanism

When using “SELECT … FOR UPDATE”, MySQL places an exclusive lock (X lock) on the target row. Rows with an exclusive lock cannot be updated or deleted by other transactions, preventing conflicts and inconsistencies. Here, we explain the operation overview and internal mechanism in an easy-to-understand way.

Basic Operation of Row Locks

The rows acquired with “SELECT … FOR UPDATE” are blocked from updates or deletions by other transactions until that transaction completes (commit or rollback). For example, when processing to decrease the stock quantity in a product table, if you lock the target row with “FOR UPDATE”, you can make other processes wait if they try to change the stock quantity simultaneously.

Relationship with Other Transactions

While the lock is in place, if other transactions attempt to update or delete the same row, those operations will wait until the lock is released. However, normal SELECT (read) operations can be executed without regard to the lock. In other words, the purpose of the lock is to “maintain data consistency” and “prevent write conflicts”.

About Gap Locks

In InnoDB, there is also a special lock called a “gap lock”. This is used to prevent new data from being inserted into that range when “the specified row is not found” or “when searching with range conditions”. For example, if you try to acquire data with id 5 using FOR UPDATE but it doesn’t exist, a lock is also placed on the empty area (gap) near that id, temporarily preventing other transactions from inserting new records into the same range.

Lock Granularity and Performance

Row locks are characterized by the ability to lock only the minimum necessary range. This allows maintaining data consistency without significantly degrading overall system performance. However, if there are complex search conditions or no indexes set, locks may unintentionally extend to a wide range, so caution is needed.

4. Choosing Between Options: NOWAIT and SKIP LOCKED

MySQL 8.0 and later allows you to use additional options such as NOWAIT and SKIP LOCKED with the “SELECT … FOR UPDATE” syntax. These options are used to flexibly control behavior when lock conflicts occur. We will explain the characteristics of each and how to choose between them.

NOWAIT Option

Specifying NOWAIT causes it to return an error immediately without waiting if another transaction has already locked the target row.
This behavior is effective in cases where you do not want any waiting time at all, for example, in systems that require fast responses or when you want to immediately retry failed items in batch processing.

SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

This SQL will immediately result in an error stating “cannot acquire lock” if the row with id = 1 is locked by another transaction.

SKIP LOCKED Option

SKIP LOCKED skips locked rows and acquires only unlocked rows.
It is mainly used for large-scale data processing or when you want to process queue-type tables simultaneously with multiple processes. This allows you to avoid touching data being processed by other transactions and efficiently process only the rows that can be handled.

SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

In this example, only the rows that are not locked among those with status = 'pending' are acquired. This enables efficient task processing across multiple processes.

Key Points for Choosing Between Them

• NOWAIT: For business logic where you want to quickly determine success or failure and avoid waiting.
• SKIP LOCKED: For cases where you want to distribute processing of large amounts of data across multiple processes or to process as quickly as possible while minimizing lock conflicts.

By selecting these options according to the situation and business requirements, you can achieve more efficient and flexible exclusive control.

5. Code Explanation with Practical Examples

In this section, we’ll explain the specific usage of “SELECT … FOR UPDATE” from simple examples to advanced examples commonly used in real-world business operations, incorporating actual SQL code.

Basic Usage

First, the basic pattern of “locking a specific row and updating it safely”.
For example, retrieve specific order information from the orders table and simultaneously lock that order row to prevent changes by other transactions.Example: Safely Changing the Status of a Specific Order

START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = 'processed' WHERE id = 1;
COMMIT;

In this flow, the order row with id = 1 is locked with “FOR UPDATE”, preventing other processes from updating the same row simultaneously. Until commit, other transactions will wait for updates or deletions on this row.

Advanced Example: Safe Issuance of Unique Counters

“SELECT … FOR UPDATE” is also very effective when you want to safely issue sequential numbers or serial numbers on the database.
For example, in the process of issuing member numbers or order numbers, it prevents conflicts when multiple processes acquire and update numbers simultaneously.Example: Issuing Serial Numbers Without Duplication

START TRANSACTION;
SELECT serial_no FROM serial_numbers WHERE type = 'member' FOR UPDATE;
UPDATE serial_numbers SET serial_no = serial_no + 1 WHERE type = 'member';
COMMIT;

In this example, the row with type = 'member' in the serial_numbers table is locked, the current serial number is retrieved and incremented, then committed. This allows safe issuance of non-duplicate sequential numbers even if multiple processes run simultaneously.

Reference: FOR UPDATE with JOIN

“FOR UPDATE” can also be used in combination with JOIN, but caution is needed as the lock may extend to unexpected ranges. Basically, when you want to lock only the rows of the table to be updated, it’s safer to use a simple SELECT to narrow down and lock only the necessary rows.

In this way, “SELECT … FOR UPDATE” can be utilized in various business logics, from simple data updates to practical serial number issuance. In actual system design, choose the usage that fits your needs.

6. Precautions and Measures for Gap Locks and Deadlocks

“SELECT … FOR UPDATE” is a very convenient means of exclusive control, but MySQL’s InnoDB engine also has unique behaviors and precautions such as gap locks and deadlocks. Here, we explain these mechanisms and measures to avoid problems in actual operations.

Behavior of Gap Locks and Precautions

Gap locks are a mechanism where, if a row does not exist in the specified search condition or in the case of a range search, a lock is also applied to that range (gap). For example, in SELECT * FROM users WHERE id = 10 FOR UPDATE;, if the row with id = 10 does not exist, a lock is applied to the gap before and after it, temporarily blocking new inserts (INSERT) by other transactions.

This gap lock can prevent problems such as duplicate registrations or violations of uniqueness, but on the other hand, it may cause side effects like “locks applied to a wider range than expected, causing INSERTs to wait.” It is particularly important to be cautious in systems that frequently manage sequential IDs or perform range searches.

Occurrence of Deadlocks and Measures

Deadlock is a state where multiple transactions are waiting for each other to release locks, causing the processing to never progress. In MySQL (InnoDB), when a deadlock is detected, one of the transactions is automatically rolled back, but the ideal is to design to avoid occurrences as much as possible.Main Deadlock Measures:

• Unify the Order of Lock Acquisition
  When locking multiple tables or rows simultaneously, aligning the access order across all processes can significantly reduce the risk of deadlocks.
• Keep Transactions Short
  Make the processing in a single transaction as compact as possible and avoid unnecessary waits.
• Be Cautious with Complex JOIN Syntax
  LEFT JOIN or locks on multiple tables may cause locks to spread to unexpected ranges. Aim for simple SQL structures, and describe lock-required processing separately for safety.

Risks of Using with JOIN Syntax

“SELECT … FOR UPDATE” used in combination with JOIN may cause locks to propagate beyond the main table. For example, using FOR UPDATE while JOINing orders and customers may lock rows in unintended ranges. Therefore, it is recommended to target only the tables to be locked and perform individual SELECTs.

In this way, MySQL’s lock control has unique pitfalls. During system development, correctly understand the mechanisms of gap locks and deadlocks, and aim for stable operations.

7. Comparison of Pessimistic Locking vs. Optimistic Locking

There are two main methods for exclusive control in databases: “pessimistic locking” and “optimistic locking.” “SELECT … FOR UPDATE” is a typical example of pessimistic locking, but in actual operations, it’s important to use them interchangeably with optimistic locking. This section explains their features and selection criteria.

What is Pessimistic Locking?

Pessimistic Locking (Pessimistic Lock) assumes that “other transactions will likely try to modify the same data” at the time of accessing the data, and it involves acquiring a lock in advance.
Using “SELECT … FOR UPDATE” applies a lock in the stage before data updates, preventing conflicts or inconsistencies from other transactions. It is effective in scenarios with high risk of conflicts or where data integrity must absolutely be maintained.Main Use Cases:

• Inventory management or balance processing
• Preventing duplicates in order numbers or serial numbers
• Systems where multiple people edit simultaneously

What is Optimistic Locking?

Optimistic Locking (Optimistic Lock) assumes that “conflicts rarely occur,” so it proceeds with processing without basically acquiring locks.
When actually updating, it checks the target data’s “version number” or “update timestamp,” and if no changes have been made, it writes directly. If the data has already been changed by another transaction, it results in an error.Main Use Cases:

• Systems with a lot of data reading and few simultaneous writes
• Applications where users operate relatively independently

Example Implementation of Optimistic Locking:

-- Remember the version when retrieving the data
SELECT id, value, version FROM items WHERE id = 1;

-- Overwrite if the version hasn't changed during update
UPDATE items SET value = 'new', version = version + 1 WHERE id = 1 AND version = 2;
-- If someone has already updated the version, this UPDATE will fail

Points for Choosing Between Them

• Pessimistic Locking: Use in business logic where conflicts frequently occur or integrity must be absolutely maintained.
• Optimistic Locking: Use when conflicts are rare, prioritizing performance.

In actual systems, it is common to use both depending on the importance of the processing or access patterns.
For example, use pessimistic locking for “order processing” or “inventory allocation,” and optimistic locking for “profile updates” or “settings changes,” which is an effective way to differentiate.

By understanding the differences between pessimistic and optimistic locking and selecting the appropriate exclusive control for the situation, safe and efficient database operations become possible.

8. Performance Considerations

“SELECT … FOR UPDATE” provides powerful exclusive locking, but depending on how it’s used, it can adversely affect the overall system performance. Here, we explain points to watch out for in actual operations and common pitfalls.

Table Locking When No Index Exists

“SELECT … FOR UPDATE” is basically row locking, but if no index is set on the search conditions or if the range is ambiguous, the entire table may be locked.
For example, if you use a column without an index in the WHERE clause or search with ambiguous conditions (such as LIKE patterns that are not left-anchored), MySQL cannot perform efficient row locking and may end up locking the entire table.

If this state continues, other transactions will be forced to wait longer than necessary, leading to decreased overall system responsiveness and increased deadlocks.

Be Careful with Prolonged Transactions

If a transaction continues for a long time while holding a lock with “SELECT … FOR UPDATE”, other users or systems will have to wait for the lock to be released during that time.
This is particularly prone to occur due to design flaws in the application (such as waiting for user input while holding the lock), which significantly impairs system performance.Main countermeasures:

• Minimize the lock range and targets (optimize search conditions, use indexes)
• Keep transaction processing as short as possible (perform user operation waits and unnecessary processing outside the transaction)
• Properly implement timeouts and exception handling to prevent unexpected long-term locks

Retry Processing Due to Lock Contention

In high-traffic systems or environments with many batch processes, errors or waits due to lock contention may occur frequently.
In such cases, consider retry processing when lock acquisition fails, as well as utilizing options like NOWAIT or SKIP LOCKED.

If considerations for performance are insufficient, even the exclusive control can become a cause of “processing delays” or “system-wide stagnation”. From the design stage, pay attention to lock behavior and performance, and aim for stable operations.

9. Frequently Asked Questions (FAQ)

Here, we summarize common questions and issues regarding “SELECT … FOR UPDATE” in an easy-to-understand Q&A format. Let’s cover points that are easy to stumble on in practice and points that are prone to misunderstanding.

Q1. During “SELECT … FOR UPDATE”, can other sessions SELECT the same row?

A. Yes, they can. The lock from “SELECT … FOR UPDATE” only applies to “update or delete” operations. Normal SELECT (read) operations are possible from other sessions, so if it’s just for reference, it won’t be blocked by the lock.

Q2. What happens if you try to acquire a non-existent row with “FOR UPDATE”?

A. In that case, a gap lock is applied to the search range (gap). This prevents INSERT (new registration) into the relevant range from other transactions. Be careful not to unintentionally block INSERTs.

Q3. Is it okay to use “FOR UPDATE” together with JOIN syntax like LEFT JOIN?

A. Basically, it is not recommended. Using JOIN can cause locks to extend to unintended multiple tables or a wide range. If you want to lock only the necessary tables and rows, acquire locks individually with simple SELECTs.

Q4. How should I differentiate between using NOWAIT and SKIP LOCKED?

A. NOWAIT immediately errors if the lock cannot be acquired, while SKIP LOCKED acquires only rows that are not locked. Depending on processing requirements, choose “NOWAIT if you don’t want to wait and need immediate determination” or “SKIP LOCKED if you want to distribute processing of large amounts of data”.

Q5. In what cases is optimistic locking more suitable?

A. Optimistic locking is effective in scenarios where conflicts rarely occur or where high-speed processing and high throughput are required. Use pessimistic locking (FOR UPDATE) in cases with many conflicts or where data integrity is absolutely necessary.

By utilizing the FAQ, you can anticipate and resolve readers’ questions in advance, enhancing the practicality and reliability of the entire article. Please refer to it during actual system design and troubleshooting as well.

10. Summary

“SELECT … FOR UPDATE” is a particularly powerful and flexible method among exclusive control mechanisms in MySQL. In systems where multiple users or processes handle the same data simultaneously, it can be said to be indispensable for maintaining data consistency and security.

Through this article, we have covered a wide range from basic usage to options and application examples, precautions such as gap locks and deadlocks, comparisons between pessimistic locking and optimistic locking, and performance issues. I hope you have gained knowledge that will be useful in actual on-site operations and troubleshooting.Reviewing the key points:

• “SELECT … FOR UPDATE” is only effective within a transaction
• Exclusive control via row locks prevents simultaneous updates and data conflicts
• Be cautious of MySQL-specific behaviors such as gap locks and wide-area locks during JOINs
• Appropriately utilize options such as NOWAIT and SKIP LOCKED
• Understand the differences between pessimistic locking and optimistic locking and use them according to the purpose
• Index design, transaction management, and considerations for performance are also important

“SELECT … FOR UPDATE” is convenient on one hand, but if you don’t properly understand its mechanisms and side effects, it can lead to unexpected problems. It’s important to always use it with awareness tailored to the design intent and operational policies.
In the future, those aiming for more advanced database operations or application development should definitely refer to this article and select the optimal exclusive control for your own system.