Hi,
> It seems SQLAlchemy uses the same session for all request because session
is created with same way we did in SQLAlchemy instance
no it creates a single *scoped* session, but that uses a thread local
variable to have a different session for each thread. Also I think flask
sqlalchemy will handle the creation / removal of the session before / after
each request.
What you are doing is instead creating a scoped session
> db.create_scoped_session(options={'bind': read_engine, 'expire_on_commit':
False})
but then you get a single session from it and use it in all the threads.
That's not supported
> read_session = session()
Here I would look how to declare local sessions for each request in flask,
so you get the same behaviour as the default session managed by flask
sqlalchemy
On Wednesday, 21 June 2023 at 14:31:43 UTC+2 Mike Bayer wrote:
>
>
> On Wed, Jun 21, 2023, at 7:13 AM, Ali Hopyar wrote:
>
> 0
> <https://stackoverflow.com/posts/76521666/timeline>
>
> We have an environment with the following libraries and versions;
> Python 3.8
> Flask 1.0.2
> Flask-SQLAlchemy 2.3.0
> SQLAlchemy 1.2.10
> PyMySQL 1.0.2
>
>
> Normally, we run on a single RDS Mysql but because of the high load we
> needed to add a read replica and we have following configurations;
> SQLALCHEMY_DATABASE_URI = //DB URL
> SQLALCHEMY_ECHO = False
> SQLALCHEMY_TRACK_MODIFICATIONS = False
> SQLALCHEMY_POOL_RECYCLE = 60
> SQLALCHEMY_POOL_SIZE = 10 SQLALCHEMY_MAX_OVERFLOW = 30 # Default to 10
> SQLALCHEMY_POOL_TIMEOUT = 60 # Default to 30
> SQLALCHEMY_BINDS = { 'read': Read Db URL }
>
>
> Before creating the read replica we would use db and session like;
> from flask_sqlalchemy import SQLAlchemy
> db = SQLAlchemy(session_options={"expire_on_commit": False})
> db.session.query(MyEntity).filter_by(id=self.id).all()
>
>
> After creating the read replica we have added a read_session like;
> read_engine = db.get_engine(bind='read')
> session = db.create_scoped_session(options={'bind': read_engine,
> 'expire_on_commit': False})
> read_session = session()
>
>
> and started use it like;
> read_session.query(MyEntity).filter_by(id=self.id).all()
>
>
> but after this point, our application started to produce errors like Packet
> sequence number wrong - got x but expected y and Can't reconnect until
> invalid transaction is rolled back .
>
> When I googled the error, I see that the reason of this error is
> multi-threading and it says every request should be handled in separate
> sessions but I wonder how it would work before creating the read replica.
> It seems SQLAlchemy uses the same session for all request because session
> is created with same way we did in SQLAlchemy instance. Related code in
> SQLAlchemy =>
> def __init__(self, app=None, use_native_unicode=True, session_options=None,
> metadata=None, query_class=BaseQuery, model_class=Model):
> self.use_native_unicode = use_native_unicode
> self.Query = query_class
> self.session = self.create_scoped_session(session_options) # here is
> how session created in SQL Alchemy
> self.Model = self.make_declarative_base(model_class, metadata)
> self._engine_lock = Lock()
> self.app = app _include_sqlalchemy(self, query_class)
>
> if app is not None:
> self.init_app(app)
>
> I wonder, what we did wrong and how we can create a session to use every
> where like SQLAlchemy did it?
>
>
> it looks like you are using Flask session mechanics here, which we don't
> support on this end. I'm not sure what their approach to concurrency is so
> you will want to ask on their discussion tracker / list as to what patterns
> should be in place.
>
> if you are actually on SQLAlchemy 1.2 I would also advise incrementally
> moving up your versions to 1.3, 1.4, 2.0. things are going to work more
> and more poorly on a version that old as other libraries and platforms
> continue to modernize around it.
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/73c9991e-3011-4ed2-95da-33a3c16fe56an%40googlegroups.com.
