CODEWITHSUNDEEP
sqlpostgresql
mdev
mdev

Reputation: 1416

Postgres : Deadlock with 2 update queries

I am trying to wrap my head around with this deadlock issue in our production, and now i really need some help.

PostgreSQL 9.5.10

Query 1: (Updating 1000 records)

update entitlements.stream_general sg
set stream_offset_id =nextval( 'entitlements.stream_general_stream_offset_id_seq' ),
should_update_offset_id = false
from (select id, topic, stream_id from entitlements.stream_general where should_update_offset_id = true limit 1000) sg2
where sg.id=sg2.id and sg.topic=sg2.topic and sg.stream_id = sg2.stream_id

Query 2: (Updating a single record)

update entitlements.stream_general set stream_action = $1::entitlements.stream_action_type, update_dt = now(), should_update_offset_id = true where stream_id = $2 and topic = $3 and id = $4

Exception :

Process 60563 waits for ShareLock on transaction 3603536083; blocked by process 60701. Process 60701 waits for ShareLock on transaction 3603536039; blocked by process 60563.

Since there are only two transactions involved in the deadlock processes, how can one Update can be in deadlock with another Update. According to my understanding, after first Update there will be RowExclusiveLock on all those rows, and second Update should get blocked. How can there be a DEADLOCK?

stream_general table schema :

CREATE TABLE entitlements.stream_general (
    stream_id int4 NOT NULL,
    id varchar NOT NULL,
    topic varchar NOT NULL,
    stream_offset_id int8 NOT NULL DEFAULT '-1'::integer,
    create_dt timestamptz NOT NULL DEFAULT now(),
    update_dt timestamptz NOT NULL DEFAULT now(),
    stream_action stream_action_type NOT NULL,
    should_update_offset_id bool NULL,
    PRIMARY KEY (stream_id, topic, id),
    FOREIGN KEY (stream_id) REFERENCES entitlements.stream(stream_id) ON DELETE CASCADE
)
WITH (
    OIDS=FALSE
) ;
CREATE INDEX stream_general_id_idx ON entitlements.stream_general USING btree (id, topic) ;
CREATE INDEX stream_general_should_update_offset_id_index ON entitlements.stream_general USING btree (should_update_offset_id) ;
CREATE INDEX stream_general_stream_id_idx ON entitlements.stream_general USING btree (stream_id, topic, stream_offset_id) ;

Note : stream_id is the foreign key.

Only culprit i can think of is subquery in Query1, but how i am not able to figure out how that Select can be problematic. Or may be something is up with foreign constraints.

Upvotes: 1

Views: 536

Answers (1)

Andomar
Andomar

Reputation: 238078

The first query is first taking a read lock before taking a write lock. Could that cause a deadlock when the second query waits for a write lock too?

Try for update to have the subquery acquire a write lock?

update  ...
from    (
        select  ...
        from    ...
        FOR UPDATE
        ) sg2

Upvotes: 1

Related Questions