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.
Asked
Active
Viewed 994 times
1 Answers
0
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.
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
-
I am using INSERT ... ON CONFLICT DO UPDATE ... Should sorting help in this case? – user3621726 Aug 07 '19 at 01:43
-
@user3621726: Let me phrase it this way: `ON CONFLICT DO UPDATE` certainly does not defend against deadlocks from multiple concurrent writes. Sorting *may* very well be the solution. Especially since you mention "batch updates". Make sure these batches are sorted consistently. Follow the first link. I added one more related link. – Erwin Brandstetter Aug 07 '19 at 01:50