In many cases, locks are a major issue for most system administrators. If a transaction blocks some other operations, it is usually a problem. End users will complain that "something hangs." However, you can rely on the fact that PostgreSQL never hangs without a reason. Usually, it is simple and all about locking.

In this section you will learn how to detect locks and how to figure out who is locking which transaction.

Let's do a simple example considering the following data:

test=# CREATE TABLE t_test AS SELECT * 
   FROM generate_series(1, 10) AS id;
SELECT 10

The goal is to have two concurrent SELECT FOR UPDATE operations reading slightly different data. Those two operations will lock each other, and then the goal will be to figure out which operation is blocking which transaction.

The first SELECT FOR UPDATE command selects everything larger than 9:

test=# BEGIN;
BEGIN
test=# SELECT * FROM t_test WHERE id > 9 FOR UPDATE;
 id 
----
 10
(1 rows)

The second transaction locks...