APScheduler's Advisory Lock Failure: My Solo VM's Scheduler Died Permanently

It started with a user report: "Content engine auto-publishing should put 3 posts on dev.to, but only 2 appeared, and then nothing worked." This is the kind of subtle bug that can fester, but the reality was far more systemic. My entire APScheduler setup had died. Not just for dev.to, but for *all* my scheduled tasks: content engine sweeps, daily top 3 analysis, profile analysis, model health checks, weekly reports – everything. The cron logs showed nothing for three days straight.

This wasn't just a hiccup; it was a full-blown scheduler apocalypse on my single small VM. The immediate symptom was a lack of new posts on dev.to, but the root cause was a complete, permanent scheduler failure.

The Wrong Turn: Relying on PostgreSQL Advisory Locks for Leader Election

My approach to ensuring only one instance of my worker process ran scheduled jobs involved using PostgreSQL's pg_try_advisory_lock. The idea was that each worker would try to acquire this advisory lock. The one that succeeded would be the leader, responsible for running the jobs. Other workers would see the lock is held and stand down.

However, in my specific environment – direct PostgreSQL connection (localhost:5432) without a connection pooler like pgbouncer, using asyncpg for dedicated connections – this mechanism proved fatally flawed. The lock was acquired, but immediately released. The worker thought it held the lock (`active=True`), but a check of pg_locks showed zero holders. This meant the singleton pattern was broken. Worse, the self-healing mechanism relied on the same flawed lock acquisition, meaning it couldn't recover.

The situation was so unstable that I even observed a period where both my blue and green services (running on ports 8000 and 8001 respectively) thought they were the leader, resulting in a double execution of jobs. This was a clear sign the leader election was fundamentally broken.

The Root Cause: Session State Unreliability

The core issue was the unreliability of the session state with PostgreSQL advisory locks in this particular setup. The lock was being held at the session level, and in an environment with dedicated connections and potential connection resets or pool management (even without pgbouncer, the underlying connection handling can be complex), this session state wasn't guaranteed. When the session state was lost or reset, the lock was implicitly released, but the application logic didn't know.

The symptom of active=True but pg_locks showing 0 holders was the smoking gun: the session was lying about holding the lock.

The Fix: A Robust Leader Election with a Simple Table

The fix involved completely abandoning the advisory lock mechanism. Instead, I implemented a leader election strategy based on a simple database table, scheduler_leader, using a time-based lease. This approach is far more robust as it doesn't depend on volatile session states.

Here’s how the new system works (commit ed891ea):

All workers start their scheduler in a paused state: scheduler.start(paused=True).
Only one designated leader worker can resume the scheduler: resume().
Leader election happens every 25 seconds using a simple DML statement: UPDATE scheduler_leader SET holder=$me, heartbeat=now() WHERE id=1 AND (holder=$me OR holder IS NULL OR heartbeat < now()-75s) RETURNING holder.
This transaction ensures only one worker can successfully update the record and claim leadership. The `heartbeat` column acts as a lease.
If a leader dies, its lease will expire after 75 seconds (heartbeat < now()-75s). Any other worker can then acquire the lease and become the new leader (failover).
When a worker shuts down cleanly, it releases the lease, allowing for immediate failover.

This new method has zero dependency on session state. It works reliably regardless of connection pooling, session resets, or re-connections. It guarantees a single leader across all running services (blue and green).

Verification and Lessons Learned

On the live server, I can now directly verify the leader election. The scheduler_leader table shows exactly one leader holding the lease, with a recent heartbeat. Any worker can query this table directly, removing any ambiguity.

The key lessons here are:

Session-level advisory locks are dangerous in clustered or pooled environments. They are prone to state corruption and difficult to debug.
Leader election should rely on robust, state-independent mechanisms. A simple database table with a time-based lease, updated via atomic DML, is a proven and reliable pattern.
Always distrust application state when it contradicts the database. If your application says it holds a lock, but the database says it doesn't, the database is the source of truth.

With the scheduler now stable, the backlog of dev.to posts is being processed by a newly resilient devto_backlog_sweep cron job, running every 4 hours to respect rate limits. The journey continues, one reliable cron job at a time.

...building aicoreutility.com in the open... aicoreutility.com