SELECT FOR UPDATE in SQL: How Row Locking Works

Key Takeaways

SELECT FOR UPDATE locks (or acquires write intent on) selected rows to reduce conflicting writes during a transaction.
It’s commonly used in high-concurrency OLTP databases to coordinate access to shared data and simplify correct application logic.
Proper use reduces race conditions but requires care to avoid excessive contention or deadlocks.

CockroachDB What Is Select for Update blog4 1WWEBP

Relational databases are great for transactional workloads. But things can get messy when multiple transactions start trying to access the same data at the same time. Luckily, in many SQL databases there’s a solution for that: SELECT FOR UPDATE.

What is SELECT FOR UPDATE in SQL?

SELECT FOR UPDATE is a SQL command that’s useful in the context of transactional workloads. It allows you to “lock” the rows returned by a SELECT query until the entire transaction that query is part of has been committed. Other transactions attempting to access those rows are placed into a time-based queue to wait, and are executed chronologically after the first transaction is completed.

This is useful because it prevents the thrashing and unnecessary transaction retries that would otherwise occur when multiple transactions are attempting to read those same rows. Any time multiple transactions are likely to be working with the same rows at roughly the same time, SELECT FOR UPDATE can be used to increase throughput and decrease tail latency (compared to what you would see without using it).

In other words: SELECT FOR UPDATE makes contended transactions process more smoothly (which generally also means they process more quickly and efficiently).

When should you use SELECT FOR UPDATE?

In general, SELECT FOR UPDATE is useful for any transactional workload where multiple transactions might attempt to access the same rows at the same time.

However, different ‘flavors’ of SQL handle SELECT FOR UPDATE somewhat differently, and some do not support it at all. For example, MySQL supports SELECT FOR UPDATE, but SQLite does not.

Often, the reason for this is related to the way different systems handle transactions more broadly. For example, SQLite does not need SELECT FOR UPDATE because initiating a transaction locks the entire database. This allows SQLite databases to offer serializable transactional isolation, the highest level of isolation, for ironclad consistency. However, locking the entire database during a transaction that might only affect a few rows has obvious negative implications for performance, particularly at scale.

However, choosing between performance and consistency is not necessary! CockroachDB, for example, offers serializable isolation, but it does not have to lock the entire database at the beginning of a transaction to make that happen. SELECT FOR UPDATE can be used to maximize database performance in the event of concurrent transactions working on the same rows, and the end result (in the case of CockroachDB) is still a database with serializable isolation.

Different SQL databases handle transaction isolation, and thus SELECT FOR UPDATE, differently, so it’s important to be familiar with the options and defaults for the system you’re using.

How does SELECT FOR UPDATE work in action?

Let’s take a look at how SELECT FOR UPDATE works. We’ll use CockroachDB syntax and parameters here, but the syntax for other SQL databases that support SELECT FOR UPDATE will be similar.

Imagine we’re working with a database that includes the following table kv:

A complete transaction that uses SELECT FOR UPDATE on that table could look like this:

BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;
UPDATE kv SET v = v + 5 WHERE k = 1;
COMMIT;

Working line by line through the statement above:

The first line, BEGIN, initiates the transaction.
The second line, the SELECT statement, identifies the rows that will be impacted and locks them. In this case, it’s a single row: the first row of the table.
The third line makes an update to the row in question. In this case, adding 5 to the value in the v column of the row.
The fourth line, COMMIT; commits the transaction.

If we were to run this transaction on our example table, the result would be this:

What’s important here, though, is that the SELECT … FOR UPDATE line (line 2) locked the row that the transaction updated. If another transaction (let’s call it Tx 2) hit the database attempting to operate on the same row while this transaction (Tx 1) was processing, Tx 2 would be added to the queue for processing after Tx 1 commits, rather than beginning to execute, failing, and having to retry because Tx 1 changed one of the values Tx 2 was accessing while it was processing.

Try CockroachDB Today

Spin up your first CockroachDB Cloud cluster in minutes. Start with $400 in free credits. Or get a free 30-day trial of CockroachDB Enterprise on self-hosted environments.

What parameters does SELECT FOR UPDATE support?

Again, different database systems allow for different parameters and modifications of SELECT statements.

A common one, for example, is SELECT … FOR SHARE, which provides a weaker form of row-locking in some database systems. In PostgreSQL, SELECT … FOR UPDATE completely locks the relevant rows, whereas SELECT … FOR SHARE locks the relevant rows only for updates and deletes.

In contrast, because CockroachDB always guarantees serializable isolation and there are no “weaker” locking levels, SELECT … FOR SHARE functions identically to SELECT … FOR UPDATE. The FOR SHARE syntax is supported only for Postgres compatibility.

Another common parameter is NOWAIT, which returns an error immediately if a transaction is not able to immediately lock a row. In SQL syntax, NOWAIT appears directly after FOR UPDATE, like so:

SELECT * FROM kv WHERE k = 1 FOR UPDATE NOWAIT;

SKIP LOCKED is also a parameter supported by some databases that allows waiting transactions to skip locked rows temporarily so that the “hold” on those rows doesn’t slow the processing of elements of the transaction impacting non-locked rows. CockroachDB doesn’t currently support this, in part because it uses multiversion concurrency control. This forum thread has some excellent information for achieving the same goals without using SKIP LOCKED.

What is SELECT FOR UPDATE? SELECT FOR UPDATE is a SQL clause used within a database transaction to acquire exclusive locks on the rows returned by the SELECT query. This mechanism prevents other transactions from modifying, deleting, or acquiring conflicting locks on those specific rows until the current transaction is either committed or rolled back.

Does SELECT FOR UPDATE lock entire tables? No. SELECT FOR UPDATE locks only the selected rows, though broad queries can effectively block many concurrent transactions.

What happens if another transaction tries to access locked rows? In CockroachDB, when a transaction attempts to access rows locked by SELECT FOR UPDATE, the outcome (wait, retry, or fail) depends on the transaction’s isolation level, priority, and query parameters. Under the default SERIALIZABLE isolation, a conflicting transaction will typically wait for the lock to be released, helping prevent the “thrashing” that can occur when transactions repeatedly conflict and restart.

What does the NOWAIT parameter do with SELECT FOR UPDATE? The NOWAIT parameter causes the query to return an error immediately if the rows cannot be locked, rather than waiting.

What is the difference between SELECT FOR UPDATE and SELECT FOR SHARE? SELECT FOR UPDATE and SELECT FOR SHARE apply different lock strengths to selected rows, with the former acquiring an exclusive lock that prevents conflicting reads and writes, while the latter allows shared locking reads. Their exact behavior can vary across database systems. In CockroachDB, which guarantees SERIALIZABLE isolation by default, both SELECT FOR UPDATE and SELECT FOR SHARE behave the same in practice, and the FOR SHARE syntax is maintained primarily for PostgreSQL.

Can SELECT FOR UPDATE cause performance issues? Yes. Long-running transactions and high contention can reduce concurrency and increase latency if locks are held too long.

Can SELECT FOR UPDATE improve performance in transactional workloads? Yes. SELECT FOR UPDATE can improve performance in some high-contention workloads by reducing unnecessary retries and contention, though retries may still occur and should be handled by the application.

How does CockroachDB handle SELECT FOR UPDATE safely at scale? CockroachDB handles SELECT FOR UPDATE safely at scale by leveraging its default SERIALIZABLE isolation level and an underlying distributed concurrency control mechanism, which includes Multi-Version Concurrency Control (MVCC) and optional durable, replicated locks. This approach prioritizes correctness while minimizing performance bottlenecks.

Charlie Custer is a former teacher, tech journalist, and filmmaker who’s now combined those three professions into writing and making videos about databases and application development (and occasionally messing with NLP and Python to create weird things in his spare time).

sql

distributed sql

Data 101