How to Avoid Gap Locks in MySQL

Gap locks are a MySQL behavior many teams discover only after they cause production issues. They enforce REPEATABLE READ isolation in InnoDB but can create unexpected contention in simple queries.

For example:

SELECT * FROM orders WHERE id > 100 FOR UPDATE;

This locks not only the matching rows but also the gaps between them. Concurrent inserts into id > 100 can be blocked even if they do not touch the same rows. Systems slow down because of gap locks, not actual conflicts.

A few ways to avoid gap locks:

• Choose the right isolation level. Most OLTP workloads do not need REPEATABLE READ. Switching to READ COMMITTED avoids most gap locks without sacrificing correctness.
• Index carefully. Missing or misaligned indexes widen the locked range. Adding a proper covering index narrows locks and reduces contention.
• Prefer equality over ranges. = and IN predicates avoid gap locks, while <, >, and BETWEEN often trigger them. Restructure queries to target exact keys when possible.
• Keep transactions short. Gap locks persist until commit. Releasing them faster lowers the chance of blocking concurrent writers.
• Use escape hatches cautiously. Options like innodb_gap_lock_off disable gap locks entirely but introduce replication and consistency risks. Use them only if you fully understand the trade-offs.

In MySQL 8.0, gap locks are less aggressive on unique indexes, but range predicates can still trigger them, so these strategies remain relevant.

At Readyset, we think a lot about these subtle database behaviors. Scalability is not just about throughput. It is about predictability. Reducing surprises like unexpected locks keeps systems running smoothly.

🔍 What We're Watching in the Database Ecosystem

How Agentic AI Will Change Database Management

A thoughtful piece on what happens when AI agents, not humans, generate most of the database traffic. The article dives into automation, autonomy, and what it means for DBAs and infra teams.

Neon Postgres Extension Analytics

Neon put together a slick preview of what extensions their customers install the most. A nice reminder of just how powerful Postgres's extension ecosystem has become for delivering real-world functionality.

MariaDB Acquires SkySQL

MariaDB's reacquisition of SkySQL underscores a clear strategic direction: the future of databases is cloud-first, flexible, and AI-driven. By bringing SkySQL back in-house, MariaDB is signaling a stronger commitment to managed services and serverless capabilities as the dominant model for how enterprises will consume databases going forward.

In Case You Missed It

New on our Blog

Behind the Magic: How Readyset Speeds Up Queries with Streaming Dataflow

A behind-the-scenes look at how Readyset's streaming dataflow engine automatically keeps cached queries fresh without the invalidation complexity of traditional caching systems.

Upcoming Events

PGDay Austria

We're excited to share that Tanmay and I will be presenting two different talks at PGDay Austria: "Scaling Reads in PostgreSQL with Readyset" and "Data Gravity is Real: But You Can Bend It".

Outside of the talks, you'll find them at the Readyset booth. Stop by to talk shop, swap ideas, ask about our bingo activities to win prizes, or just say hi.