I have a route (in a node JS app) that inserts and updates some data in a PostgreSQL database (version 13). In pseudo-code, here are all queries that are done in sequential order: On some instances of the app without that much traffic that writes on their own table, I have many deadlocks. I don't understand why since there is

PostgreSQL: deadlock without a transaction

I have a route (in a node JS app) that inserts and updates some data in a PostgreSQL database (version 13).

In pseudo-code, here are all queries that are done in sequential order:

select * from x where y=$1;

-- if there is no result
   insert into x (y,z) values ($1,$2) returning *;

-- else if there is a result
   insert into x (y,z) values ($1,$2) returning *; -- values are different
   update x set y=$1 where z=$2 returning *;
   update x set a=$1 where b=$2 returning *;

-- end if

On some instances of the app without that much traffic that writes on their own table, I have many deadlocks. I don’t understand why since there is no transaction, only simple inserts, and in some cases update. I use prepared statements (to avoid SQL injections) thanks to node postgres.

I never had deadlocks before (or maybe I did not notice them), so I don’t understand why it can happened.

What could be the reason why there are deadlocks? And how to avoid them?

EDIT

Logs from the server (I don’t have more detailed logs since it’s on a platform as a service):

2021-04-07T11:55:17+02:00 Process 583773 waits for ShareLock on transaction 2408877; blocked by process 583789.
2021-04-07T11:55:17+02:00 2021-04-07 09:55:17.084 GMT [583789] DETAIL: Process 583789 waits for ShareLock on transaction 2408880; blocked by process 583773.
2021-04-07T11:55:17+02:00 2021-04-07 09:55:17.084 GMT [583789] ERROR: deadlock detected
2021-04-07T11:55:17+02:00 2021-04-07 09:55:17.084 GMT [583789] STATEMENT: update x set user_id=$1, user_properties=$2 where user_id=$3
2021-04-07T11:55:17+02:00 2021-04-07 09:55:17.084 GMT [583789] HINT: See server log for query details.
2021-04-07T11:55:17+02:00 2021-04-07 09:55:17.084 GMT [583789] CONTEXT: while rechecking updated tuple (119,3) in relation "x"
2021-04-07T11:55:17+02:00 Process 583773: update x set user_id=$1, user_properties=$2 where user_id=$3
2021-04-07T11:55:17+02:00 Process 583789: update x set user_id=$1, user_properties=$2 where user_id=$3

Answer

In PostgreSQL all data modification happens in a transaction. Even if it is only a single-statement transaction, there is still a transaction.

The log entry is not enough to give a definitive answer, but it sure looks like your updates are updating more than one row each. If they occasionally update the same rows but in different orders, they can dead lock against each other. I would think that that should probably be rare for the queries in your log, as I would think they would choose rows to update based on single-valued scan of the same index and so generally do it in the same order.

EDIT

Advertisement

Answer