1---
2title: Use Advisory Locks for Application-Level Locking
3impact: MEDIUM
4impactDescription: Efficient coordination without row-level lock overhead
5tags: advisory-locks, coordination, application-locks
6---
7 
8## Use Advisory Locks for Application-Level Locking
9 
10Advisory locks provide application-level coordination without requiring database rows to lock.
11 
12**Incorrect (creating rows just for locking):**
13 
14```sql
15-- Creating dummy rows to lock on
16create table resource_locks (
17  resource_name text primary key
18);
19 
20insert into resource_locks values ('report_generator');
21 
22-- Lock by selecting the row
23select * from resource_locks where resource_name = 'report_generator' for update;
24```
25 
26**Correct (advisory locks):**
27 
28```sql
29-- Session-level advisory lock (released on disconnect or unlock)
30select pg_advisory_lock(hashtext('report_generator'));
31-- ... do exclusive work ...
32select pg_advisory_unlock(hashtext('report_generator'));
33 
34-- Transaction-level lock (released on commit/rollback)
35begin;
36select pg_advisory_xact_lock(hashtext('daily_report'));
37-- ... do work ...
38commit;  -- Lock automatically released
39```
40 
41Try-lock for non-blocking operations:
42 
43```sql
44-- Returns immediately with true/false instead of waiting
45select pg_try_advisory_lock(hashtext('resource_name'));
46 
47-- Use in application
48if (acquired) {
49  -- Do work
50  select pg_advisory_unlock(hashtext('resource_name'));
51} else {
52  -- Skip or retry later
53}
54```
55 
56Reference: [Advisory Locks](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS)
57