Keep Transactions Short to Reduce Lock Contention

Why This Matters

Locks are held for the entire duration of a transaction. Long transactions keep locks longer, blocking other queries and causing contention. This leads to:

• Slow queries waiting for locks to be released
• Increased deadlock risk
• Poor application performance under load
• Connection pool exhaustion

The Problem

Doing slow operations (HTTP calls, file I/O, external API calls) inside transactions keeps locks held unnecessarily:

-- BAD: HTTP call inside transaction
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 123;
-- Lock held on orders.id=123

-- HTTP call takes 2 seconds!
-- Other queries waiting for this lock are blocked
-- http.post('https://payment-api.com/charge', ...)

UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;
-- Lock released after 2+ seconds

While waiting for the HTTP call, the lock on orders.id=123 blocks all other queries trying to access that row. With many concurrent transactions, this creates a bottleneck.

The Solution

Do slow operations outside the transaction. Only keep database operations inside transactions:

-- GOOD: HTTP call outside transaction
-- Step 1: Quick database update
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 123;
COMMIT;
-- Lock released immediately

-- Step 2: Slow HTTP call (no locks held)
-- http.post('https://payment-api.com/charge', ...)
-- Takes 2 seconds, but doesn't block database

-- Step 3: Final database update
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;

For complex workflows, use a state machine pattern:

-- Use status field to track progress
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  status TEXT NOT NULL,
  -- ... other fields
);

-- Transaction 1: Mark as processing (fast)
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 123 AND status = 'pending';
COMMIT;

-- Do slow work outside transaction
-- payment_result = charge_card(...)

-- Transaction 2: Update result (fast)
BEGIN;
UPDATE orders 
SET status = CASE WHEN payment_result.success THEN 'paid' ELSE 'failed' END
WHERE id = 123;
COMMIT;

What to Avoid Inside Transactions

• HTTP/API calls: Network latency can be seconds. Move these outside transactions.
• File I/O operations: Reading/writing files can be slow. Do this before or after transactions.
• Heavy computations: CPU-intensive work blocks the transaction. Use background jobs instead.
• User input: Waiting for user interaction keeps locks held. Get input first, then start transaction.
• Sleep/delays: Never use pg_sleep() or delays inside transactions unless absolutely necessary.
• External service calls: Email sending, SMS, webhooks - all should happen outside transactions.

Monitoring Long Transactions

Query active transactions to find long-running ones:

-- Find transactions running longer than 1 second
SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > INTERVAL '1 second'
ORDER BY duration DESC;

Check for blocked queries waiting on locks:

-- Find queries waiting for locks
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;