2025-03-27 PostgreSQL deadlock solution

Scenario: Task Queue Processing

Multiple workers need to process tasks from a pending_tasks table without conflicts.

1. Table Setup

​x
1
sql
2
CREATE TABLE pending_tasks (
3
    task_id SERIAL PRIMARY KEY,
4
    payload JSONB,
5
    status VARCHAR(20) DEFAULT 'pending',
6
    claimed_by UUID
7
);
8

9
-- Insert sample tasks
10
INSERT INTO pending_tasks (payload)
11
VALUES 
12
    ('{"type": "email"}'),
13
    ('{"type": "report"}'),
14
    ('{"type": "cleanup"}');

2. Worker 1 (Session A)

Must use

ORDER BY task_id

and

FOR UPDATE SKIP LOCKED

x
1
sql
2
BEGIN;
3

4
-- Claim first available task that's not locked
5
SELECT * FROM pending_tasks
6
WHERE status = 'pending'
7
ORDER BY task_id
8
FOR UPDATE SKIP LOCKED
9
LIMIT 1;
10

11
-- Returns: task_id=1 (oldest pending task)
12
-- Locks row 1 exclusively

3. Worker 2 (Session B)

xxxxxxxxxx
12
1
sql
2
BEGIN;
3

4
-- Same query in concurrent session skips locked row
5
SELECT * FROM pending_tasks
6
WHERE status = 'pending'
7
ORDER BY task_id
8
FOR UPDATE SKIP LOCKED
9
LIMIT 1;
10

11
-- Returns: task_id=2 (next available task)
12
-- Never sees task_id=1 because it's locked by Worker 1

4. Update and Commit

Add commit after every update

Worker 1:

xxxxxxxxxx
7
1
sql
2
UPDATE pending_tasks 
3
SET status = 'processing', 
4
    claimed_by = 'worker-uuid-1'
5
WHERE task_id = 1;
6

7
COMMIT; -- Releases lock

Worker 2:

xxxxxxxxxx
7
1
sql
2
UPDATE pending_tasks 
3
SET status = 'processing', 
4
    claimed_by = 'worker-uuid-2'
5
WHERE task_id = 2;
6

7
COMMIT;

Key Features Demonstrated

1. Non-blocking: Workers don't wait for each other's locks

2. Fair ordering: ORDER BY task_id ensures oldest tasks are processed first

3. Atomic claims: Each task is guaranteed to be processed by only one worker

Does not work with GROUP BY - Alternative

To use FOR UPDATE SKIP LOCKED with GROUP BY in PostgreSQL, you cannot directly combine these clauses because GROUP BY requires aggregate functions and FOR UPDATE locks individual rows, not grouped results37. However, there are workarounds:

Workaround Using Advisory Locks

For scenarios requiring exclusive access to grouped data (e.g., all jobs for a specific person_id), use advisory locks to coordinate at the application level:

xxxxxxxxxx
8
1
sql
2
BEGIN;
3
SELECT * FROM jobs 
4
WHERE status = 'new' 
5
  AND pg_try_advisory_xact_lock(person_id) = true 
6
LIMIT 1 FOR UPDATE SKIP LOCKED;
7
-- Process jobs for this person_id
8
COMMIT;

How it works:

1. pg_try_advisory_xact_lock(person_id) attempts to lock the person_id without waiting3.

2. If the lock succeeds (true), the worker processes all jobs for that person_id.

3. If the lock fails (false), the worker skips this person_id and continues to the next available one.

Limitations of FOR UPDATE SKIP LOCKED

• Row-level locking: SKIP LOCKED skips individual locked rows, not grouped results25.

• Inconsistent views: Designed for queue-like systems where consistency isn’t critical2.

• No batch locking: Cannot lock all rows for a group in a single operation3.

Alternative Approach

If you need to process all jobs for a group (e.g., all pending jobs for a person_id), use a subquery to first identify the group, then lock its rows:

xxxxxxxxxx
12
1
sql
2
BEGIN;
3
WITH target_person AS (
4
  SELECT person_id FROM jobs 
5
  WHERE status = 'new' 
6
  LIMIT 1
7
)
8
SELECT * FROM jobs 
9
WHERE person_id = (SELECT person_id FROM target_person)
10
FOR UPDATE SKIP LOCKED;
11
-- Process all jobs for this person_id
12
COMMIT;