Re: Deadlock with 2 processes waiting on transactionids and lockingunrelated tables
Thanks for your response
Yes they will both have many reads and writes before running the deadlocking query.
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 ?
Cheers,
On 19 Feb 2018, at 4:43 pm, Rene Romero Benavides <rene.romero.b@gmail.com> 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 <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 по дате отправления:
Предыдущее
От: Rene Romero BenavidesДата:
Сообщение: Re: Deadlock with 2 processes waiting on transactionids and lockingunrelated tables
Следующее
От: Igal SapirДата:
Сообщение: Migrate2Postgres - A new tool for migration from other DBMSs