# How to fix idle in transaction connections in PostgreSQL

> **Quick answer:** An `idle in transaction` connection ran `BEGIN` but never
> committed or rolled back.  While it sits there it holds any locks it took and
> pins the `xmin` horizon so autovacuum cannot clean up dead tuples, quietly
> causing lock waits and bloat.  Find them in `pg_stat_activity` and cap them with
> `idle_in_transaction_session_timeout`, then fix the app to commit promptly.

## What the state actually means

A backend can be in several states.  The dangerous one is `idle in transaction`:

- `active`, running a query right now.
- `idle`, connected, **no** open transaction.  Harmless.
- `idle in transaction`, ran `BEGIN`, maybe some statements, and is now waiting
  with the transaction **still open**. Harmful.
- `idle in transaction (aborted)`, same, but a statement errored; it is waiting
  for `ROLLBACK`.

The usual cause is application code that opens a transaction and then does
something slow *inside* it, an external API call, waiting on user input, a long
compute, instead of committing quickly.

## Why it hurts

An open transaction is not free:

- **It holds locks.** Any row or table locks it acquired stay held until commit,
  so other queries can block behind it.
- **It blocks vacuum cleanup.** The transaction keeps an old snapshot, which pins
  the `xmin` horizon: autovacuum cannot remove dead tuples newer than that
  snapshot, so
  [bloat](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)
  builds up across the whole database while it sits there.
- **It occupies a connection slot** you may badly need under load.

## How do I find them?

```sql
SELECT pid,
       state,
       now() - xact_start  AS txn_open_for,
       now() - state_change AS idle_for,
       query               AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;
```

Match with `LIKE 'idle in transaction%'`, not `=`. There is a second state.`idle in transaction (aborted)`, where a statement errored and the transaction is
sitting open waiting for a `ROLLBACK`, and an equality check silently misses it,
even though it does the same damage.

The oldest `xact_start` is doing the most damage.  It is held its snapshot and
locks the longest.  To end a specific offender:

```sql
SELECT pg_terminate_backend(<pid>);
```

## Why does aI-generated code leave transactions open?

Your AI coding agent (Claude Code, Cursor) writes code that opens a transaction
and does the requested work, and sometimes that work includes a call to another
service, or an early `return` on an error path that skips the commit/rollback.  It
reads as correct, transactional code.  What it cannot see is that in production that
external call takes two seconds while the transaction holds locks and blocks
vacuum.  The cost is a runtime, concurrency property, invisible in the source.

## How do I keep idle-in-transaction from coming back?

- **Set a timeout** so a stuck transaction dies on its own.`idle_in_transaction_session_timeout` (since PostgreSQL 9.6) terminates any
  session idle inside a transaction past the limit:

  ```sql
  ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
  ```

- **Commit (or roll back) as soon as the database work is done.**
- **Never hold a transaction open across a network call** or user think-time.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the live session picture, which connections are `idle in transaction`, how long
they have held their snapshot, what locks they hold, so it can trace a stuck
transaction back to the code path that opened it and never committed.  It surfaces
and explains; it does not terminate sessions or change server settings for you.
[Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**What does idle in transaction mean?**
A connection opened a transaction with `BEGIN`, ran some statements, and is now
idle without `COMMIT` or `ROLLBACK`, the transaction is still open.

**Why is it a problem?**
It holds locks (blocking others) and pins the `xmin` horizon so autovacuum cannot
clean up dead tuples (causing bloat), and it ties up a connection.

**How do I find them?**
`pg_stat_activity` where `state LIKE 'idle in transaction%'` (use `LIKE`, not
`=`, so you also catch `idle in transaction (aborted)`), ordered by
`now() - xact_start`.

**How do I kill them automatically?**
Set `idle_in_transaction_session_timeout` (since PostgreSQL 9.6) at the server or
role level; sessions idle in a transaction past the limit are terminated.

## Related

- [Understanding lock contention in PostgreSQL](https://www.dbgorilla.com/learn/postgres/understanding-lock-contention-in-postgresql/)
- [What is table bloat in PostgreSQL and how do I fix it?](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)
- [PostgreSQL connection pooling and "too many connections"](https://www.dbgorilla.com/learn/postgres/postgresql-connection-pooling-too-many-connections/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)