FOR UPDATE vs FOR NO KEY UPDATE

2025-07-31

Locks are important in databases when working with concurrent applications. In RDBMS, FOR UPDATE and FOR NO KEY UPDATE are one of the ways to acquire exclusive lock on rows. Both of them are having their own performance implications. Let's understand them below.

FOR UPDATE & FOR NO KEY UPDATE

BEGIN;
 
SELECT * FROM <table>
WHERE id = 802
FOR UPDATE
 
--- OR
SELECT * FROM <table>
WHERE id = 802
FOR NO KEY UPDATE
 
COMMIT;

FOR UPDATE & FOR NO KEY UPDATE clause locks the rows selected by the query for any update. Once lock is acquired it, selected rows can be modified by only the current transaction holding the lock. Any transaction trying to update the same row, will wait for current transaction to complete.

What's the difference?

To understand the difference, let's take an example of basic chat app which have conversations and messages within those conversations.

CREATE TABLE conversation(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(255),
    message_count INT DEFAULT 0
);
 
CREATE TABLE message(
    id INT,
    conversation_id INT NOT NULL REFERENCES conversation(id),
    text VARCHAR(1024) NOT NULL,
    PRIMARY KEY (conversation_id,id)
);

Our job is to insert few messages into message table increment the message_count in the same transaction, to keep them in sync.

BEGIN;
SELECT * FROM conversation WHERE id=1 FOR UPDATE;
--- insert few message into messages table
--- update message_count by the number of messages inserted
COMMIT;

Any other transaction will be blocked, trying to insert messages for the same conversation because of FOR UPDATE.

To insert messages into table, DB needs to acquire locks on conversation table to maintain foreign key integreatly.

As you can see in the below images, FOR UPDATE on conversation and INSERT into message are trying to acquire the same lock on conversation and conversation_pkey.

W3Worker Logo W3Worker Logo

In real world application, this is unacceptable. Multiple transactions could try to insert records for the same conversation in 100s of table within databases. All of them will be blocked. Users may face high response time and lags. Gateway may timeout. And many others...

The solution is to use FOR NO KEY UPDATE which acquires weaker lock than FOR UPDATE.

In simple terms, FOR NO KEY UPDATE tells the database to allow inserts because foreign key will remain intact.

FOR NO KEY UPDATE behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock

Since, it does not blocks inserts into child tables. It won't create unnecessary delays in transaction execution, no more waiting, lag will lower in high concurrent applications.

LinkedIn