How to release possible Postgres row locks?

Simple:

Get the active locks from pg_locks:

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid 
ORDER BY relation asc;

Copy the pid(ex: 14210) from above result and substitute in the below command.

SELECT pg_terminate_backend(14210)

To release possible locks from Postgres I generally follow these in sequence.

Find long-running queries in your DB by running the following query. This will help you fetch the PIDs of the long-running query which is blocking your update.

SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

or if you can find out which processes are holding a lock on a particular table by running this query

SELECT *
FROM pg_locks l
JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
WHERE t.relname = 'Bill';

Once you figure out the PID which is 'active' and blocking your update you can kill it by running this query. It takes some time to kill the process.
```
SELECT pg_cancel_backend(__pid__);
```
Check by running Query 2 if the process is killed. If it still is active then kill this process by running this query.
```
SELECT pg_terminate_backend(__pid__);
```

What version of PostgreSQL are you running? The following assumes 8.1.8 or later (it may apply to earlier versions too, I don't know).

I presume that you mean that phpPgAdmin timed out -- the PostgreSQL backend will take as long as it takes to complete a query/update. In that case, it's possible that the original session is still alive and the UPDATE query is still running. I suggest running the following query (taken from chapter 24 of the PostgreSQL docs) on the machine that hosts the PostgreSQL server process, to see whether the session is still alive:

ps auxwww|grep ^postgres

Several rows should appear: 1 for the postmaster master process, and 1 each for "writer", "stats buffer", and "stats collector" processes. Any remaining lines are for processes serving DB connections. These lines will contain the username and database name.

Hopefully, from that you can see whether the session you performed the original UPDATE in is still hanging around. Although in theory you could find more detailed info by SELECTing from the system view pg_stat_activity, by default PostgreSQL is not set up to populate the most useful fields (such as current_query and query_start). See chapter 24 for how to enable this in the future.

If you see the session is still there, kill it. You will need to be logged in as the user running the process (usually postgres) or root to do so -- if you don't run the server yourself, get your DBA to do this for you.

One more thing: for updating rows in a table, PostgreSQL avoids using locks. Instead, it allows every writing transaction to create a new "version" of the DB, which becomes the "current version" when the transaction is committed, provided it doesn't conflict with updates made in the meantime by other transactions. So I suspect the "hanging" you're seeing is caused by something else -- though what, I'm not sure. (Have you checked the obvious things, like whether the disk partition containing the DB is full?)

It's possible to see the locks.

Here is a view to make it a bit easier than using pg_locks directly:

CREATE OR REPLACE VIEW public.active_locks AS 
 SELECT t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
   FROM pg_locks l
   JOIN pg_stat_all_tables t ON l.relation = t.relid
  WHERE t.schemaname <> 'pg_toast'::name AND t.schemaname <> 'pg_catalog'::name
  ORDER BY t.schemaname, t.relname;

Then you just select from the view:

SELECT * FROM active_locks;

And kill it with:

SELECT pg_cancel_backend('%pid%');

Other solutions: http://wiki.postgresql.org/wiki/Lock_Monitoring

How to release possible Postgres row locks?

Tags:

Database

Sql

Postgresql

Related

Recent Posts