Скачать или смотреть Understanding MySQL Row Locking: How Many Rows Are Locked with SELECT .. FOR UPDATE LIMIT 1?

Explore the intricacies of MySQL row locking behavior when using `SELECT ... FOR UPDATE LIMIT 1`, and learn why multiple rows may be locked when you expect only one.
---
This video is based on the question https://stackoverflow.com/q/76098834/ asked by the user 'Alexis' ( https://stackoverflow.com/u/956974/ ) and on the answer https://stackoverflow.com/a/76099276/ provided by the user 'Ergest Basha' ( https://stackoverflow.com/u/16461952/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How many row should lock when using SELECT .. FOR UPDATE LIMIT 1

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding MySQL Row Locking: How Many Rows Are Locked with SELECT .. FOR UPDATE LIMIT 1?

When working with MySQL, particularly with the InnoDB storage engine, understanding how row locking functions is critical for building efficient and reliable applications. A common area of confusion involves the behavior of row locks when executing a SELECT statement with the FOR UPDATE clause in conjunction with a LIMIT. In this post, we’ll delve into a specific situation: How many rows should lock when using SELECT .. FOR UPDATE LIMIT 1?

The Problem at Hand

You have a table named Counts, indexed on the count column, and you want to retrieve only one row that meets certain criteria while ensuring that the retrieved row is locked for updates. The query in question looks like this:

[[See Video to Reveal this Text or Code Snippet]]

The goal is to skip any rows that are already locked, but upon testing, you notice that every row fitting the criteria is locked, not just the returned row. This raises an important question: why does this happen?

The Mechanics Behind Row Locking

InnoDB Row Locking Behavior

The InnoDB storage engine in MySQL has specific behaviors that dictate how locks are applied to rows in response to SQL queries:

Row Scanning: When executing a query with conditions (like WHERE count >= 4), InnoDB needs to scan the rows that meet these conditions regardless of the LIMIT. The initial expectation might be to lock only the rows that meet the LIMIT criteria, but in reality, more rows get locked.

Locking Strategy:

Using UNIQUE Index (Primary Key): If your query uses a unique index (like a Primary Key), only that specific row is locked.

Using a Non-Unique Index: If your query uses a non-unique index, every row that matches the criteria will be locked. This means if several rows have a count value of 4 or higher, all of those rows will be locked as InnoDB needs to ensure data integrity in a concurrent environment.

No Index Present: If there is no index on the column being queried, InnoDB has to scan the entire table, resulting in every row being locked.

Example Examination

Let's look deeper into the exam query. To break down why you're experiencing this issue, we can reference the EXPLAIN output you received, which showed that three rows were scanned when your query was executed. This provides a clear indication of how MySQL is processing your query:

[[See Video to Reveal this Text or Code Snippet]]

Output indicates:

Type: range

Rows: 3

The presence of the count_i index leads MySQL to scan three rows based on the criteria, locking all of them despite the LIMIT of one.

Switching to Primary Key Ordering

Your testing revealed a different outcome when you ordered the results by the Primary Key id:

[[See Video to Reveal this Text or Code Snippet]]

This resulted in only one row being locked because the query could effectively identify a single unique row without needing to lock others that satisfied the count condition.

Conclusion

In summary, when using SELECT .. FOR UPDATE LIMIT 1 in MySQL with an indexed column, it’s crucial to understand that the locking mechanism is driven by how many rows the database engine needs to examine before fulfilling the query output. InnoDB locks all rows that it scans to arrive at the desired result. To minimize locking, consider adjusting your usage of indexes or how you structure your queries, keeping in mind the specific requirements of your application.

Understanding these principles will not only improve the performance of your database queries but also enhance the integrity of concurrent transactions within your applications. Happy querying!