Planned lessonMID-015-L03

Row-level locks

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

lock rows explicitly when needed.

SELECT ... FOR UPDATE locks the selected rows until the transaction ends; others wait on writes or on their own FOR UPDATE.
FOR SHARE blocks writers but allows other readers with FOR SHARE.
NOWAIT fails immediately; SKIP LOCKED skips locked rows.
Prefer an atomic UPDATE ... SET qty = qty - 1 WHERE qty > 0 when it is enough: no lock dance and no round trip.
Keep locking transactions short and lock rows in a consistent order.

Draft outline details

sql

BEGIN;
SELECT qty FROM stock WHERE product_id = 7 FOR UPDATE;
UPDATE stock SET qty = qty - 1 WHERE product_id = 7;
COMMIT;

What happens to a second FOR UPDATE on the same row?

Show answer

It waits until the first transaction ends.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.