Adding "with (nolock)" to all queries (mssql)
Arnar Birgisson
I'm using SA to build queries (no orm) and handle connection pooling
on a linux machine, connecting to mssql via freetds.
This was working very well with SQL Server 2000, but a couple of weeks
ago we upgraded to 2005. Since then the SA app has been having some
problems. I've checked that I'm using the latest (0.64) version of
freetds - which was released to provide support for sql server 2005.
Now, I won't go into the specific problems (mainly b/c I'm not sure
what's going on) but my dba suggested that I add "with (nolock)" after
each table alias in every query (I'm only doing selects). The problem
is that the queries are built and executed with SA. Can I plug into
some hooks or something to do this?
Arnar
Rick Morrison
We use pymssql here over FreeTDS with SQL Server 2005 here without
issues. If your DBA suggests "with (nolock)", I'm assuming that you're
seeing some sort of persistent table/page locks?
Can you give a bit more info?
Arnar Birgisson
On 2/8/07, Rick Morrison <rickmo...@gmail.com> wrote:
> Pymssql, or adodbapi? Client-side cursors, or Server-side?
Pymssql and client side cursors (I guess, I'm just issuing plain
select statements, no stored procs or such).
> We use pymssql here over FreeTDS with SQL Server 2005 here without
> issues. If your DBA suggests "with (nolock)", I'm assuming that you're
> seeing some sort of persistent table/page locks?
>
> Can you give a bit more info?
I'm trying to analyze the problem right now, but I'm afraid I don't
have alot of info. We were having locking issues in other systems
running of the same db server which were solved (partially at least)
by adding nolock hints to datawarehousing procedures.
The symptom on the SA side is that this seems to happen after the
server (apache/mod_python) has been running for a while. A user (and
always the same two out of ~40) call me and says his page is empty -
meaning that the main query is giving 0 result rows when it should be
giving >300. I log in with my name and everything is fine - I log in
with his and I see the empty screen. If I restart apache it fixes the
problem for a while.
I don't see any errors or tracebacks in the apache error_log, but I
have yet to enable more logging since this only happens on the
production server and quite sporadically.
This has some pre-history. I was initially running this in cherrypy
which was simply not working at all. DB connections would lock up
until there were no left in the pool and this happend quite fast, in
about 20 minutes. I moved the part of the application that speaks to
mssql over to mod_python/apache and that solved the problem while we
were running on sql server 2000.
I'll try to analyze this some more and get back to you.. thanks for the help.
Arnar
Rick Morrison
issues with not fully reading all query results -- make sure all your
query results are fully consumed, i.e. read them into a list and the
iterate that instead of partially iterating the cursor.
On 2/8/07, Arnar Birgisson <arn...@gmail.com> wrote:
>
Arnar Birgisson
Arnar
Rick Morrison
as I know, adodbapi is Windows-only (anyway, it has its own set of
idiosyncrasies too).
Arnar Birgisson
>
> If you're using FreeTDS, I assume you're on some kind of Unix. As far
> as I know, adodbapi is Windows-only (anyway, it has its own set of
> idiosyncrasies too).
Yes, Linux. I mentioned that in the first post so I deducted from your
question that I had a choice :o)
Anyways, freetds has always been a pain in my ... neck, I've moved a
few projects over to windows servers.
Let's say that I do that, should I use pymssql or adodbapi?
Arnar
Rick Morrison
On the pymssql vs. adodbapi, the answer is not an easy question, and
is quite a mixed bag.
MS supports ADO (at least for now), and no longer supports DB-Lib, so
from that standpoint adodbapi would be the way to go, at least as
regards the toolchain from the DB-API to the SQL server. On the other
hand, I have heard that the adodbapi project has been unresponsive as
of late.
Pymssql seems to be pretty reliable once set up correctly, but of
course relies on the now-unsupported DB-lib, and has the
aforementioned issues with partial result reads.
All told, I would use adodbapi on Windows, and pymssql on *nix.
I have my eye on the pyodbc project, that might end up being the "one
best way" for MS-SQL connections, and would open up other ODBC
compliant data sources as well. Course that won't help you in the
near-term....
Rick
On 2/8/07, Arnar Birgisson <arn...@gmail.com> wrote:
>
Michael Bayer
before I read the next 10 replies in this thread, this seems like an issue
of too high of a transaction isolation level going on (something that
plagued my SQL server apps when i was writing them, though that was years
ago). cant the connection itself have a more lenient isolation level set
?
Arnar Birgisson
Yes, we thought that might be the case. We tried changing the
isolation level on this connection to "snapshot". Now I'm getting
different errors, and more frequent.
One error I get is this:
Mod_python error: "PythonHandler jobtracking.api.controller"
Traceback (most recent call last):
File "/usr/lib/python2.4/site-packages/mod_python/apache.py", line
299, in HandlerDispatch
result = object(req)
File "/usr/lib/python2.4/site-packages/jobtracking-1.0_r20-py2.4.egg/jobtracking/api/controller.py"
, line 30, in handler
visit = s.query(VisitIdentity).get_by(visit_key=visit_key.value)
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 95, in get_by
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 255,
in select_whereclause
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 458,
in compile
File "build/bdist.linux-i686/egg/sqlalchemy/orm/interfaces.py", line
75, in setup
File "build/bdist.linux-i686/egg/sqlalchemy/orm/interfaces.py", line
64, in _get_context_strategy
AttributeError: 'ColumnProperty' object has no attribute 'strategy'
and another error is "object not subscriptable" when doing something
like "eststatus = result.fetchone()[0]"
Any ideas?
Arnar
TypeError: unsubscriptable object
Michael Bayer
On Feb 12, 4:25 am, "Arnar Birgisson" <arna...@gmail.com> wrote:
> Hi again,
>
> Yes, we thought that might be the case. We tried changing the
> isolation level on this connection to "snapshot". Now I'm getting
> different errors, and more frequent.
>
> One error I get is this:
>
> AttributeError: 'ColumnProperty' object has no attribute 'strategy'
that should have nothing to do with transaction isolation. thats some
mixup of column/relation-based properties on a class (quite a strange
one).
>
> and another error is "object not subscriptable" when doing something
> like "eststatus = result.fetchone()[0]"
how do you know fetchone() is returning a row ?
Arnar Birgisson
On 2/12/07, Michael Bayer <zzz...@gmail.com> wrote:
> On Feb 12, 4:25 am, "Arnar Birgisson" <arna...@gmail.com> wrote:
> > Hi again,
> >
> > Yes, we thought that might be the case. We tried changing the
> > isolation level on this connection to "snapshot". Now I'm getting
> > different errors, and more frequent.
> >
> > One error I get is this:
> >
> > AttributeError: 'ColumnProperty' object has no attribute 'strategy'
>
> that should have nothing to do with transaction isolation. thats some
> mixup of column/relation-based properties on a class (quite a strange
> one).
I'm not using any orm here, just building queries... is this a problem
on my end or in SA?
> >
> > and another error is "object not subscriptable" when doing something
> > like "eststatus = result.fetchone()[0]"
>
> how do you know fetchone() is returning a row ?
Uhm.. maybe it's not. But if I hit refresh (this is a webapp) I don't
get the error. The data is not changing wrt to this so I should be
seeing a consistent result.
However, this might be a symptom of the same problem I was having
before - i.e. no rows being returned where there should be some.
Arnar