Advisory-Locks

Right, so you’ve gotten your locks all lined up, and now everything has ground to a halt. Welcome to the deadlock, the database equivalent of a standoff in a Western movie, but with less dramatic music and more error logs. A deadlock happens when two or more transactions are each waiting for the other to release a lock, creating a perfect circle of pointless waiting. The database isn’t stupid; it won’t let this nonsense continue forever. One of you is going to get shot (figuratively, your transaction will be rolled back) so the other can live. Your job is to make sure it’s not your transaction that gets chosen and, more importantly, to write your code so these standoffs are rare and handled gracefully when they do occur.

Alright, let’s talk about advisory locks. You’ve probably been in a situation where you need to coordinate actions across your entire application, maybe to prevent two instances of a job scheduler from running the same task or to ensure a single user can’t spam-click a “process payment” button. You could create a dedicated “lock” table and use SELECT ... FOR UPDATE on a specific row, but that feels a bit clunky, doesn’t it? It involves table I/O, it can contribute to vacuum load, and it just seems like using a sledgehammer to crack a nut.

Right, let’s talk about the big, blunt instruments of the locking world: table-level locks. While row-level locks are like a surgeon’s scalpel, table locks are the sledgehammer you use when you need to block everyone from touching a table, or when you’re about to do something so drastic to the table’s very structure that you can’t have anyone peeking in. The first thing you need to understand is that these locks aren’t some abstract concept; they are very real modes that the database engine applies, and they have a strict hierarchy of who blocks whom. The main modes you’ll encounter are:

Right, so you’ve decided you want a lock. Not just any lock, but a specific row. You march up to your database, SELECT ... FOR UPDATE in hand, ready to claim what’s yours. And then you wait. And wait. Because someone else is already holding a lock on that row, and your transaction is now stuck in line, blocked and patient. That blocking behavior is the sensible, default way to handle concurrency. It preserves serialization and prevents a dozen transactions from suddenly stampeding through the moment a lock is released. But sensible isn’t always what you need. Sometimes, waiting is a luxury you can’t afford. Your application might be a high-throughput queue system where a waiting process grinds everything to a halt. Or maybe you’re building a user-facing feature where spinning forever is a terrible user experience. This is where PostgreSQL gives you two brilliant, slightly dangerous tools to politely decline to wait in line: NOWAIT and SKIP LOCKED.

Alright, let’s pull back the curtain on the weaker lock modes. You’ve met the heavy hitters—FOR UPDATE and its row-level exclusive lock. But sometimes you need to collaborate, not conquer. That’s where this crew comes in. They’re the diplomats of the locking world, allowing varying degrees of shared access while still maintaining some control. They exist because the designers realized that slapping a fully exclusive lock on everything is like using a sledgehammer to crack a walnut—effective, but you’ll make a huge mess and nobody else can have any walnuts.

Right, let’s talk about SELECT FOR UPDATE. You’re about to step out of the cozy, read-only world of SELECT and into the slightly more dangerous territory where you need to make sure the data you’re looking at stays that way until you’re done with it. It’s the difference between window shopping and putting an item on hold. The latter comes with responsibility, and if you do it wrong, you’ll block everyone else in the store.