Limitations and Potential Issues When Working with Transactions: LOCK, DEADLOCK

Transactions in PostgreSQL provide isolation — one of the key ACID properties. To make this happen, the system uses locks. A lock is a mechanism that makes sure multiple transactions don't "fight" over the same row or table. Think of it like a line at the grocery store: only one person gets checked out at a time. Locks work kinda the same way, but they control access to data in tables.

Main Types of Locks

PostgreSQL has a few types of locks, and you might have already seen some of them in EXPLAIN ANALYZE:

1. ROW EXCLUSIVE (row exclusive lock) — happens when you change data in a row. This is the most common lock type, for example, when you run INSERT, UPDATE, or DELETE.
2. SHARE (shared lock) — used for operations that make sure data doesn't change while your query is running, like with SELECT ... FOR SHARE.
3. EXCLUSIVE (exclusive lock) — blocks any other operations on the data except reading.

You could say locks are like guard dogs watching over your data.

The Lock Problem: What Can Go Wrong?

Now that we get what locks are, let's see what kind of trouble they can cause. The biggest one is the deadlock problem. A deadlock is when two (or more) transactions are waiting on each other, so everything gets stuck in an endless loop. It usually looks like this:

1. Transaction 1 locks row A and wants to access row B.
2. Transaction 2 locks row B and wants to access row A.
3. Since both rows are locked, neither transaction can finish.

Deadlock Example

To really feel the pain of a dev who hits a deadlock, imagine this scenario:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- At the same time
-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

-- Transaction 1 tries to lock id = 2
UPDATE accounts SET balance = balance - 100 WHERE id = 2;

-- Transaction 2 tries to lock id = 1
UPDATE accounts SET balance = balance + 200 WHERE id = 1;

Voilà! Each transaction is locked by the other. PostgreSQL will eventually notice and throw a deadlock error.

How to Avoid Deadlocks?

1. Consistent order of accessing data.
   Try to always access data in the same order. If transaction 1 locks row A first and then row B, transaction 2 should do the same: row A first, then row B.

-- Correct access order
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

2. Keep transactions short.
   Long transactions make deadlocks more likely. Finish transactions as fast as you can: BEGIN, do your changes, and COMMIT right away.

3. Use transaction isolation levels.
   If your task doesn't need super strict data isolation, consider using the READ COMMITTED isolation level. It helps avoid some locks.

Timeouts and Lock Diagnostics

There are tools for diagnosing and preventing locks in PostgreSQL. For example, you can set a max lock wait time:

SET lock_timeout = '5s';  -- Set lock timeout to 5 seconds

If a lock is held longer than that, the transaction gets killed, which stops a total dead end.

Tracking Locks in PostgreSQL

One handy command for monitoring locks is pg_locks. It shows all active locks in the system:

SELECT * FROM pg_locks;

You can see which transactions are holding locks and which are waiting for them to be released. Super useful when debugging deadlocks.

LOCK Features and Manual Object Locking

If you need to manage locks manually, use the LOCK command:

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

Heads up: ACCESS EXCLUSIVE is the strongest lock, it blocks any other operations on the table, even SELECT. Only use it for special cases (like changing the table structure).

To lock specific rows when updating, use SELECT ... FOR UPDATE:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

This makes sure other transactions can't change those rows until your transaction is done.

What Should You Remember About Locks?

Locks aren't evil, they're a tool. They help keep your data consistent, but you gotta use them carefully. Here are some key tips:

1. Don't start a transaction unless you know exactly why you need it.
2. Finish your transaction as quickly as possible.
3. Avoid accessing data in different orders.
4. Use isolation levels that fit your task.

Now you're ready to handle locks and deadlocks! May pg_locks and the wisdom of ACID be with you.

2

Task

SQL SELF, level 54, lesson 1

Locked

Creating and Viewing Locks

Creating and Viewing Locks

Open