Re: Deadlock with 2 processes waiting on transactionids and lockingunrelated tables
Oh I see. Yeah it’s kind of obvious now that you point it out!
Btw, do the transactions use explicit locking?
We occasionally use for update so that could be involved. We’ll have a closer look at the code involved.
I’m still curious about why the locks are both transaction locks rather than one of them being a row lock.
Thanks for your help!
On 19 Feb 2018, at 5:54 pm, Rene Romero Benavides <rene.romero.b@gmail.com> wrote:My guess is that the transaction doing:update "planscheduleitem" set "planschedule"=$1 where "psi"=$2updates ticket before reaching that pointAndupdate ticket set unread = true where ticketid = $1updates planscheduleitem before thatDoes it make sense to you? Btw, do the transactions use explicit locking?2018-02-18 23:28 GMT-06:00 David Wheeler <david@inomial.com>: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 = $1Process 7186: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2How 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 belowselect 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"=$2SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode, relname, page, tupleFROM 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 = $1SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode, relname, page, tupleFROM 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
<PastedGraphic-4.tiff>
David Wheeler • software engineer
<PastedGraphic-2.tiff><PastedGraphic-1.tiff><PastedGraphic-3.tiff>--
Вложения
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6
Следующее
От: Michael PaquierДата:
Сообщение: Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6