Thanks for your response
> Does any of the two tables have triggers?
Yes the ticket table has a trigger that inserts changes into a ticketstatuslog
table when the ticket.status column changes and on insert.
ticket_status_insert_trigger AFTER INSERT ON ticket FOR EACH ROW EXECUTE
PROCEDURE ticket_status_trigger_function()
ticket_status_update_trigger AFTER UPDATE OF ticketstatus ON ticket FOR
EACH ROW WHEN (old.ticketstatus <> new.ticketstatus) EXECUTE PROCEDURE
ticket_status_trigger_function()
> What's the database / transaction isolation level?
Both read committed
> Do the updates run in a transaction among other read / write operations
> within the same transaction ?
Yes they will both have many reads and writes before running the deadlocking
query.
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 4:43 pm, Rene Romero Benavides <[email protected]>
> wrote:
>
> Hi. Does any of the two tables have triggers? What's the database /
> transaction isolation level? Do the updates run in a transaction among other
> read / write operations within the same transaction ?
> Regards.
>
> 2018-02-18 23:28 GMT-06:00 David Wheeler <[email protected]
> <mailto:[email protected]>>:
> 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/>
>
