[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
On Feb 12, 4:25 am, Arnar Birgisson [EMAIL PROTECTED] 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 ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
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 wrote: Hi all, 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Pymssql, or adodbapi? Client-side cursors, or Server-side? 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? On 2/8/07, Arnar Birgisson [EMAIL PROTECTED] wrote: Hi all, 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Pymssql runs on top of the ancient library DB-lib, which has real 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 [EMAIL PROTECTED] wrote: Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Ok. Should I rather be using adodbapi then? Arnar On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: Pymssql runs on top of the ancient library DB-lib, which has real 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 [EMAIL PROTECTED] wrote: Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
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). On 2/8/07, Arnar Birgisson [EMAIL PROTECTED] wrote: Ok. Should I rather be using adodbapi then? Arnar On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: Pymssql runs on top of the ancient library DB-lib, which has real 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 [EMAIL PROTECTED] wrote: Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Yeah sorry, I should have caught that. 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 [EMAIL PROTECTED] wrote: On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
