-- SELECT FOR UPDATE: locks specific rows for update
BEGIN;
SELECT * FROM inventory
WHERE product_id = 42 AND quantity >= 5
FOR UPDATE;  -- locks this row

-- Now other transactions that try to lock/update this row will wait
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 42;
COMMIT;
-- Lock released

-- SELECT FOR UPDATE SKIP LOCKED: don't wait, skip locked rows
-- Useful for job queues
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- If another worker grabbed it, we skip it and get the next one

Transactions — ACID Properties

Transactions — ACID Properties

Transactions — ACID Properties