Hey everyone, i’m having a weird issue with one of my n8n workflows .
i am processing API data in batches and then doing an upsert into PostgreSQL. it works fine when i run a single execution , but when multiple executions overlap, i sometimes get the below
deadlock detected
SQLSTATE: 40P01
process 8421 waits for sharelock on transaction 987654;
blocked process by process 7312.
I’m also using Redis before the postgres node as a lock , but apparently that isn’t preventing the issue has anyone dealt with this before ?, i’ m not sure if the problem is my n8n workflow , the batching, or the postgres query
40P01 with a ShareLock wait is a lock ordering problem, not contention, so a Redis lock outside the database cannot fix it. The upsert operation builds one INSERT ... ON CONFLICT per item and Query Batching defaults to Single Query, so a batch becomes many statements in one transaction, and two executions touching the same keys in different orders deadlock.
Sort each batch by the conflict key before the Postgres node. Consistent lock order removes most of these outright.
Deduplicate keys within the batch.
Set On Error to Continue using error output and retry through a short Wait, since a deadlock victim is safe to retry.
Only if overlap is unacceptable, set workflow concurrency to 1 instead of coordinating in Redis.
If it persists, post your match-on columns and whether Query Batching is Single Query or Transaction, they fail differently.
@Kulang
But then , 40p01 is a PostgreSQL deadlock . it usually happens when two transactions are trying to lock related rows in different oders.
The Redis lock is worth checking too. Make sure it’s actually atomic and that every exection is using the same lock key .
i’d also try reducing the batch size and see whether the problem disappears
i checked it and the batches can actually contain records belonging to the same customer , so i guess two executions could be updating related rows a the same time .
One thing i’m still unsure about: should i retry the entire workflow when this happens, or just the Postgres part?
Just the Postgres node. The victim’s transaction is rolled back whole, so nothing upstream needs redoing and retrying the workflow would refetch the API for one failed batch. Turn on Retry On Fail there, capped at 5 tries and 5000ms apart.
On the lock, drop it. Redis cannot order what happens inside a Postgres transaction. Now that you know batches share a customer, sort each batch by customer id then by your conflict key before the Postgres node, so two executions take the same rows in the same sequence and cannot deadlock. If some batches truly must not interleave, use SELECT pg_advisory_xact_lock(hashtext($1)) with the customer id instead, since it releases with the transaction and a crash cannot strand it.
@Kulang
i wouldn’t retry the entire workflow , you can if you want to … i’d put th Postgres operation into a separate sub-worflow and retry that part only.
for Redis , use an atomic lock with a TTL rather than simply checking wether a key exist and then setting it.
One thing nobody has said yet, and it is the part that bit us: the fix you are
converging on can turn a loud failure into a silent one.
Retry On Fail plus On Error “continue using error output” is the right handling
for a deadlock victim, because the victim’s transaction is rolled back whole and
retrying is safe. But those two settings together also mean that when the retries
are exhausted, the execution still finishes green. The batch that never landed
leaves no failed execution behind — just a shorter row count in Postgres that
nobody is counting.
So it is worth wiring the exhausted branch to something, not to nothing. The
cheapest version is on the error output: a Postgres node that writes the customer
id, the batch key and the SQLSTATE into a small table, and an alert if that table
is non-empty at the end of the hour. Then a deadlock you did not defeat is a row
you can see rather than a gap you find weeks later.
Two smaller things on the same theme:
The advisory lock suggestion above (pg_advisory_xact_lock on the customer id) is
the one I would reach for before ordering, if the batches truly overlap per
customer, because sorting only guarantees the order inside a batch — two
executions can still interleave if one of them retries and re-enters with a
different set. The advisory lock is transaction scoped, so a crash releases it.
And if you do keep the Redis lock, check what happens when the TTL expires while
the Postgres transaction is still open. That path is where a lock stops being a
lock, and it fails quietly too.
@daniel_Samuel i changed the workflow like you suggested . i move postgres part into a separate sub-workflow and changed the Redis step to use SET NX with an expiry.
The deadlock error is happening much less now, but i noticed something strange. when two execution start at almost the same time , one gets the Redis lock and the other one gets: SET NX -false
But the second execution doesn’t seem to wait . it continues to the postgres node anyway.
sometimes i also see two execution updating the same customer at almost exactly the same time.
Am i misunderstanding how SET NX is supposed to work?
SET NX returning false means that the key already exists, so that execution didn’t acquire the lock i’d put an IF/Switch afterr the Redis node and only continue to Postgres when the lock was successfully acquired.
For the false branch , you could add a wait node and try again.
Also make sure the lock has a TTL so crashed execution doesn’t leave it stuck .
