Hi,
We’re seeing deadlock semi-regularly (0-2 per day) that I’m really having
trouble getting to the bottom of.
Process 7172 waits for ShareLock on transaction 4078724272; blocked by process
7186.
Process 7186 waits for ShareLock on transaction 4078724210; blocked by process
7172.
The two queries in question are updates on unrelated tables. Running the
queries on their own shows no overlapping entries in pg_locks.
Process 7172: update ticket set unread = true where ticketid = $1
Process 7186: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
How can I work out why Postgres has decided that the two processes are in
deadlock? Is there an explainer somewhere on transaction level locks? I can’t
see anything in the docs besides that they exist.
Details below
select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.2 (Ubuntu 10.2-1.pgdg14.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
(1 row)
-------------------------------------------------------------------------------------------------------------------------------------------------------
after running update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode,
relname, page, tuple
FROM pg_locks LEFT JOIN pg_class ON (relation=oid) where pid =41715;
locktype | virtualxid | transactionid | virtualtransaction | pid |
mode | relname | page | tuple
---------------+------------+---------------+--------------------+-------+------------------+-------------------------+------+-------
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_parentticketid | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_originalticketid | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_tickettypeid_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_subject_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_closedtime_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_assignedto_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_serviceuid_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_parentuid_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_createdtime_idx | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_txid | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_tickettype | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_ticketpriority | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_idx_0 | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_pkey | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | number_constraint | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket | |
virtualxid | 56/2306863 | | 56/2306863 | 41715 |
ExclusiveLock | | |
transactionid | | 4089785154 | 56/2306863 | 41715 |
ExclusiveLock | | |
relation | | | 56/2306863 | 41715 |
RowExclusiveLock | ticket_fromuid_idx | |
(19 rows)
--------------------------------------------------------------------------------------------------------------------------------------------------------
after running update ticket set unread = true where ticketid = $1
SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode,
relname, page, tuple
FROM pg_locks LEFT JOIN pg_class ON (relation=oid) where pid =41715;
locktype | virtualxid | transactionid | virtualtransaction | pid |
mode | relname | page | tuple
---------------+------------+---------------+--------------------+-------+------------------+--------------------------------------+------+-------
relation | | | 56/2306861 | 41715 |
RowExclusiveLock | planscheduleitem_plan_company_idx | |
relation | | | 56/2306861 | 41715 |
RowExclusiveLock | psi_uid_startdate | |
relation | | | 56/2306861 | 41715 |
RowExclusiveLock | psi_planschedule_startdate_starttime | |
relation | | | 56/2306861 | 41715 |
RowExclusiveLock | planscheduleitem_pkey | |
relation | | | 56/2306861 | 41715 |
RowExclusiveLock | planscheduleitem | |
virtualxid | 56/2306861 | | 56/2306861 | 41715 |
ExclusiveLock | | |
transactionid | | 4089783283 | 56/2306861 | 41715 |
ExclusiveLock | | |
(7 rows)
TIA
Cheers,
-- David
<http://www.inomial.com/>
<http://www.inomial.com/>David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing <http://www.inomial.com/>
p +61 3 9663 3554
<http://www.linkedin.com/company/inomial-pty-ltd>
<https://www.facebook.com/Inomial> <https://twitter.com/inomial>
<https://twitter.com/inomial>
