SQLAlchemy backend intermittently errors on concurrent requests

[markhobson]

When using the SQLAlchemy backend, concurrent requests can produce various SQLAlchemy errors.

See markhobson/flask-session-issue to reproduce. This simple Flask app configures Flask-SQLAlchemy with an in-memory SQLite database, and backs Flask-Session onto that. The single endpoint creates an HTTP session if one doesn't exist, and returns an HTML page with links to three stylesheet resources. The browser requesting these three assets concurrently can trigger various SQLAlchemy errors, such as:

127.0.0.1 - - [29/May/2024 18:03:50] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [29/May/2024 18:03:50] "GET /static/main1.css HTTP/1.1" 304 -
127.0.0.1 - - [29/May/2024 18:03:50] "GET /static/main2.css HTTP/1.1" 304 -
[2024-05-29 18:03:50,716] ERROR in _utils: Exception when querying database ((sqlite3.InterfaceError) bad parameter or other API misuse
[SQL: SELECT sessions.id AS sessions_id, sessions.session_id AS sessions_session_id, sessions.data AS sessions_data, sessions.expiry AS sessions_expiry 
FROM sessions 
WHERE sessions.session_id = ?
 LIMIT ? OFFSET ?]
[parameters: ('session:l9TjTgOemCbydDiy26XOF0Kwr4AwAqpuQNTKmi3VJm8', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/rvf5)).Retrying (1/3) in 0.30s.
Traceback (most recent call last):
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlite3.InterfaceError: bad parameter or other API misuse

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/flask_session/_utils.py", line 52, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/flask_session/sqlalchemy/sqlalchemy.py", line 138, in _retrieve_session_data
    record = self.sql_session_model.query.filter_by(session_id=store_id).first()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 2728, in first
    return self.limit(1)._iter().first()  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
                                                  ^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2236, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/mark/Projects/mark/flask-session-issue/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) bad parameter or other API misuse
[SQL: SELECT sessions.id AS sessions_id, sessions.session_id AS sessions_session_id, sessions.data AS sessions_data, sessions.expiry AS sessions_expiry 
FROM sessions 
WHERE sessions.session_id = ?
 LIMIT ? OFFSET ?]
[parameters: ('session:l9TjTgOemCbydDiy26XOF0Kwr4AwAqpuQNTKmi3VJm8', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/rvf5)

[markhobson]

This occurs with an in-memory SQLite database, but not a file-based SQLite database, nor a production-grade database like PostgreSQL. I appreciate that this is more of a SQLite issue, but it would be good to provide a better out-of-the-box experience for Flask-Session if possible.

[markhobson]

Note that #254 would mitigate this issue.