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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.