Versions Compared

Old Version 1

changes.mady.by.user Unknown User (wiki_transfer)

Saved on

compared with

New Version 2

changes.mady.by.user Charles Kiplagat

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

<?xml version="1.0" encoding="utf-8"?>
<html>
Many problems that arise from the database can be attributed to database transactions that have not been completed propertly. This can cause the connection pool to fill to the limit and to cause other database operations such as VACUUM to fail to complete. Connection pooled transactions should read "idle" next to their process name, while transactions which have not yet completed successfully usually read "idle in transaction". Other messages you are likely to see include "SELECT waiting", which is the most serious problem, because this means that an HTTP request was made but the SELECT to get the data never completed - probably the user got fed up of waiting and went away.

...

2) Find and fix the places in the code where transactions are not safely completed. This is surprisingly hard, because although the application framework initialises and closes transactions at start and finish, there are a variety of things that can happen causing the transaction to fail. Principally, exceptions that occur at the database level can have unfortunate consequences for the state of the transaction, so this need to be looked at. In addition, any situation which causes a Context object to go out of scope (which can happen if additional context objects are created in the course of the processing) can leave a hanging transaction.

Panel

Contents

Table of Contents
outlinetrue
stylenone

Determining locked tables

...

Code Block
SELECT relation, transaction, pid, mode, granted, relname 
  FROM pg_locks 
  INNER JOIN pg_stat_user_tables 
  ON pg_locks.relation = pg_stat_user_tables.relid 
  WHERE pg_locks.pid='[pid]';

...

A useful query acquired from a user comment on the Postgres documentation page:

http://www.postgresql.org/docs/current/interactive/monitoring-locks.htmlImage Removed

Panel

select pg_class.relname, pg_locks.transaction, pg_locks.mode, pg_locks.granted as "g",
substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid
order by query_start;

An other query from Jeff Davis shows any queries that are waiting on a lock, and the
query that currently holds the lock on which those queries are waiting: (not included here for brevity)

http://groups.google.com/group/pgsql.general/browse_thread/thread/10f61707d242a308?pli=1Image Removed

Workaround: Killing "Idle in Transaction" processes with crontab

...