[sqlalchemy] WITH (nolock) on all queries
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). 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.
Re: [sqlalchemy] WITH (nolock) on all queries
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.
Re: [sqlalchemy] WITH (nolock) on all queries
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). 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.
Re: [sqlalchemy] WITH (nolock) on all queries
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). well HINT is a construct that on some backends is given per table like sybase, so that's why it accepts the entity. SQL server dialect doesn't have hints implemented, this patch will do it, what version are you on ? diff -r 223fc8419706 lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Mon Jun 27 19:25:35 2011 -0400 +++ b/lib/sqlalchemy/dialects/mssql/base.py Tue Jun 28 15:18:54 2011 -0400 @@ -766,6 +766,9 @@ return s return compiler.SQLCompiler.get_select_precolumns(self, select) +def get_from_hint_text(self, text): +return text + def limit_clause(self, select): # Limit in mssql is after the select keyword return I'm looking at some examples of WITH (nolock) and it appears to work the same way, the directive is given per table. with_hint() doesn't necessarily know that the query is against just one entity, it is not sophisticated enough to check for that right now. -- 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.
Re: [sqlalchemy] WITH (nolock) on all queries
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. -- 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.
Re: [sqlalchemy] WITH (nolock) on all queries
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.comwrote: 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.
