WITH (nolock) on all queries

[Alexandre Conrad]

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

[Michael Bayer]

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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Alexandre Conrad]

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 <mik...@zzzcomputing.com>:

--
Alex | twitter.com/alexconrad

[Michael Bayer]

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.

[Doug Morgan]

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 <mik...@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.

--
Doug Morgan

[Michael Bayer]

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.

>