使用建议锁协调应用任务
学习 PostgreSQL 建议锁如何为应用提供轻量级、由程序控制的互斥锁，用于协调任务并保护关键区段，而无需锁定表行
使用建议锁协调应用任务 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
What Are Advisory Locks?
Advisory locks are locks whose meaning is defined entirely by your application. PostgreSQL does not associate them with any row or table — your code decides what a given lock key represents.
Why Use Them?
They are perfect for coordination that does not map to a single row:
- Ensuring only one worker runs a nightly job
- Serializing access to an external API
- Preventing two processes from importing the same file
Lock Keys
An advisory lock is identified by a 64-bit integer, or by two 32-bit integers. You pick the numbers; a common pattern is to hash a string into an integer key.
SELECT hashtext('nightly-report');Session-Level Locks
pg_advisory_lock holds the lock until you release it or the session ends. It blocks if another session already holds the same key.
SELECT pg_advisory_lock(42);
-- do exclusive work
SELECT pg_advisory_unlock(42);Try-Lock Without Blocking
Often you do not want to wait. pg_try_advisory_lock returns true if it got the lock and false immediately if someone else holds it.
SELECT pg_try_advisory_lock(42);The Single-Worker Pattern
A cron-triggered job uses try-lock to ensure only one instance runs. If it returns false, this instance simply exits.
-- if this returns false, another worker is running
SELECT pg_try_advisory_lock(hashtext('nightly-report'));Transaction-Level Locks
The _xact_ variants release automatically at COMMIT or ROLLBACK, so you cannot forget to unlock. Great for short critical sections.
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- protected work here
COMMIT; -- lock auto-releasedInspecting Held Locks
Advisory locks show up in pg_locks with locktype 'advisory'. Use it to debug who is holding what.
SELECT pid, classid, objid, granted
FROM pg_locks
WHERE locktype = 'advisory';Advisory vs Row Locks
Key differences from SELECT ... FOR UPDATE:
- Advisory locks are not tied to any row
- They survive across statements without holding row versions
- Their meaning is purely a convention your app agrees on
Pitfalls to Avoid
Watch out for:
- Forgetting to unlock session-level locks (prefer xact variants)
- Key collisions between unrelated features — namespace your keys
- Connection poolers reusing sessions and leaking locks
Unlocking Session Locks Cleanly
Session-level locks can be acquired multiple times and must be unlocked the same number of times. To clear everything held by the current session at once, use the unlock-all helper.
SELECT pg_advisory_unlock_all();Quick Check
Test your advisory lock knowledge.
Recap
You learned advisory locks:
- Application-defined locks not tied to rows or tables
- Identified by 64-bit or paired 32-bit keys
- Session-level vs auto-releasing transaction-level variants
pg_try_advisory_lockpowers single-worker patterns- Namespace keys and prefer xact locks to avoid leaks
常见问题解答
「使用建议锁协调应用任务」课时是免费的吗？
是的 — 「使用建议锁协调应用任务」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
「使用建议锁协调应用任务」这节课中我会学到什么？
学习 PostgreSQL 建议锁如何为应用提供轻量级、由程序控制的互斥锁，用于协调任务并保护关键区段，而无需锁定表行 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 PostgreSQL Performance & Query Optimization 需要有经验吗？
无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「使用建议锁协调应用任务」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？
能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。