SQLite session writes other than add_items still strand the write lock on failure

[abhay-codes07]

Please read this first

• Have you read the docs? Yes — Sessions.
• Have you searched for related issues? Yes. fix(memory): roll back a failed SQLiteSession insert #4163 fixed exactly this for SQLiteSession.add_items; this issue is that the same defect remains in the five sibling write paths. Also searched rollback, write lock, in_transaction, database is locked.

Describe the bug

#4163 established that a write failing partway through leaves an open transaction on the cached connection, and that "an open write transaction would hold the SQLite write lock for the lifetime of the connection and block every later writer." That fix was applied only to SQLiteSession.add_items.

The same defect is still present in five other write paths:

Method	Stranded write transaction after a failed statement
SQLiteSession.add_items (fixed in #4163)	no
SQLiteSession.pop_item	yes
SQLiteSession.clear_session	yes
AsyncSQLiteSession.add_items	yes
AsyncSQLiteSession.pop_item	yes
AsyncSQLiteSession.clear_session	yes

clear_session is the clearest case: it issues two DELETEs under a single commit, so a failure on the second leaves the first applied inside an open transaction — both a partial mutation and a stranded lock.

AsyncSQLiteSession is the more damaging case. It holds one connection for the entire session (self._connection), so the stranded transaction holds the write lock until the session object is closed. Every later add_items from any writer — including other processes — then blocks or fails with database is locked. One transient OperationalError (disk full, a concurrent writer, a schema change) permanently wedges session persistence for that process.

Debug information

• Agents SDK version: 0.19.4 (reproduced on main at 8be468f3)
• Related library versions: aiosqlite
• Python version: 3.12
• Operating system: Windows 11 (not platform specific — this is SQLite transaction semantics)
• Model and model provider: none needed
• Does the issue reproduce with the latest Agents SDK release? Yes.
• Does the issue occur consistently or intermittently? Consistently and deterministically.

Repro steps

The script drops a table from an independent connection so that a statement inside the session's write fails, then checks whether the session's connection is still in a transaction and whether an independent writer can still take the write lock.

import asyncio
import sqlite3
import tempfile
from pathlib import Path

from agents import SQLiteSession
from agents.extensions.memory import AsyncSQLiteSession

ITEMS = [{"role": "user", "content": "m0"}]


def drop(db, table):
    c = sqlite3.connect(str(db))
    c.execute(f"DROP TABLE {table}")
    c.commit()
    c.close()


def write_lock_held(db):
    probe = sqlite3.connect(str(db), timeout=0)  # no busy handler
    try:
        probe.execute("CREATE TABLE probe (x INTEGER)")
        probe.commit()
        return False
    except sqlite3.OperationalError:
        return True
    finally:
        probe.close()


async def main():
    # AsyncSQLiteSession.clear_session: the messages DELETE succeeds, the sessions DELETE fails.
    db = Path(tempfile.mkdtemp()) / "a.db"
    s = AsyncSQLiteSession("sid", db_path=str(db))
    await s.add_items(ITEMS)
    drop(db, "agent_sessions")

    try:
        await s.clear_session()
    except sqlite3.OperationalError:
        pass

    conn = await s._get_connection()
    print("in_transaction:", conn.in_transaction)
    print("write lock held:", write_lock_held(db))


asyncio.run(main())

Actual behavior

in_transaction: True
write lock held: True

The session's only connection sits in an open write transaction. Every subsequent write through that session, and every write from any other connection or process, is blocked until the session is closed.

Running the same probe across all six methods:

SQLiteSession:
  add_items  (fixed in #4163)          open_txn=0 blocked=False
  pop_item                             open_txn=1 blocked=True
  clear_session (2nd DELETE fails)     open_txn=1 blocked=True

AsyncSQLiteSession:
  add_items                            in_transaction=True blocked=True
  pop_item                             in_transaction=True blocked=True
  clear_session (2nd DELETE fails)     in_transaction=True blocked=True

Expected behavior

A failed write rolls back, leaving no partial mutation and no open transaction, and the session stays usable — the behavior #4163 established for add_items. All six rows above should read blocked=False.

Root-cause hypothesis

(hypothesis) _locked_connection() yields a cached connection without managing transactions, so every write path has to roll back for itself. #4163 added that rollback inline to add_items only. Because the rollback obligation belongs to the connection rather than to any single method, a small _rollback_on_failure(conn) guard applied at each _locked_connection() write site would cover all of them from one place.

Proposed scope

Add that guard to both SQLite session modules and use it in every write path. No public API change, no schema change, and no change to which statements are committed — only the failure path differs.

I have a fix with regression tests ready and will open a PR referencing this issue.

[seratch]

Resolved by #4212