How to specify NOLOCK queries in SA

[BruceC]

Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL
Server 2005 db via PYODBC. I'm getting a lot of database locks, & the
statements appear to be like this:

SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF

I'm assuming that this is getting information about the model in
preparation for another query (perhaps an insert). It returns the
relevant column name, but no data , as 1 can obviously never equal 2.

I would like to explicitly make this a NOLOCK select, to prevent these
queries from generating locks, as these locks appear to be impacting
our application performance & server stability. Can anyone point me in
the direction of where such a change could be made?

[Michael Bayer]

On May 13, 2008, at 9:46 PM, BruceC wrote:

>
> Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL
> Server 2005 db via PYODBC. I'm getting a lot of database locks, & the
> statements appear to be like this:
>
> SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF

I cant locate the string "FMTONLY" within the MSSQL dialect at all.
So this is not something SQLA is issuing.

>
> I would like to explicitly make this a NOLOCK select, to prevent these
> queries from generating locks, as these locks appear to be impacting
> our application performance & server stability. Can anyone point me in
> the direction of where such a change could be made?

our MSSQL devs might be able to help here but also you might want to
see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA
is not issuing any locking.

[BruceC]

Thanks for your speedy response Michael :)

Yes, after more searching it does rather look like this is an issue
that has to do with MSSQL, rather than SQLA. This post (http://
forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896&SiteID=1) seems
to point to a similar opinion, & points the finger at db connector
parameters.

For anyone else who reads this looking for an answer to their own
issue, so you know, we have decided to make changes to our database
configuration options to remove the default locking of records during
a query, as that looks like it may resolve our particular issue. We
have the luxury of not having our data used in a way that requires
locking. This approach may not be appropriate for other users, but it
does suit our needs, so we'll try this out...

[BruceC]

Just to be a bit more specific, these are the changes we will try in
our mssql db:

ALTER DATABASE MyDBName
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDBName
SET READ_COMMITTED_SNAPSHOT ON

:)

[Rick Morrison]

Hi Bruce,

I'm considering a switch from pymssql to pyodbc myself in the not-too-distance future, and this thread has me a bit curious about what's going on. This is a subject that may affect SQL more in the future when ODBC and JDBC drivers get more use.

I think there's two distinct questions that need to be answered to get to the bottom of this. The first question is "why are these queries being issued at all, and from where"? Like Mike says, SQLA is playing no part in constructing or issuing these queries.

From the bit of googling that I've done so far, it seems that the FMTONLY queries are issued behind the scenes by the data connector to fetch metadata regarding the query. While there's a lot of reasons a data connector might need to have metadata, there's two that seem especially likely when SQLA comes into play:

   a) There are un-typed bind parameters in the query, and the connector needs to know the data types for some reason.

   b) There is going to be a client-side cursor constructed, and result metadata is needed to allocate the cursor. From the description you give, I would bet that this is your main issue.

If the cause is (a), a fix might be problematic, as SQLA issues all of its queries using bind parameters, and I'm not sure if type information is used for each. But if you're using explicit bind parameters, you may want to specify the type on those.

As for the more likely cause (b) I would think this could be gotten around by making sure you specify "firehose" (read-only, forward-processing, non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc settings for this might be. As a bonus, you'll probably see a bit of a performance boost using these types of cursors as well.


The second question is more of a mystery to me: "ok, so the data connector issues a FMTONLY query........if it's just fetching metadata, why would that cause database locks?".

This one I can't figure out. Unless you're calling stored procedures or UDF's that have locking side effects, It's got to be a bug in the data connector.  From what I read a FMTONLY query should be pretty fast (other than the round-trip network time), and should lock nothing.

Are you running on Windows, or on Unix? What's your ODBC connector?

Please post to the list as you work through this and let us know what you find...

Rick

[BruceC]

Hi Rick,

This could involve quite a bit of investigation on our part, so it
could take a while before we get to the bottom of it.

We're running Windows 2003 as the OS, MS SQL Server 2005, via the SQL
Server Native Driver, using PYODBC, Apache 2.2.6 & mod_python 3.3.1.
The app is based on Pylons, using SQLAlchemy-0.4.4dev_r3557-py2.5 &
Elixir-0.5.0dev_r2495-py2.5.

Now for what it's worth, we made the change I mentioned above to our
db. Before the change, we were getting 360000 maximum concurrent locks
in a day. after the change, that figure went down to 400. It also
reduced the load on our CPUs by about 10%. So it helped. We will be
doing more examination of what's happening with cursors next
week...I'll keep you posted. :)