Section 1 of 3 · 6 minutes
Understand why transactions hold locks
Your goal
What you will be able to do
Explain how a transaction keeps locks until commit or rollback protects consistency.
Why this matters at work
The practical reason
A query that finished can still block others when its transaction remains open.
Learn
The idea in plain English
PostgreSQL locks rows and objects to keep concurrent work consistent. Most locks remain until the transaction ends, not merely until one statement finishes.
An idle session inside a transaction can therefore block work and delay vacuum cleanup. Check transaction start time and state before focusing only on the current query text.
Remember these points
- Transaction boundaries control most lock lifetimes.
- Idle in transaction is different from ordinary idle.
- Long transactions can affect locking and vacuum.
See it in SQL
Find long open transactions
List sessions whose transactions have remained open for more than one minute.
SELECT
pid,
usename,
application_name,
state,
now() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;This returns old transactions whether they are currently active or idle. Application and role fields help find the owner.
Age alone is not permission to cancel. Confirm the workload, lock impact, and rollback cost first.
The oldest open transactions first, with owner, application, state, and age.
Useful words
Important terms
- Idle in transaction
- A session waiting for the client while a transaction is still open.
Quick check · Not graded
Check your understanding
When are most transaction locks released?
Your progress is saved to your signed-in account.