Yeah, I haven't tough about deadlocks, with is really a problematic situation, as there is no way to guarantee the right order for lock aquisition....
We are already planing a maintenance to put the SERIALIZABLE option live, at least till we get confident to have solved all issues, but thank you. I think i may be better for us if we focus on solving all issues we are facing as quick as possible instead of just making a 'quick dirty fix', as you ponted out, the risk of doing this update may not worth the benefits. Thanks for your support. On Monday, January 22, 2018 at 1:16:30 PM UTC-2, Mike Bayer wrote: > > On Mon, Jan 22, 2018 at 9:53 AM, Jean Marcel Duvoisin Schmidt > <[email protected] <javascript:>> 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
