Row-level locking (FOR UPDATE, FOR SHARE) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to lock selected rows for update in PostgreSQL.

PostgreSQL

SELECT * FROM orders WHERE status = 'pending' [1];

Drag options to blanks, or click blank then click option'

ALOCK IN SHARE MODE

BWITH LOCK

CFOR UPDATE

DFOR SHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR SHARE instead of FOR UPDATE when intending to lock for writing.

Using LOCK IN SHARE MODE which is MySQL syntax, not PostgreSQL.

The FOR UPDATE clause locks the selected rows so no other transaction can modify them until the current transaction ends.

2fill in blank

medium

Complete the code to lock rows for shared access in PostgreSQL.

PostgreSQL

SELECT * FROM products WHERE stock > 0 [1];

Drag options to blanks, or click blank then click option'

AFOR UPDATE

BFOR NO KEY UPDATE

CFOR KEY SHARE

DFOR SHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR UPDATE which locks rows exclusively.

Confusing FOR SHARE with FOR KEY SHARE.

The FOR SHARE clause locks rows for shared access, allowing concurrent reads but preventing updates.

3fill in blank

hard

Fix the error in the code to lock rows for update correctly.

PostgreSQL

SELECT * FROM customers WHERE active = true [1];

Drag options to blanks, or click blank then click option'

AFOR UPDATE SKIP LOCKED

BFOR UPDATE NOWAIT

CFOR UPDATE WAIT

DFOR SHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR UPDATE WAIT which is not valid syntax.

Using FOR SHARE which does not lock rows for update.

FOR UPDATE SKIP LOCKED locks rows for update but skips rows already locked by other transactions, avoiding waiting.

4fill in blank

hard

Fill both blanks to lock rows for update but avoid waiting if locked.

PostgreSQL

SELECT * FROM invoices WHERE paid = false [1] [2];

Drag options to blanks, or click blank then click option'

AFOR UPDATE

BNOWAIT

CSKIP LOCKED

DFOR SHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR SHARE instead of FOR UPDATE.

Using NOWAIT instead of SKIP LOCKED when wanting to skip locked rows.

Use FOR UPDATE SKIP LOCKED to lock rows for update and skip those already locked by others.

5fill in blank

hard

Fill all three blanks to lock rows for key share and avoid waiting in PostgreSQL.

PostgreSQL

SELECT * FROM sessions WHERE active = true [1] [2] [3];

Drag options to blanks, or click blank then click option'

AFOR KEY SHARE

BNOWAIT

CSKIP LOCKED

DFOR UPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing FOR UPDATE with key share locking.

Using NOWAIT and SKIP LOCKED together incorrectly.

FOR KEY SHARE NOWAIT SKIP LOCKED locks rows with key share, avoids waiting, and skips locked rows.