Resolving deadlock: concurrent transactions calling stored function inserting/updating a table

Question

I am running into a DEADLOCK situation when multiple concurrent transactions are calling a stored function which inserts or updates a table. Each transaction executes a batch update, using Postgres 10.

Answer 1

Typically, forcing a consistent order of rows in all INSERT, UPDATE and DELETE commands across all concurrent transactions avoids deadlocks altogether.

Function or no function, that typically does not have any bearing on deadlocks. The more locks your transactions acquire over their lifetime and the longer they take, the more likely deadlocks become. "Batch updates" are a prime suspect here. May rows that need to be locked for the UPDATE ...

Related:

• Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
• How to simulate deadlock in PostgreSQL?
• How to use RETURNING with ON CONFLICT in PostgreSQL?

Details of the solution depend on the undisclosed details of your problem.