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

0

Your Answer

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