Concurrency and 'FOR UPDATE' using 'with_for_update' on all SELECTS (`.query`) by default

[Jean Marcel Duvoisin Schmidt]

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?

Thanks!

[Mike Bayer]

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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jean Marcel Duvoisin Schmidt]

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.