PostgreSQL Row Locks vs Advisory Locks for Ledger Updates

For concurrent ledger updates, PostgreSQL row locks are the safer default because they protect the actual data row, while advisory locks are application-level flags that require you to enforce discipline yourself. Row locks guarantee that two transactions cannot update the same ledger entry simultaneously, preventing lost updates and double-spending. Advisory locks are faster and more flexible for coordinating operations across multiple tables or external resources, but they do nothing to protect your data unless every code path checks them.

Formula

This calculator estimates the maximum throughput of a serialized lock queue and the average wait time for each transaction. The formulas are:

• Max throughput = (1000 / u) × t, where u is the average transaction duration in milliseconds and t is the number of concurrent transactions. This gives the theoretical maximum number of transactions per second if all t transactions are always waiting in line.
• Average wait time = (u × (t − 1)) / 2, where u is the average transaction duration and t is the number of concurrent transactions. This is the average time a transaction spends waiting for its turn, assuming a first-come, first-served queue.

These are simplified models that ignore real-world overhead like lock acquisition time, context switching, and I/O. They are useful for rough capacity planning, not for precise performance prediction.

Worked Example

Using the default values from the calculator: 10 concurrent transactions, each taking 50 ms on average.

1. Max throughput = (1000 / 50) × 10 = 20 × 10 = 200 transactions per second.
2. Average wait time = (50 × (10 − 1)) / 2 = (50 × 9) / 2 = 450 / 2 = 225 ms.

So with 10 concurrent transactions each taking 50 ms, you can process about 200 transactions per second, and each transaction waits on average 225 ms before it gets the lock.

How Row Locks Work in PostgreSQL

When you execute UPDATE or SELECT ... FOR UPDATE on a row, PostgreSQL places an exclusive row-level lock on that specific row. The lock is held until the transaction commits or rolls back. Any other transaction trying to update the same row will block until the first transaction finishes. This is the standard mechanism for preventing lost updates in a ledger: if two payments try to deduct from the same account balance, the second one waits, reads the new balance after the first commits, and then applies its own change.

Row locks are automatic and invisible to the application. You don’t need to remember to acquire them; the database does it for you. They are also deadlock-safe if you consistently lock rows in the same order (e.g., always update the account with the lower ID first). PostgreSQL detects deadlocks and aborts one of the conflicting transactions, which your application must handle with a retry.

One important detail: row locks are tied to the transaction. If you need to hold a lock across multiple statements or even across application requests, you must keep the transaction open, which can increase connection pool pressure and lock contention.

How Advisory Locks Work

Advisory locks are application-defined locks stored in PostgreSQL’s memory. They are not attached to any table or row. You acquire them with pg_advisory_lock() (session-level) or pg_try_advisory_lock() (non-blocking), and release them with pg_advisory_unlock(). There is also a transaction-scoped variant, pg_advisory_xact_lock(), which releases automatically at commit or rollback.

Advisory locks are useful when you need to coordinate access to a resource that isn’t a single row. For example, you might want to ensure that only one process runs a daily reconciliation job, or that two different tables are updated atomically as a unit. You can use an advisory lock with a key derived from a ledger ID, and then all code that touches that ledger must acquire the same lock.

The main risk is that advisory locks are purely cooperative. If any code path forgets to acquire the lock, it can corrupt data without any warning. There is no database-level enforcement. Also, session-level advisory locks must be explicitly released, and if the session dies, the lock is released automatically, but if you forget to release it, you can leak locks and eventually block all other sessions.

Choosing Between Row Locks and Advisory Locks

The decision comes down to what you are protecting:

• Use row locks when you are updating a single row or a small set of rows in a table. This is the default for ledger entries, account balances, or inventory counts. The database handles correctness automatically.
• Use advisory locks when you need to coordinate across multiple tables, external systems, or long-running jobs. For example, if a ledger entry has a header and many detail lines, and you want to prevent two transactions from modifying the same header plus its details, an advisory lock on the header ID is simpler than locking all detail rows.
• Use advisory locks for multi-statement operations that don’t fit in a single transaction. If you need to hold a lock while performing an external API call or a slow computation, an advisory lock is better than keeping a transaction open, because transactions hold connection resources and can cause long-running locks on other rows.

A common pattern is to use both: row locks for the actual data modifications, and advisory locks to serialize the overall business operation. For instance, you might take an advisory lock on a customer ID before reading their balance, then update the balance row with a row lock, then release the advisory lock. This prevents two concurrent requests from even starting the read-modify-write cycle, reducing wasted work and deadlocks.

Common Problems and Misconceptions

One frequent misconception is that row locks are always slower than advisory locks. In practice, row locks are very fast for short transactions because PostgreSQL stores lock information in the row header. Advisory locks require a system catalog lookup and are slightly slower to acquire, but they avoid blocking on unrelated rows. The real performance difference comes from contention: if many transactions fight over the same row, row locks will serialize them and throughput drops. Advisory locks can allow more parallelism if you design the lock granularity carefully.

Another issue is lock escalation. PostgreSQL does not escalate row locks to table locks, so you don’t get the “lock everything� behavior of some other databases. That is usually good, but it means you must be careful with foreign keys: updating a parent row can take locks on child rows if there are foreign key constraints, which can cause unexpected blocking.

Deadlocks are a real risk with row locks. If transaction A locks row 1 and then tries to lock row 2, while transaction B locks row 2 and then tries to lock row 1, PostgreSQL will detect the cycle and abort one transaction. The standard fix is to always acquire locks in a consistent order. Advisory locks have the same deadlock risk if you use multiple locks, but you control the acquisition order more explicitly.

A common mistake with advisory locks is using a 32-bit key when you need a 64-bit key. If you pass a single integer, it is a 32-bit lock. If you pass two integers, it is a 64-bit lock. If you use a hash of a UUID, make sure you don’t accidentally collide with another key. Use pg_advisory_lock(hashtext('ledger:' || id)) to get a 32-bit hash, but be aware of collision risk.

Performance and Contention

For a typical ledger with frequent updates to the same account, row locks are the bottleneck. The more concurrent transactions target the same row, the longer each waits. The calculator above gives a rough estimate: with 10 concurrent transactions each taking 50 ms, you get about 200 transactions per second. If you increase the transaction duration to 200 ms, throughput drops to 50 per second, and average wait time jumps to 900 ms.

To improve throughput, you can reduce transaction duration by keeping transactions short, batching operations, or using partitioning to spread rows across multiple tables. Advisory locks can help by allowing you to serialize only the critical section, but they don’t reduce the fundamental serialization if the data itself must be updated in order.

Another technique is to use a queue table with a status column and use SELECT ... FOR UPDATE SKIP LOCKED to claim a row. This is a hybrid approach: you use row locks, but you skip rows that are already locked, allowing different workers to process different ledger entries concurrently. This is often used for job queues and works well for ledgers where each entry is independent.

Safety and Correctness

Regardless of which lock type you choose, you must ensure that your transaction isolation level is appropriate. The default READ COMMITTED is usually fine for ledger updates because each statement sees the latest committed data. If you use REPEATABLE READ or SERIALIZABLE, you may get serialization failures that require retries, but you also get stronger guarantees against anomalies like write skew.

Always handle deadlock errors (SQLSTATE 40P01) and serialization failures (40001) in your application. A simple retry loop with a small random backoff is sufficient for most systems. Never ignore these errors, because they mean your transaction was aborted and any changes were rolled back.

Advisory locks are not transaction-scoped by default. If you use pg_advisory_lock() and your transaction aborts, the lock is still held until you explicitly release it or the session ends. This can cause hard-to-debug hangs. Prefer pg_advisory_xact_lock() when the lock should be tied to the transaction, and always release session-level locks in a finally block or use a timeout.

Frequently Asked Questions

Can I use advisory locks instead of row locks for a simple balance update?

Yes, but it is more error-prone. You would need to acquire the advisory lock before reading the balance, then update the row, then release the lock. If any code path forgets the advisory lock, you get a lost update. Row locks are automatic, so they are the safer choice for a simple single-row update.

Do row locks block reads?

No. In PostgreSQL, row locks do not block SELECT statements that use the default READ COMMITTED isolation. Readers see the last committed version of the row, even if another transaction holds a lock on it. This is because PostgreSQL uses Multi-Version Concurrency Control (MVCC). Only other writers that try to update the same row will block.

What happens if a transaction holding a row lock crashes?

PostgreSQL automatically releases all locks held by a transaction when the session disconnects or the server restarts. The transaction is rolled back, and any changes are undone. This is true for both row locks and session-level advisory locks, but advisory locks are released when the session ends, not necessarily when a transaction aborts.

Can I use advisory locks to prevent two users from editing the same ledger entry in a web app?

Yes, you can use an advisory lock keyed on the ledger entry ID. But you must also ensure that the lock is released when the user finishes editing, which may be much later than a database transaction. This is a valid use case for session-level advisory locks, but you must handle session timeouts and manual release carefully. A simpler alternative is to use an optimistic locking column (like updated_at) and check it on update.

Are advisory locks faster than row locks?

For very short operations, row locks are often faster because they are stored in the row header and require no extra system catalog lookups. Advisory locks are slightly slower to acquire, but they can reduce contention if you use them to serialize a broader operation, allowing more parallelism in the database. The performance difference is usually small compared to the cost of the actual data modification.

How do I avoid deadlocks with row locks in a ledger?

Always update rows in a consistent order. For example, if you are transferring money between two accounts, always update the account with the lower ID first, then the higher ID. This ensures that two concurrent transfers never try to lock the same two rows in opposite order. If you cannot control the order, use a single advisory lock on the transaction ID to serialize all transfers.

Related

• Multi Site Hosting: What to Check Before Buying
• Malware Protection If You Turn Off S Mode
• VLC Sync Subtitles: How to Fix Audio & Sub Delay

in t | Number of concurrent transactions | - | 10 | 1 | 1000
in u | Average transaction duration (ms) | - | 50 | 1 | 10000
out throughput | Max transactions per second (approx) | - | (1000 / u) * t | 0
out wait | Average wait time per transaction (ms) | - | (u * (t - 1)) / 2 | 0