3636from sqlalchemy .ext .asyncio import AsyncEngine
3737from sqlalchemy .ext .asyncio import AsyncSession as DatabaseSessionFactory
3838from sqlalchemy .ext .asyncio import create_async_engine
39+ from sqlalchemy .orm .exc import StaleDataError # Import StaleDataError
3940from sqlalchemy .pool import StaticPool
4041from typing_extensions import override
4142
@@ -148,8 +149,10 @@ def __init__(self, db_url: str, **kwargs: Any):
148149 engine_kwargs .setdefault ("poolclass" , StaticPool )
149150 connect_args = dict (engine_kwargs .get ("connect_args" , {}))
150151 connect_args .setdefault ("check_same_thread" , False )
151- # Enforce SERIALIZABLE isolation for SQLite to prevent race conditions.
152- connect_args .setdefault ("isolation_level" , "SERIALIZABLE" )
152+ # For SQLite, use "IMMEDIATE" or "EXCLUSIVE" for stronger isolation,
153+ # as "SERIALIZABLE" is not supported. "IMMEDIATE" will begin a
154+ # transaction immediately, preventing other connections from writing.
155+ connect_args .setdefault ("isolation_level" , "IMMEDIATE" )
153156 engine_kwargs ["connect_args" ] = connect_args
154157 elif url .get_backend_name () != _SQLITE_DIALECT :
155158 engine_kwargs .setdefault ("pool_pre_ping" , True )
@@ -205,7 +208,7 @@ async def _rollback_on_exception_session(
205208 On normal exit the caller is responsible for committing; on any exception
206209 the transaction is explicitly rolled back before the error propagates,
207210 preventing connection-pool exhaustion from lingering invalid transactions.
208- """ <
211+ """
209212 async with self .database_session_factory () as sql_session :
210213 try :
211214 yield sql_session
@@ -369,16 +372,14 @@ async def create_session(
369372 if is_sqlite or is_postgresql :
370373 now = now .replace (tzinfo = None )
371374
372- # Initialize event_sequence for new sessions
373- session_state ["event_sequence" ] = 0
374-
375375 storage_session = schema .StorageSession (
376376 app_name = app_name ,
377377 user_id = user_id ,
378378 id = session_id ,
379379 state = session_state ,
380380 create_time = now ,
381381 update_time = now ,
382+ event_sequence = 0 , # Explicitly set for new sessions
382383 )
383384 sql_session .add (storage_session )
384385 await sql_session .commit ()
@@ -388,7 +389,7 @@ async def create_session(
388389 storage_app_state .state , storage_user_state .state , session_state
389390 )
390391 session = storage_session .to_session (
391- state = merged_state , is_sqlite = is_sqlite , event_sequence = 0
392+ state = merged_state , is_sqlite = is_sqlite , event_sequence = storage_session . event_sequence
392393 )
393394 return session
394395
@@ -443,7 +444,8 @@ async def get_session(
443444 app_state = storage_app_state .state if storage_app_state else {}
444445 user_state = storage_user_state .state if storage_user_state else {}
445446 session_state = storage_session .state
446- event_sequence = session_state .get ("event_sequence" , 0 )
447+ # event_sequence is now a direct attribute of storage_session
448+ event_sequence = storage_session .event_sequence
447449
448450 # Merge states
449451 merged_state = _merge_state (app_state , user_state , session_state )
@@ -502,7 +504,7 @@ async def list_sessions(
502504 user_state = user_states_map .get (storage_session .user_id , {})
503505 merged_state = _merge_state (app_state , user_state , session_state )
504506 # Pass event_sequence to to_session if it exists in state
505- event_sequence = session_state . get ( " event_sequence" , 0 )
507+ event_sequence = storage_session . event_sequence
506508 sessions .append (
507509 storage_session .to_session (state = merged_state , is_sqlite = is_sqlite , event_sequence = event_sequence )
508510 )
@@ -570,40 +572,15 @@ async def append_event(self, session: Session, event: Event) -> Event:
570572 if storage_session is None :
571573 raise ValueError (f"Session { session .id } not found." )
572574
573- # Get the current event_sequence from storage.
574- stored_event_sequence = storage_session .state .get ("event_sequence" , 0 )
575- next_event_sequence = stored_event_sequence + 1
576-
577- # Atomically update the event_sequence in the database.
578- # This update includes a WHERE clause that checks the current
579- # event_sequence, ensuring optimistic concurrency control.
580- # If another writer has already updated the session, the rowcount
581- # will be 0, indicating a conflict.
582- update_session_stmt = (
583- update (schema .StorageSession )
584- .where (
585- schema .StorageSession .app_name == session .app_name ,
586- schema .StorageSession .user_id == session .user_id ,
587- schema .StorageSession .id == session .id ,
588- schema .StorageSession .state ["event_sequence" ]
589- == stored_event_sequence ,
590- )
591- .values (
592- state = storage_session .state | {"event_sequence" : next_event_sequence }
593- )
594- )
595- update_result = await sql_session .execute (update_session_stmt )
575+ # When using version_id_col, SQLAlchemy automatically handles the
576+ # event_sequence increment and optimistic concurrency check during commit.
577+ # We just need to update the storage_session object's state and its
578+ # event_sequence (which will be incremented by SQLAlchemy upon flush/commit).
596579
597- if update_result .rowcount == 0 :
598- raise ValueError (
599- "The session has been modified by another writer. "
600- "Please reload the session before appending events."
601- )
602-
603- # Update the in-memory storage_session to reflect the database change
604- # This is important for subsequent ORM operations within the same session
605- # and for the final session.event_sequence update.
606- storage_session .state ["event_sequence" ] = next_event_sequence
580+ # Update event_sequence in the storage_session object before commit.
581+ # SQLAlchemy's version_id_col will use this value for the WHERE clause.
582+ # It will also increment it after a successful commit.
583+ storage_session .event_sequence = session .event_sequence
607584
608585 storage_app_state = await _select_required_state (
609586 sql_session = sql_session ,
@@ -644,10 +621,7 @@ async def append_event(self, session: Session, event: Event) -> Event:
644621 storage_user_state .state | state_deltas ["user" ]
645622 )
646623 if state_deltas ["session" ]:
647- # Note: storage_session.state["event_sequence"] was already updated
648- # by the explicit UPDATE statement above.
649- # We need to ensure other session state changes are merged.
650- # The | operator for dictionaries correctly merges.
624+ # Merge other session state changes.
651625 storage_session .state = (
652626 storage_session .state | state_deltas ["session" ]
653627 )
@@ -661,14 +635,22 @@ async def append_event(self, session: Session, event: Event) -> Event:
661635 storage_session .update_time = update_time
662636 sql_session .add (schema .StorageEvent .from_event (session , event ))
663637
664- await sql_session .commit ()
638+ try :
639+ await sql_session .commit ()
640+ # Update in-memory session's event_sequence after successful commit
641+ # SQLAlchemy's version_id_col increments storage_session.event_sequence
642+ # upon successful commit.
643+ session .event_sequence = storage_session .event_sequence
644+ except StaleDataError as e : # Catch StaleDataError for optimistic concurrency
645+ raise ValueError (
646+ "The session has been modified by another writer. "
647+ "Please reload the session before appending events."
648+ ) from e
665649
666650 # Update timestamp with commit time
667651 session .last_update_time = storage_session .get_update_timestamp (
668652 is_sqlite
669653 )
670- # Update in-memory session's event_sequence after successful commit
671- session .event_sequence = next_event_sequence
672654
673655 # Also update the in-memory session (handled by super().append_event)
674656 # This call might also update last_update_time, but event_sequence is explicitly handled now.
0 commit comments