Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

David Wheeler <david@xxxxxxxxxxx> · Mon, 19 Feb 2018 16:58:33 +1100

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

David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing
p +61 3 9663 3554

On 19 Feb 2018, at 4:43 pm, Rene Romero Benavides <rene.romero.b@xxxxxxxxx> 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@xxxxxxxxxxx>:
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

<PastedGraphic-4.tiff>

David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing
p +61 3 9663 3554

<PastedGraphic-2.tiff><PastedGraphic-1.tiff><PastedGraphic-3.tiff>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/