CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Tomasz
Tomasz

Reputation: 155

PostgreSQL - FOR UPDATE SKIP LOCKED deadlock

I have a parallel process that is using queue table in PostgreSQL. Logic is:

  1. Begin transaction.
  2. Mark 100 unprocessed records with some random generated ID.
  3. Commit.
  4. Run some heavy app logic that takes some time and is processing queue records with generated ID in step 2.
  5. Update 100 processed records with success/bad status.

Up to 20 threads are doing those steps. However, sometimes when I'm trying to do 2 step with query:

UPDATE QUEUE_TABLE 
SET QUEUE_TXN_GUID=$RANDOM_GUID,
QUEUE_STATUS=1 
WHERE QUEUE_ROW_GUID IN 
  (SELECT QUEUE_ROW_GUID from QUEUE_TABLE 
   WHERE QUEUE_STATUS IS NULL OR QUEUE_STATUS = -1 
   LIMIT 100 FOR UPDATE SKIP LOCKED) RETURNING QUEUE_ROW_GUID

I got error deadlock detected.

Query that I'm using in step 5 is UPDATE QUEUE_TABLE SET CDC_QUEUE_REZ_STATUS=$STATUS WHERE CDC_QUEUE_REZ_TXN_GUID=$RANDOM_GUID;

I don't know why I'm getting this strange deadlock, with FOR UPDATE SKIP LOCKED in first update subquery.

Upvotes: 2

Views: 3775

Answers (1)

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15861

The reason of the issue is the fact that there are duplicates in QUEUE_ROW_GUID. Select locks some rows but then query updates not those rows that were locked. That's why concurrently running query may try to update the same rows as this one. So the SKIP LOCKED does not work in this case.

Given that update of rows may happen in different order the first query (that tries to update say row 1 and row 2) may first update row 1 and then try to update row 2 but waits on lock. Concurrently running query (that tries to update 1 and 2 as well) already updated row 2 and waits for lock for row 1. Hence the deadlock.

You need to use unique identifiers to update rows after they are locked.

Upvotes: 2

Related Questions