Advisory Locks or SKIP LOCKED: A Debate for Agent SQL Workers

The following reconstruction is a composite queue-worker failure, not a report from a named production tenant. A checkout worker queue stalled after midnight when three agent-written jobs targeted the same unpaid-order slice. Each session selected candidate rows without a skip clause, then waited on locks that never yielded under replica lag. The parser accepted every statement, so the usual syntax gate never fired during the pre-promotion review.

The on-call thread split into two durable camps with different promotion rules for the same queue table. One camp wanted named advisory locks so overlapping agents would wait inside a single critical section. The other camp wanted SKIP LOCKED so workers would skip held rows and keep draining the backlog. Both positions are credible in PostgreSQL; they encode opposite answers to whether waiting is cheaper than progress.

Why agent-written SQL changes the locking debate

Agent-written SQL often looks locally correct while ignoring the other sessions that will run the same text. Tool-calling agents emit SELECT ... FOR UPDATE because that pattern appears in runbooks, reviews, and a large amount of training data. Concurrent copies of that pattern convert a queue into a convoy, especially when the agent also omits a session timeout. The operational question is not whether locking is required; it is which lock semantic an agent is allowed to promote.

Database-native evidence is stronger than a model confidence score for this class of defect. PostgreSQL documents session and transaction advisory locks in the explicit locking chapter, and it documents SKIP LOCKED as a row wait policy on SELECT. Those are primary behaviors, not folklore, and they fail in different ways when agents reuse them blindly. A promotion rule that cannot name one semantic is not a rule.

Position A: transaction-scoped advisory locks

Advocates treat pg_advisory_xact_lock as a named mutex the agent must request before touching business rows. The lock key becomes an explicit critical section, which is easy to grep in review and easy to wrap in one transaction. When the transaction ends, PostgreSQL releases the lock without a separate unlock call, which reduces leaked-lock incidents from forgotten cleanup paths. That property matters when the authoring agent is better at opening work than at writing ROLLBACK handlers.

This position is strongest when overlap is a correctness bug rather than a throughput nuisance. Nightly close, singleton reconciliation, and “only one agent may rewrite this partition” jobs match that shape. The cost is wait time: other agents block until the lock holder commits or rolls back, so a slow holder becomes a pause for every peer that uses the same key. Agents that hash unstable strings into the key recreate that pause under a new name.

The rehearsal below is labeled sample SQL and is not an executed production result:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';
SELECT pg_advisory_xact_lock(hashtext('agent-job:billing-close'));
-- agent DML is inserted only after the lock is granted
ROLLBACK; -- rehearsal only; replace with COMMIT after sign-off

hashtext is a convenience, not a cryptographic guarantee, and collisions remain possible across poorly chosen names. Teams that take this path should keep a registry of lock keys beside the agent prompt. Unknown keys should fail closed, because an agent that invents a mutex is not locking the resource the reviewer had in mind.

Position B: FOR UPDATE SKIP LOCKED

The other camp argues that queue workers should never wait on a sibling row. FOR UPDATE SKIP LOCKED lets a session take the next unlocked row that matches the predicate, which keeps throughput alive when several agents drain one table. Reviewers can cite the SELECT reference instead of a remembered blog post, which is the kind of evidence this debate should prefer. Waiting is treated as a failure mode, not as a fairness feature.

This position is strongest when skipping a locked row is acceptable and duplicate processing is prevented by the row lock plus a status column. It is weaker when the business requires strict serial order, because skipped rows can be processed later by another worker. Agents also emit SKIP LOCKED without a supporting LIMIT, which can lock a much larger set than the author intended. A skip clause without a bound is still a wide lock.

BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '10s';
WITH next_job AS (
  SELECT id
  FROM agent_jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE agent_jobs AS j
SET status = 'running',
    locked_at = now()
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.status;
ROLLBACK; -- rehearsal only

A missing WHERE status = 'queued' remains catastrophic even with SKIP LOCKED in the statement. The skip clause does not replace predicate hygiene, a row limit, or a timeout. If the agent cannot state which rows it is willing to skip, the text is not a queue drain.

Evidence that should change the default

Advisory locks do not conflict with ordinary row locks, which surprises agents that treat “lock” as one global concept. Two sessions can hold a row lock and an advisory lock independently, so mixing both without a diagram creates false confidence during review. SKIP LOCKED never waits, which means a monitoring query that expects a blocked wait_event can report a healthy worker that is skipping the entire backlog. Absence of blocking is not proof of progress.

pg_locks and pg_stat_activity remain the ground truth during rehearsal, and an agent summary that says “looks safe” is not evidence. Capture wait_event_type, granted, and lock type before anyone promotes the text. If rehearsal cannot show either a granted advisory lock or a skipped-row update, the candidate statement is not ready.

SELECT pid, wait_event_type, wait_event, state, left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

SELECT locktype, mode, granted, pid, relation::regclass
FROM pg_locks
WHERE database = (SELECT oid FROM pg_database WHERE datname = current_database());

Run those two queries from a third session while the candidate SQL is still inside an open transaction. Evidence collected after COMMIT is a postmortem, not a rehearsal. Agents that cannot keep the transaction open long enough for this inspection are not ready for promotion either.

A numbered rehearsal workflow

Use this sequence on a disposable database that already contains a realistic volume of queued rows. Do not point the authoring agent at a primary that lacks timeouts, and do not treat a dry parse as a concurrency test.

1. Freeze the candidate statement as a file, including a comment that names the intended lock semantic in one word: exclude or skip.
2. Reject any text that mixes pg_advisory_xact_lock and FOR UPDATE SKIP LOCKED in one transaction without a written split into two jobs.
3. Require SET LOCAL statement_timeout and SET LOCAL lock_timeout in the same transaction as the lock or the UPDATE.
4. Execute the file under BEGIN, inspect pg_locks, then force ROLLBACK and record rows touched.
5. Repeat the same file from two concurrent psql sessions and capture whether the second session blocked or progressed.
6. Promote only when the observed overlap matches the semantic named in step 1, not the comment the agent generated after the fact.

Commands for the concurrent check, labeled as a local rehearsal sketch:

# terminal A
psql -v ON_ERROR_STOP=1 -f rehearsal_worker.sql

# terminal B, started within one second
psql -v ON_ERROR_STOP=1 -f rehearsal_worker.sql

Keep both invocations on the same frozen file when testing exclusion and when testing skip behavior. Agents sometimes emit “unique” advisory keys that still hash together, and that collision only appears when two sessions use the same text. If the second session blocks on a queue drain, the semantic is wrong even if the SQL is pretty.

Where a second-pass model review fits

Static gates catch missing timeouts; they do not catch a wrong lock semantic chosen with fluent comments. A useful extra step is to send the frozen SQL, the intended semantic, and the lock-key registry to a review model that is not the authoring model. The review prompt should ask for a verdict of exclude, skip, or reject-and-split, plus a PostgreSQL reason, and it should refuse to rewrite the query.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

MonkeyCode is an open-source project with free model access and a free server option, which is enough to host that second pass without folding it into the authoring loop. Treat it as a review sidecar: the model should not receive production row data, and it should not execute SQL. If the sidecar disagrees with concurrent psql evidence, the database evidence wins and the statement stays blocked. The sidecar is optional for teams that already freeze SQL and run two-person review; it is not optional for teams that let an authoring agent promote its own text.

Decision table

Observed shape	Prefer advisory xact lock	Prefer SKIP LOCKED	Reject promotion
Singleton job where overlap is a correctness bug	Yes	No	Mixing both semantics
Queue drain where progress under contention matters	No	Yes, with LIMIT	SKIP LOCKED without LIMIT
Strict FIFO business order	Yes, or a single worker	No	SKIP LOCKED claiming FIFO
Unknown lock key or missing timeout GUC	No	No	Yes
DDL, LOCK TABLE, or cross-database lock claims	No	No	Yes

The table is a promotion filter, not a style guide. Rows in the reject column are not “needs a better comment.” They are not shippable until the agent emits a different statement, usually two statements with two jobs.

Decision rule

Choose advisory locks when overlap is a correctness failure and waiting is cheaper than double-application of the same job. Choose SKIP LOCKED when the table is a queue, a LIMIT is present, and skipped rows may legally be processed later. Reject promotion when the agent mixes both semantics, omits timeouts, or cannot show concurrent rehearsal evidence from two sessions.

If two concurrent rehearsals block on a queue drain, the text is using the wrong semantic for that table. If two concurrent rehearsals both mutate a singleton close job, the text is also using the wrong semantic. The rule is the observed overlap, not the comment the agent wrote above the query, and not a model’s claim that the SQL is idiomatic.

Limitations and who should not use this

This debate assumes one PostgreSQL cluster and transaction-scoped behavior as documented in the explicit locking chapter. Advisory locks are not distributed locks for multiple engines, and they do not survive a jump to a different database. SKIP LOCKED does not repair a predicate that matches too much, and it does not provide fairness among workers. Neither option is a substitute for a status column, a unique job key, or a frozen statement file.

Do not apply this workflow on a production primary without a staging clone and session timeouts. Do not use it for DDL, LISTEN/NOTIFY design, or ORM-generated SQL you cannot freeze as text. Do not treat a model review as a substitute for ROLLBACK rehearsal. Multi-master or sharded topologies need a different lock story than a single pg_locks view can provide, and this article does not offer one.

The method also fails closed on purpose. Agents that cannot name a semantic do not receive a default lock. A silent default is how the midnight convoy started, and it will start again the first time two copies of the same tool call overlap.

Lock comments in agent SQL are cheap; concurrent evidence is not. Freeze the statement, pick one semantic, and make two sessions prove it before promotion. If you already run that second-pass review on spare hardware, MonkeyCode’s free server option is one place to keep the sidecar off the authoring path.