How to handle deadlocks while multiple batch inserts?

Question

I need suggestions/ideas of handling a issue with deadlocks. I'm having multiple batch inserts commands to same table:

insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, $6,$7,$8), ($9, $10, $11, $12, $13, $14, $15, $16), ($17, $18, $19, $20, $21, $22, $23, $24), ($25, $26, $27, $28, $29, $30, $31 ,$32), ($33, $34, $35, $36, $37, $38,$39,540), ($41, $42, $43,$44,545, $46, $47,$48) ... ON CONFLICT DO NOTHING

And since Insertion order is not honored in database most probably because PostgreSQL is launching several parallel insert threads, I'm getting the following deadlocks:

[1111]:ERROR:deadlock detected 
[1111]:DETAIL: Process 1111 waits for ShareLock on transaction 1423145219; blocked by process 2222.
Process 1111 waits for ShareLock on transaction 1423145222: blocked by process 1111.
Process 1111: insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, $6,$7,$8), ($9, $10, $11, $12, $13, $14, $15, $16), ($17, $18, $19, $20, $21, $22, $23, $24), ($25, $26, $27, $28, $29, $30, $31 ,$32), ($33, $34, $35, $36, $37, $38,$39,540), ($41, $42, $43,$44,545, $46, $47,$48) ... ON CONFLICT DO NOTHING
Process 2222: insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, $6,$7,$8), ($9, $10, $11, $12, $13, $14, $15, $16), ($17, $18, $19, $20, $21, $22, $23, $24), ($25, $26, $27, $28, $29, $30, $31 ,$32), ($33, $34, $35, $36, $37, $38,$39,540), ($41, $42, $43,$44,545, $46, $47,$48) ... ON CONFLICT DO NOTHING

As we can see the bulk inserts acquire ShareLocks which deadlock each other.

On the one hand I don't want to acquire an exclusive lock for each bulk insert on the entire table because I guess it will affect the parallel inserts performance.

On the other hand I'm not 100% sure that enforcing order (by id column which is a PK with a CTE select) is a good solution, because even if I could guarantee insertion order that would NOT ensure a later select statement would return them in that order since it's multiple bulk insertions, and the order might change.

(And also I have to reorder the table for each bulk insert).

Answer 1

The best solution would be to make sure that no two of the concurrent statements try to insert rows with the same primary or unique key. And no, ON CONFLICT doesn't do that trick.

The second best solution is to sort the rows inserted by a single statement before inserting them:

WITH r (id, p_id, m_type, m_id, sha256, path) AS
(
   VALUES
      ($1, $2,  $3,  $4,  $5,  $6,  $7,  $8),
      ($9, $10, $11, $12, $13, $14, $15, $16),
      ...
)
INSERT INTO ip (id, p_id, m_type, m_id, sha256, path)
SELECT * FROM r
ORDER BY r.id;

That should only cause live locks, but no deadlocks.

Of course there are other causes for deadlocks like triggers, but you didn't mention any such details.