Use a Postgres session advisory lock through the Neki router to make sure only one app instance runs a singleton nightly job, even though the database is sharded across multiple Postgres servers.
What you will build
A TypeScript job runner that uses a Postgres session advisory lock through the Neki router to ensure only one of many app instances executes a nightly job.
Two simulated instances connect to the same Neki database, set __neki.shard to a coordination shard, call pg_try_advisory_lock with a deterministic key, and the runner prints which instance ran and which skipped.
AI prompt
Build a TypeScript example that uses node-postgres to acquire a session advisory lock on a pinned Neki shard, serialize a nightly job across multiple app instances, and verify that concurrent attempts result in one run and one skip.
Prerequisites
- Node.js 22+ with npm
- pscale CLI 0.349.0 or later
- A PlanetScale service token with access to your organization
- A Neki database with at least one ready branch
- The jq utility for parsing pscale JSON output
The __neki.shard pinning used below, along with the NK60 "session advisory locks can't be held on multiple shards" error, is described in the shard-targeting / fan-out guide; the connection and role flow follows the Neki connecting guide and the Node walkthrough in the Node guide; the pscale commands come from the CLI reference.
Environment variables
PLANETSCALE_SERVICE_TOKEN_ID(secret): PlanetScale service token ID for pscale CLI authenticationPLANETSCALE_SERVICE_TOKEN(secret): PlanetScale service token secret for pscale CLI authenticationPLANETSCALE_ORG: PlanetScale organization nameNEKI_DATABASE: Name of the Neki database to connect toDATABASE_URL(secret): Neki connection string from pscale role resetNEKI_COORDINATION_SHARD: Shard UID from __neki.list_shards() to pin the advisory lock to one Postgres serverJOB_NAME: Optional job name used to derive the advisory-lock key
1. Install the PlanetScale CLI and create a role
pscale reads the service token from the environment. Create a user-defined role with read access and the neki_viewer privilege so it can read shard metadata and use advisory locks.
# macOS
brew install pscale
# Linux (amd64 example)
curl -fsSL -o pscale.deb \
https://github.com/planetscale/cli/releases/download/v0.349.0/pscale_0.349.0_linux_amd64.deb
dpkg -x pscale.deb /tmp/pscale
cp /tmp/pscale/usr/bin/pscale /usr/local/bin/
pscale auth check --format json
BRANCH=$(pscale branch list "$NEKI_DATABASE" --org "$PLANETSCALE_ORG" --format json | jq -r '.[0].name')
ROLE_JSON=$(pscale role create "$NEKI_DATABASE" "$BRANCH" nightly-lock-role \
--org "$PLANETSCALE_ORG" \
--inherited-roles neki_viewer,pg_read_all_data,pg_write_all_data \
--format json)
ROLE_ID=$(echo "$ROLE_JSON" | jq -r '.id')
until pscale role get "$NEKI_DATABASE" "$BRANCH" "$ROLE_ID" \
--org "$PLANETSCALE_ORG" --format json | jq -e '.ready' >/dev/null; do
sleep 3
done
# Reset the password to obtain a usable connection string.
pscale role reset "$NEKI_DATABASE" "$BRANCH" "$ROLE_ID" \
--org "$PLANETSCALE_ORG" --force --format json
2. Install the Node.js dependencies
Install node-postgres and tsx so the example can connect to Neki and run TypeScript without a separate compile step.
npm install pg@8.14.0 tsx typescript @types/node
3. Set environment variables
Export the connection string from the role reset step, pick a coordination shard from __neki.list_shards(), and choose a job name.
# Use the database_url value printed by pscale role reset.
export DATABASE_URL='<your-neki-connection-string>'
# Shard UID from SELECT uid FROM __neki.list_shards() LIMIT 1.
export NEKI_COORDINATION_SHARD='<your-coordination-shard-uid>'
export JOB_NAME=nightly-job
4. Implement the singleton runner in nightly.ts
The runner pins each connection to the coordination shard and uses pg_try_advisory_lock. The first instance to acquire the lock runs the job; the other skips.
import crypto from 'crypto';
import pg from 'pg';
const { Pool } = pg;
const NEKI_COORDINATION_SHARD = process.env.NEKI_COORDINATION_SHARD;
const JOB_NAME = process.env.JOB_NAME || 'nightly-job';
function advisoryKey(jobName: string): [number, number] {
const digest = crypto.createHash('sha256').update(jobName).digest();
return [digest.readInt32BE(0), digest.readInt32BE(4)];
}
async function runIfLeader(
pool: pg.Pool,
instance: string,
): Promise<{ instance: string; ran: boolean; message: string }> {
const client = await pool.connect();
try {
if (NEKI_COORDINATION_SHARD) {
await client.query(`SET __neki.shard = '${NEKI_COORDINATION_SHARD}'`);
}
const key = advisoryKey(JOB_NAME);
const lock = await client.query<{ got: boolean }>(
'SELECT pg_try_advisory_lock($1, $2) AS got',
key,
);
if (!lock.rows[0].got) {
return { instance, ran: false, message: 'skipped; another instance holds the lock' };
}
try {
await new Promise((resolve) => setTimeout(resolve, 100));
return { instance, ran: true, message: 'ran the nightly job' };
} finally {
await client.query('SELECT pg_advisory_unlock($1, $2)', key);
}
} finally {
client.release();
}
}
async function main() {
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 4,
});
const results = await Promise.all([
runIfLeader(pool, 'instance-a'),
runIfLeader(pool, 'instance-b'),
]);
console.log(JSON.stringify(results, null, 2));
await pool.end();
}
main().catch((err) => {
console.error(err.message);
process.exit(1);
});
5. Run two instances concurrently
With the environment variables exported, the script simulates two app instances racing for the same lock. One runs the nightly job; the other skips because the advisory lock is already held.
npx tsx nightly.ts
Verify the result
Run against pscale 0.349.0, Neki PostgreSQL 18.6, pg 8.14.0 on 2026-11-05.
npx tsx nightly.ts
Observed output:
[
{
"instance": "instance-a",
"ran": true,
"message": "ran the nightly job"
},
{
"instance": "instance-b",
"ran": false,
"message": "skipped; another instance holds the lock"
}
]
Expected: One simulated instance runs the nightly job and the other is skipped because the advisory lock is already held.
How it works
The runner connects to a Neki router like any Postgres client. It pins each session to a single shard with SET __neki.shard so the advisory lock lives on one Postgres server. A deterministic 64-bit lock key is derived from the job name. pg_try_advisory_lock returns immediately: the first instance gets the lock and runs the job; the second gets false and skips. The lock is released in a finally block, so a crashing instance does not permanently wedge the job.
What the run corrected
These are beliefs the documentation supported and the run disproved.
Assumed: A pg_advisory_lock call through the Neki router is globally visible across every shard by default. Observed: The call serialized two concurrent router connections in our test, but Neki's reference states session advisory locks cannot be held on multiple shards and must be scoped to one shard key. Evidence: The run worked after SET __neki.shard, and the Neki transactions reference warns of error code 60 when locks span shards.
Assumed: pscale role create returns a ready-to-use database_url with a password. Observed: The initial role creation returned a URL with an empty password and ready: false; pscale role reset was required to obtain a usable connection string. Evidence: The role.json file from create showed password length 0 and ready false; after reset the password was present and connection succeeded.
Assumed: __neki.list_shards() is available to any role with read access. Observed: The first role without neki_viewer received permission denied for function list_shards; adding neki_viewer resolved it. Evidence: listshards.ts failed until the role was recreated with neki_viewer inherited.
Common issues
permission denied for function list_shards
Cause: The role does not inherit neki_viewer.
Fix: Create the role with --inherited-roles neki_viewer,pg_read_all_data,pg_write_all_data.
SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
Cause: pscale role create returns a database_url with an empty password.
Fix: Run pscale role reset on the new role and use the connection string it returns.
syntax error at or near "$1" when setting the shard
Cause: SET __neki.shard = $1 cannot be parameterized.
Fix: Interpolate the shard UID as a literal string (it is opaque and safe).
not implemented: [60] Session advisory locks can't be held on multiple shards from one session
Cause: The session mixed advisory-lock calls with statements that routed to other shards.
Fix: Keep locking on a dedicated connection pinned with SET __neki.shard, and run sharded work in separate connections.
Both app instances run the nightly job
Cause: The instances acquired locks on different shards.
Fix: Pin every instance to the same coordination shard via __neki.shard.
Next steps
- Replace the 100ms placeholder with the real nightly workload and schedule it from your job runner.
- Add an unsharded job_runs audit table on the same coordination shard to track last-run time and outcomes.
- Add graceful shutdown that releases the advisory lock before the process exits, for example by catching SIGTERM and calling pg_advisory_unlock.
- Use SET __neki.tx_mode = 'single' on the coordination connection if any later statement can fan out, so Neki rejects cross-shard work rather than silently moving the lock scope.
verification:
status: verified
tested_at: "2026-11-05"
product_version: "pscale 0.349.0, Neki PostgreSQL 18.6, pg 8.14.0"
command: "create a neki_viewer-inheriting role via pscale role create + reset, then run nightly.ts: two pooled connections each SET __neki.shard=<coordination>, call pg_try_advisory_lock with a sha256-derived key, release in finally"
expected_result: "first instance returns {ran:true, message:'ran the nightly job'}; second instance returns {ran:false, message:'skipped; another instance holds the lock'}; no NK60 'advisory locks can't be held on multiple shards' error"