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! 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> > On 19 Feb 2018, at 5:54 pm, Rene Romero Benavides <rene.romer...@gmail.com> > wrote: > > My guess is that the transaction doing: > > update "planscheduleitem" set "planschedule"=$1 where "psi"=$2 > > updates ticket before reaching that point > > And > > update ticket set unread = true where ticketid = $1 > > updates planscheduleitem before that > > Does it make sense to you? Btw, do the transactions use explicit locking? > > 2018-02-18 23:28 GMT-06:00 David Wheeler <da...@inomial.com > <mailto:da...@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 > <tel:(407)%20872-4272>; blocked by process 7186. > Process 7186 waits for ShareLock on transaction 4078724210 > <tel:(407)%20872-4210>; 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 <tel:(408)%20978-5154> | > 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 <tel:(408)%20978-3283> | > 56/2306861 | 41715 | ExclusiveLock | > | | > (7 rows) > > TIA > > Cheers, > > -- David > > > <PastedGraphic-4.tiff> <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 <tel:+61%203%209663%203554> > > > <PastedGraphic-2.tiff> > <http://www.linkedin.com/company/inomial-pty-ltd><PastedGraphic-1.tiff> > <https://www.facebook.com/Inomial><PastedGraphic-3.tiff> > <https://twitter.com/inomial> <https://twitter.com/inomial> > > > > -- > El genio es 1% inspiración y 99% transpiración. > Thomas Alva Edison > http://pglearn.blogspot.mx/ <http://pglearn.blogspot.mx/> >
