Database Reference
In-Depth Information
Locking problems
Thanks to its MVCC design, PostgreSQL does not suffer from most locking problems, such as
writers locking out readers or readers locking out writers, but it still has to take locks when
more than one process wants to update the same row. And, it has to hold the write lock until
the current writer's transaction finishes.
So, if you have a database design where many queries update the same record, you can have
a locking problem.
The easiest way to find out if you do is to see if there are many backends waiting on locks by
running the following:
SELECT * FROM pg_locks WHERE not granted;
If it comes up empty, or with only one or two rows, then you probably don't have this problem.
To see which queries are waiting on which other queries, run the following:
select
a1.current_query as blocking_query,
a2.current_query as waiting_query,
t.schemaname ||'.'||t.relname as locked_table
from pg_stat_activity a1
join pg_locks p1 on a1.procpid = p1.pid and p1.granted
join pg_locks p2 on p1.relation = p2.relation and not p2.granted
join pg_stat_activity a2 on a2.procpid = p2.pid
join pg_stat_all_tables t on p1.relation = t.relid;
blocking_query | waiting_query | locked_table
-----------------------+-------------------------+--------------
<IDLE> in transaction | select * from t; | public.t
<IDLE> in transaction | select count(*) from t; | public.t
(2 rows)
Here, the
<IDLE>
in transaction is an open-console connection, which has issued just the
following commands:
BEGIN;
LOCK t;
and is waiting for further input.
Not enough CPU power or disk I/O capacity for the current load
These are usually caused by suboptimal query plans, but sometimes you just have not a
powerful enough computer.
Here,
top
is your friend for quick checks, and from the command line, run the following:
user@host:~$ top
