# Practices for Embedding AI Agents in Software
# Short DB Transaction, Long Agent Session
🎯 The Hook
Holding a database transaction open while the LLM thinks for 30 seconds? Your connection pool will starve and the entire system grinds to a halt. Separate the long session from the short transaction.
🔥 The Problem
Traditional web apps finish request processing in milliseconds, so request-scoped transactions work fine. AI agents are different -- LLM calls take seconds to minutes. Keeping a transaction open during inference causes connection pool exhaustion, deadlocks, lock contention, and transaction timeouts. With P99 latency often several times P50, designs that work "on average" fail at the tail.
💡 The Pattern
Never hold a transaction or database connection during LLM inference. Read data in a short transaction, commit and release the connection, call the LLM, then write results in a new short transaction. Intermediate state between steps is persisted to an external state store (Redis, DynamoDB, or a session table). Use optimistic locking (version columns) to handle data races during inference -- never hold pessimistic locks across LLM calls. This reduces required connection pool size to roughly 10-20% of concurrent sessions.
✅ When to Use
Use when:
- The agent reads from and writes to a database around LLM calls
- LLM call latency exceeds roughly 1 second (most LLM calls qualify)
- Concurrent session count may reach half the connection pool size
Don't use when:
- Traditional CRUD processing with no LLM involvement
- Operations that must complete atomically in a single transaction (e.g., bank transfers)
⚠️ Pitfalls
- Between read and write, another process may update the same row. Always use optimistic locking (version column or updated_at check)
- Size your connection pool for concurrent transaction steps, not concurrent sessions. This pattern dramatically reduces the required pool size
- For sessions involving irreversible operations, choose a state store with disk persistence over purely in-memory stores
🔧 Implementation Approach
- Follow the sequence: short read transaction, commit, release connection, LLM call, re-acquire connection, short write transaction -- never hold transactions or connections during inference
- Include optimistic locking (version column or updated_at) in write transactions to handle data races during LLM inference via retry
- Persist intermediate state between steps in an external state store (Redis, DynamoDB, or RDB session table) to enable recovery after crashes
- Size connection pools for concurrent transaction steps, not concurrent sessions -- typically 10-20% of concurrent session count suffices
- For sessions involving irreversible operations, select a state store with disk persistence to meet durability requirements
#
AIAgents# #
SoftwareArchitecture#