When to Use SELECT FOR UPDATE

Row-level locking is often dismissed as a performance killer, but when used strategically, SELECT FOR UPDATE can actually improve system reliability and throughput. By locking rows as they are read, you prevent competing transactions from interfering with each other and reduce costly retries, which are often performed by applications.

Where it shines:

• Prevent lost updates: Lock inventory rows so only one buyer decrements stock, eliminating race conditions.
• Scale workers safely: Combine FOR UPDATE SKIP LOCKED (MySQL 8.0.1+ / PostgreSQL 9.5+) with a queue table to let many consumers process tasks in parallel without collisions.
• Enforce invariants: Ensure atomic changes in scenarios such as bank transfers or ticket reservations.
• Backstop optimistic concurrency: Use FOR UPDATE on retries to serialize conflicting writes and guarantee a correct final state.

Best practices:

• Keep transactions short.
• Only lock rows you plan to update.
• Acquire locks in a consistent order to avoid deadlocks.
• Monitor for hot rows. Frequent locks on the duplicate rows can serialize traffic and become a bottleneck.

Done right, row-level locking does more than preserve correctness; it streamlines concurrency and turns potential contention into predictable, scalable workflows. Ultimately, fewer locks result in improved performance.

🔍 What We're Watching in the Database Ecosystem

Ensuring Relational Database Performance at Scale: Challenges and Strategies

At scale, relational databases not only become slower but also harder to reason about. This post breaks down common strategies, such as sharding and replication, but it also serves as a reminder that adding more hardware is not a long-term solution. True scalability stems from reducing query pressure at the source, where caching and query optimization excel.

SQL Agents Need Compilers

Most LLM-powered SQL agents fail in production because they are treated as language models instead of as systems. This piece argues that reliability comes from treating them like compilers, enforcing constraints around correctness, performance, and access control. The result is more predictable behavior and SQL generation that can scale safely.

Announcing MySQL AI

Oracle is integrating AI-native capabilities directly into MySQL, featuring built-in vector search, RAG, and model integration within HeatWave. It is an early signal of where the industry is headed: pushing intelligence closer to the data layer and increasing the importance of performance, efficiency, and scalability upstream.

In Case You Missed It

Customer Interview With Ótima Digital

Thiago, DBA, and Henrique, Tech Lead, from Ótima Digital share how they brought runaway read traffic under control, stabilized their databases, and reclaimed engineering bandwidth, all without touching their application code. They walk through deploying Readyset and explain why QueryPilot became a pivotal part of their stack.

Watch the full conversation on YouTube.