Pessimistic vs optimistic locking: how do SELECT ... FOR UPDATE, NOWAIT and SKIP LOCKED work?
Pessimistic vs optimistic locking: how do SELECT ... FOR UPDATE, NOWAIT and SKIP LOCKED work?
Both prevent two transactions from corrupting the same data. Pessimistic locking blocks others up front; optimistic locking lets everyone proceed and detects conflicts at write time.
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 7 FOR UPDATE; -- others wait for this row
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT;
Modifiers: FOR UPDATE NOWAIT fails immediately if the row is locked; FOR UPDATE SKIP LOCKED ignores locked rows. These are available in PostgreSQL, MySQL 8.0+ and Oracle. SQL Server uses hints: WITH (UPDLOCK, ROWLOCK, READPAST).
Many workers can pull different jobs without blocking each other:
WITH next_job AS (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next_job WHERE jobs.id = next_job.id
RETURNING jobs.*;
This is a good fit for work queues; it is deliberately not a consistent view of "all rows", so do not use it for reports.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.