On Mon, Jan 22, 2018 at 9:53 AM, Jean Marcel Duvoisin Schmidt <[email protected]> wrote: > Hi everyone, > > We are facing a issue, and I'm looking for a solution. Let me first explain > what is going on, our problem that we are facing now is the classic > concurrent/lock problem. Some devs (you know, a friend of mine... ;P) wrote > code without worrying about row level locking and we ended up with a > production app full of concurrences. Being said that, the solution is not > complex, just adding the proper locks and redesign some code a little bit. > The problem is the amount of stuff that needs to be changed. We are doing > it, but our code is production-running and we are facing some really serious > issues. > > And then it got me thinking, what if was possible to overwrite all SELECT > statements to use `FOR UPDATE`, this will at least solve lots of issues we > are facing in production till we manage to refactor all the code. (Just for > clarity, I'm aware it will not solve all our issues). And then, I tough a > code like this:
> > ``` > class OurSessionClass(Session): > def query(self, *args, **kwargs): > return Session.query(self, *args, **kwargs).with_for_update() > > session_maker = sessionmaker(bind=my_engine, class_=OurSessionClass) > ``` > > For obvious reasons, this won't work, as we use aggregating functions on > many places in our apps. And now here comes the actual question: is there a > way to make SQLAlchemy automatically add `with_for_update` on all `SELECT` > statements that does not uses any `func.*` or any other type of aggregator > functions? I can't get my head around on how can it be done using SQLA. > > Does anyone can point me the direction where I should go to solve this > issue? that would be very aggressive locking to use everywhere. Sounds unusual that every single SELECT you have is causing concurrency problems, because if you lock on everything like that you greatly increase your risk of deadlocks which is not much better. you can alter the state of the Query object across the board using the before_compile event: http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.QueryEvents.before_compile @event.listens_for(Query, 'before_compile', retval=True) def receive_before_compile(query): query = query.with_for_update() return query but that is likely going to lead to problems, that will impact ALL selects, including lazy loads, refreshes, etc. this is not quite the same thing but maybe just using REPEATABLE READ or even SERIALIZABLE isolation is an immediate workaround to reduce concurrency conflicts. > > Thanks! > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
