# 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#