SELECT FOR UPDATE

Lock rows for update.

SELECT FOR UPDATE

SELECT FOR UPDATE reads and locks a row. Others cannot change it until the transaction ends.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

A safe withdrawal.

SELECT FOR UPDATE — details

FOR UPDATE locks the read row. The lock stays until COMMIT or ROLLBACK.

For example, in a stock check you write SELECT stock FROM products WHERE id = 1 FOR UPDATE; nobody else can change that row until the transaction ends.

Exercise SELECT FOR UPDATE

Lock and update a row with SELECT ... FOR UPDATE.

Show example solution

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Test yourself

When does a FOR UPDATE lock end?
- SELECT'te
- COMMIT'te
- Never
- UPDATE'te
Show answer

COMMIT'te — A FOR UPDATE lock ends at COMMIT.

Add FOR UPDATE

SELECT * FROM accounts WHERE id = 1 ____

Show answer

SELECT * FROM accounts WHERE id = 1 FOR UPDATE

SELECT FOR UPDATE: match the terms

FOR UPDATE

lock + read
BEGIN

start
COMMIT

release lock
safe

consistent

Next lesson: Lock timeout

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies