Skip to content

storage: Insert from temporary tables in consistent order

vlorentz requested to merge generated-differential-D8873-source into generated-differential-D8873-target

This avoids having a transaction inserting row A then B, while another inserts row B then A; which (probably) leads to deadlocks like this:

DeadlockDetected: deadlock detected
DETAIL:  Process 1842336 waits for ShareLock on transaction 1051957280; blocked by process 64261.
Process 64261 waits for ShareLock on transaction 1051957281; blocked by process 1842336.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (1972253,5) in relation "origin_extrinsic_metadata"
SQL statement "insert into origin_extrinsic_metadata (id, metadata, indexer_configuration_id, from_remd_id, metadata_tsvector, mappings)

https://sentry.softwareheritage.org/share/issue/52b06caae89f4235a758887fd6817656/

This was already mitigating by sorting before inserting in temporary tables, then expecting postgresql to read from temporary tables in the same order rows where inserted. This is often true, but not guaranteed.

Resolves #4696 (closed).

Test Plan

No test for this, because I do not see a way to replicate this more than existing deadlock tests do.

Migrated from D8873 (view on Phabricator)

Merge request reports