Advisory Locking vs. Unique Constraints for Retry-safe Writes in Perl DBI

Goal: guarantee that a write operation retried after a transient error does not create duplicate rows in the database when using Perl’s DBI.

Constraints: Advisory locks (e.g., DBI->do('SELECT pg_advisory_lock(...)')) serialize concurrent retries and prevent duplicates but can cause deadlocks if a lock is held too long or not released promptly. Unique constraints (e.g., a UNIQUE index or PRIMARY KEY) let the database reject duplicate inserts, shifting duplicate‑write detection to error handling, which may add overhead on every retry and requires graceful handling of duplicate‑key exceptions.

Uncertainty: It is unclear whether advisory locking or unique constraints provides a better trade‑off between deadlock risk and error‑handling overhead for retry‑safe writes, especially when the retry logic is implemented with DBIx::Retry or a manual eval‑based loop.

Which approach reduces deadlock likelihood under high concurrency? Which approach minimizes the performance impact of handling duplicate‑key errors? Are there workloads where combining both mechanisms is advisable?