We have to use the 'WITH (nolock)' because of legacy requirements; however, we will look into the isolation level comment and see what our DBAs say.
Oh, and thanks for the help Mike, we'll test that patch and see if it works and report back (along with anything we get on the isolation level). Thanks, Doug On Tue, Jun 28, 2011 at 12:39 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Jun 28, 2011, at 2:55 PM, Alexandre Conrad wrote: > > > I must have it wrong, I admit I don't quite understand the arguments > > of .with_hint() > > > > session.query(User).with_hint(User, 'WITH (nolock)').get(1) > > > > if that makes any sense (I wonder why I'd need to pass the User object > again). > > > additional info, per this SO answer: > > > http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements/210443#210443 > > NOLOCK is functionally equivalent to an isolation level of READ > UNCOMMITTED. The main difference is that you can use NOLOCK on some tables > but not others, if you choose. If you plan to use NOLOCK on all tables in a > complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED > is easier, because you don't have to apply the hint to every table. > > why not set transaction isolation level on the connection ? this is a lot > easier. A connection event can set that up on all connections. > > > > > > 2011/6/28 Michael Bayer <mike...@zzzcomputing.com>: > >> > >> On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote: > >> > >>> Hi list, > >>> > >>> So I am working on a project with SQLAlchemy using MSSQL as a back-end > >>> DB and I need to add a "WITH (nolock)" statement to all my queries > >>> implicitly. Even for the .get(pk_id) method. Mike actually pointed me > >>> to the .with_hint() method on the Query object but I couldn't get it > >>> to work. > >>> > >>> PS: I also noticed that there was a .with_lockmode() and I was > >>> wondering if that could help as this can be passed at the Session > >>> level, which implies that it will affect all queries (whatever this > >>> does). > >>> > >> > >> well with_hint() should add it but if you really want to be heavyhanded > you can override the compilation of sqlalchemy.expression.sql.Select to do > it, using the form described at: > >> > >> > http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs > >> > >> > >> > >> > >>> Thanks, > >>> -- > >>> Alex | twitter.com/alexconrad > >>> > >>> -- > >>> You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > >>> To post to this group, send email to sqlalchemy@googlegroups.com. > >>> To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > >>> For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > >>> > >> > >> -- > >> You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > >> For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > >> > >> > > > > > > > > -- > > Alex | twitter.com/alexconrad > > > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- Doug Morgan <http://about.me/doug.morgan> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
