3

We have a web application that sends emails with a queue worker. Before an email is sent we write to a table:

INSERT INTO user_email (user_id, email_key, sent_at) VALUES (
    175413579283955991, 
    'reminder-2017-07-06', 
    '2017-07-06T14:35:56.329185'::timestamp
) RETURNING user_email.id

The table definition looks like this:

CREATE TABLE user_email (
    id bigserial primary key,
    user_id bigint references "user"(id),
    key varchar(64),
    sent_at timestamp without time zone,
    UNIQUE (user_id, key)
);

We've started seeing writes to this table take a very long time, it appears that SELECT statements to the user table are blocking it:

 locked_item      | transactionid
 waiting_duration | 00:00:22.516621
 blocked_pid      | 7409
 blocked_query    | INSERT INTO user_email (user_id, key, sent_at) VALUES (175413579283955991, 'reminder-2017-07-06', '2017-07-06T14:35:56.329185'::timestamp) RETURNING user_email.id
 blocked_mode     | ShareLock
 blocking_pid     | 4345
 blocking_query   | SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name, "user".title AS user_title, ... FROM "user" WHERE "user".id = 683220705055081414
 blocking_mode    | ExclusiveLock

We're at a loss on why a SELECT statement is causing an ExclusiveLock, any insights on what we should look into?

2
  • 1
    you'll have to paste the output of TABLE pg_stat_activity, all of it, when the block occurs. Commented Jul 6, 2017 at 15:44
  • 3
    Have a look at the handy "blocking_tree" query at wiki.postgresql.org/wiki/Lock_dependency_information, it gives a helpful output of blockers and blockees.
    – bma
    Commented Jul 6, 2017 at 17:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.