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

2018-02-19 Thread David Wheeler 
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


 

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


  
 
 
> On 19 Feb 2018, at 5:54 pm, Rene Romero Benavides  
> 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  >:
> 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

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

2018-02-19 Thread Rene Romero Benavides 
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 :

> 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
> <(407)%20872-4272>; blocked by process 7186.
> Process 7186 waits for ShareLock on transaction 4078724210
> <(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 <(408)%20978-5154> |
> 56/2306863 | 41715 | ExclusiveLock| |
>|
>  relation  ||   | 56/2306863 | 41715 |
> RowExclusiveLock | ticket_fromuid_idx  |  |
> (19 rows)
>
> 
> 
> 
>
> after running

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

2018-02-19 Thread David Wheeler 
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  
> 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  >:
> 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  |

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

2018-02-18 Thread Rene Romero Benavides 
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 :

> 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
> <(407)%20872-4272>; blocked by process 7186.
> Process 7186 waits for ShareLock on transaction 4078724210
> <(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 <(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, virtualtra