Thread: [SQLObject] Concurrency issues...
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Chris G. <ch...@il...> - 2004-03-21 12:18:49
|
Hey there. I'm having a big problem, and I need to find away around it,
otherwise I don't think I can use SQLObject. :)
It's a problem that was brought up a couple weeks ago. If I hammer SQLObject
with multiple threads all doing queries simultaneously, it'll either bomb
out by giving me an exception, or permanently lock up one of the threads,
and sometimes the entire python interpreter.
The problem was supposedly fixed with the new Iteration() class, but I don't
think it got to the root of the problem.
This weirdness happens with Postgres and MySQL drivers (the exceptions they
throw are slightly different, but their meanings are similar). It also
happens in SQLObject 0.5.2 and 0.6 (the latest SVN).
Here are the results of a program that hammers SQLObject (source attached).
It creates a simple database of 49+1 people, then spawns a bunch of
'whacker' threads which each repeatedly select all the records and iterate
over them.
---8<---8<---8<------S---N---I---P------>8--->8--->8---
$ python sqlobject_hammer.py
Making peeps...
--------------------------------------------------
49 peeps created...
WHACKING!!!
==================================================
whacker #0 | iteration: 1 (read 49 records)
whacker #0 | iteration: 2 (read 49 records)
whacker #1 | iteration: 1 (read 49 records)
whacker #0 | iteration: 3 (read 49 records)
whacker #0 | iteration: 4 (read 49 records)
whacker #0 | iteration: 5 (read 49 records)
whacker #0 | iteration: 6 (read 49 records)
whacker #1 | iteration: 2 (read 49 records)
whacker #0 | iteration: 7 (read 49 records)
whacker #1 | iteration: 3 (read 49 records)
whacker #0 | iteration: 8 (read 49 records)
whacker #1 | iteration: 4 (read 49 records)
whacker #1 | iteration: 5 (read 49 records)
whacker #1 | iteration: 6 (read 49 records)
whacker #2 | iteration: 1 (read 49 records)
whacker #0 | iteration: 9 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
File "sqlobject_hammer.py", line 33, in whackit
del peep
UnboundLocalError: local variable 'peep' referenced before assignment
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
File "sqlobject_hammer.py", line 29, in whackit
for count, peep in enumerate(peeps):
File "/usr/lib/python2.3/site-packages/sqlobject/main.py", line 1192, in
__iter__
return conn.iterSelect(self)
File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
199, in iterSelect
select, keepConnection=False)
File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
393, in __init__
self.cursor.execute(self.query)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
return self._execute(query, args)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for long(): w1
whacker #2 | iteration: 2 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
File "sqlobject_hammer.py", line 33, in whackit
del peep
UnboundLocalError: local variable 'peep' referenced before assignment
whacker #1 | iteration: 7 (read 49 records)
Unhandled exception in thread started by <function whackit at 0x40459d84>
Traceback (most recent call last):
File "sqlobject_hammer.py", line 29, in whackit
for count, peep in enumerate(peeps):
File "/usr/lib/python2.3/site-packages/sqlobject/main.py", line 1192, in
__iter__
return conn.iterSelect(self)
File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
199, in iterSelect
select, keepConnection=False)
File "/usr/lib/python2.3/site-packages/sqlobject/dbconnection.py", line
393, in __init__
self.cursor.execute(self.query)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
return self._execute(query, args)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33,
in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; You can't
run this command now")
Traceback (most recent call last):
File "sqlobject_hammer.py", line 49, in ?
main()
File "sqlobject_hammer.py", line 46, in main
pass
--->8--->8--->8------S---N---I---P------8<---8<---8<---
It just locked up after that.
I'm not really sure how to start debugging this. I'm a little scared of
DBConnection.py. :)
= Chris Gahan =============
(ch...@il...)
begin 666 sqlobject_hammer.py
M9G)O;2!S<6QO8FIE8W0@:6UP;W)T("H*7U]C;VYN96-T:6]N7U\@/2 G;7ES
M<6PZ+R]T97-T.G1E<W1 ;&]C86QH;W-T+W1E<W0G"FEM<&]R="!R86YD;VT*
M"FYU;7!E97!S(#T@-3 *;G5M=VAA8VME<G,@/2 T"@IC;&%S<R!0965P<RA3
M44Q/8FIE8W0I.@H@(&YA;64@/2!3=')I;F=#;VPH9&5F875L=#TB2&%N:S @
M3R=-86QL97DB*0H@(&%G92 ]($EN=$-O;"AD969A=6QT/3$P*0H*9&5F(&UA
M:V5P965P<R@I.@H@('!R:6YT(")-86MI;F<@<&5E<',N+BXB"B @<')I;G0@
M)RTG*C4P"B @4&5E<',N9')O<%1A8FQE*&EF17AI<W1S/51R=64I"B @4&5E
M<',N8W)E871E5&%B;&4H*0H@(&9O<B!I(&EN(')A;F=E*# L;G5M<&5E<',I
M.@H@(" @<&5E<" ](%!E97!S*&YA;64](DAA;FLE9"!/)TUA;&QE>2(E<F%N
M9&]M+G)A;F1I;G0H,2PY.3DI+ H@(" @(" @(" @(" @(" @(&%G93UR86YD
M;VTN<F%N9&EN="@Q+#4P,"DI"B @"B @<')I;G0@(B5D('!E97!S(&-R96%T
M960N+BXB("4@:0H*"F1E9B!W:&%C:VET*&ED*3H*(" @('0@/2 P"B @("!W
M:&EL92!T(#P@-3 P.@H@(" @(" @('0@*ST@,0H@(" @(" @('!E97!S(#T@
M4&5E<',N<V5L96-T*"D*(" @(" @("!F;W(@8V]U;G0L('!E97 @:6X@96YU
M;65R871E*'!E97!S*3H*(" @(" @(" @(" @<&%S<PH@(" @(" @('!R:6YT
M(")W:&%C:V5R(",E9"!\(&ET97)A=&EO;CH@)60@*')E860@)60@<F5C;W)D
M<RDB("4@*&ED+"!T+"!C;W5N="D*(" @(" @("!D96P@<&5E<',*(" @(" @
M("!D96P@<&5E< H@(" *9&5F(&UA:6XH*3H*(" @(&EM<&]R="!T:')E860*
M(" @(&UA:V5P965P<R@I"@H@(" @<')I;G0*(" @('!R:6YT(")72$%#2TE.
M1R$A(2(*(" @('!R:6YT("(](BHU, H*(" @(&9O<B!T(&EN(')A;F=E*&YU
M;7=H86-K97)S*3H*(" @(" @("!T:')E860N<W1A<G1?;F5W7W1H<F5A9"AW
M:&%C:VET+" H="PI*0H@(" @=VAI;&4@+3$Z"B @(" @(" @<&%S<PH@(" @
H"FEF(%]?;F%M95]?(#T]("=?7VUA:6Y?7R<Z"B @("!M86EN*"D*"@``
`
end
|
|
From: Chris G. <ch...@il...> - 2004-03-23 07:32:01
|
"Chris Gahan" <ch...@il...> wrote in message news:c3k162$stb$1...@se...... > [...] If I hammer SQLObject > with multiple threads all doing queries simultaneously, it'll either bomb > out by giving me an exception, or permanently lock up one of the threads, > and sometimes the entire python interpreter. I've been experimenting with DBConnection.py a bit, and I still can't figure out what's causing this thread synchronization error. The code all looks kosher: + take and hold a connection from the pool + execute a query + iterate through the results using the connection's cursor + when the "sqlresult" object gets deleted, return the connection to the pool I'm still randomly getting a "Commands out of sync" error in MySQL, or an "OperationalError" exception in Postgres. The problem still seems to be that another process is getting a hold of a connection which contains a cursor that hasn't finished iterating through its results. I really need somebody to help me brainstorm causes here, because this isn't my area of expertise. :) Can anybody suggest why MySQL would think I was executing an "out-of-sync" command? Could it be that the cursor needs to be "closed" before being returned to the DB? Maybe the object is getting deleted mid-iteration, and a connection containing partially-iterated cursor is being returned to the pool? Any help at all is appreciated. :) = Chris Gahan ============= (ch...@il...) |
|
From: Ian B. <ia...@co...> - 2004-03-23 16:52:34
|
On Mar 23, 2004, at 2:31 AM, Chris Gahan wrote: > Can anybody suggest why MySQL would think I was executing an > "out-of-sync" > command? Could it be that the cursor needs to be "closed" before being > returned to the DB? Maybe the object is getting deleted mid-iteration, > and a > connection containing partially-iterated cursor is being returned to > the > pool? Have you run the problem code with debugging on? The output should list connection IDs along with the commands, so you should be able to see if there's something out of sync. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
|
From: Chris G. <ch...@il...> - 2004-03-29 23:55:34
|
Ian Bicking <ia...@co...> wrote in news:7916C560-7CEA-11D8-9093- 000...@co...: > On Mar 23, 2004, at 2:31 AM, Chris Gahan wrote: >> Can anybody suggest why MySQL would think I was executing an >> "out-of-sync" command? Could it be that the cursor needs to be >> "closed" before being returned to the DB? Maybe the object is getting >> deleted mid-iteration, and a connection containing partially- >> iterated cursor is being returned to the pool? > > Have you run the problem code with debugging on? The output should > list connection IDs along with the commands, so you should be able to > see if there's something out of sync. I have, actually, but it doesn't yield much insight. It's exactly what you'd figure was happening from looking at the code: queries get executed on the connections, then you suddenly get a database exception when one thread tries to execute a query on a connection that was just returned to the pool. I think the pool needs some debugging messages if we want to figure out what's happening. I'm pretty sure the exception is caused by the state of the underlying conneciton to the database for the pooled connection. Maybe the last command isn't getting committed before the connection gets put back into the pool, or maybe the cursor isn't getting closed... or maybe the cursor being closed needs to be committed?!? :) I dunno. I'm not too familiar with Python DB stuff. One issue that this whole debacle has brought to my attention, however, is the design of DBAPI. It's not very flexible, and I think it could be improved. Right now, the pooling code (in the DBAPI class) and the database- abstraction layer (in the *Connection classes) are all bundled together in a single class, and I think they should be separated. Why don't we simplify the *Connection classes so that they're only responsible for creating connections to the DB and executing SQL commands. This would make it easier to handle exceptions (like "connection timed out", which currently kills your program if it happens). The connection object could catch that specific exception, and try to re-establish the connection, allowing the calling function to continue on its merry way! :D Then, separating the pool out could be done by creating a "ConnectionFactory" class. It could be subclassed depending on the behaviour you wanted. To use it, you'd just create an instance of it and assign it to SQLObject's _connection attribute. Default factories could be: SimpleConnectionFactory (which just creates a new connection every time, and if too many connections are open, it'll wait until some connections got closed before returning new connections), and of course, the PooledConnectionFactory. Then, SQLObject could also include (or people could write their own) special-purpose factories. For example: a factory that always returns the same connection to a specific Webware servlet, effectively giving each servlet its own DB connection (you could implement it by instantiating the factory with the servlet's .__hash__() as a parameter, or something). What do you think? I'd be willing to help overhaul it if you think it's worthwhile. The main reasons I can see for doing this is to make both the *Connection and pooling code simpler and more robust. |
|
From: Ian B. <ia...@co...> - 2004-04-04 21:53:16
|
I just wanted to reply to some of the things you brought up in general design terms. On Mar 29, 2004, at 5:55 PM, Chris Gahan wrote: > One issue that this whole debacle has brought to my attention, however, > is the design of DBAPI. It's not very flexible, and I think it could be > improved. First, no arguments here. Well, it's reasonably flexible for what it does, which is abstracting out database-specific issues. It could use more factoring, including moving things like pooling into separate, generic modules. > Right now, the pooling code (in the DBAPI class) and the database- > abstraction layer (in the *Connection classes) are all bundled together > in a single class, and I think they should be separated. > > Why don't we simplify the *Connection classes so that they're only > responsible for creating connections to the DB and executing SQL > commands. This would make it easier to handle exceptions (like > "connection timed out", which currently kills your program if it > happens). The connection object could catch that specific exception, > and > try to re-establish the connection, allowing the calling function to > continue on its merry way! :D > > Then, separating the pool out could be done by creating a > "ConnectionFactory" class. It could be subclassed depending on the > behaviour you wanted. To use it, you'd just create an instance of it > and > assign it to SQLObject's _connection attribute. Oh no, not factories! > Default factories could be: SimpleConnectionFactory (which just > creates a > new connection every time, and if too many connections are open, it'll > wait until some connections got closed before returning new > connections), > and of course, the PooledConnectionFactory. Oh no, not subclasses! > Then, SQLObject could also include (or people could write their own) > special-purpose factories. For example: a factory that always returns > the > same connection to a specific Webware servlet, effectively giving each > servlet its own DB connection (you could implement it by instantiating > the factory with the servlet's .__hash__() as a parameter, or > something). Taking the particular issue that spawned this discussion, the problem was a bug, not a lack of potential for customization. It can certainly be argued that the bug was more likely to exist because of the coupled nature of the code involved. But the solution was to fix it, it definitely *wasn't* to make it possible to customize the code so as to avoid the bug. And I think that's what would happen in this model, where people would be carrying around their own customizations to the code, each person with their own problematic corner cases. I want SQLObject to work correctly without too much effort. If there's weird issues in some case, I think SQLObject should try to do the right thing for everyone. In most cases this is quite possible. If it's not possible, we should try to look for the sensible set of choices that we should support -- not every possible choice, but just the ones that have real use cases. Then we should distill that into some sort of option, something that can be easily explained and documented, including descriptions of when you will care about the option and how you should choose. I don't want to make SQLObject flexible enough that you simply give the user the tools to fix the problem on their own -- I think that's burdensome. (It brings to mind this article: http://www.linuxworld.com/story/44251.htm) And of course, the typical option should be the one that requires no thought at all from the user -- i.e., the default. Unless the typical option would be broken for some situation, particularly broken in a way that causes bad bugs (e.g., lost data, incorrect data, concurrency issues, etc) -- in that case we have to force the user to make an explicit choice, and do what we can to make that bug more explicit (which might just mean putting in an assert statement). So from this perspective, I'm definitely opposed to encouraging lots of subclasses and factories. I am enthusiastic about robust, explicit, documented modules (just documented in the docstring, nothing fancy), and I'm very open to factoring classes like DBAPI into several modules. That still leaves open the issue of passing in parameters to these 'hidden' modules and classes, such as if you want to control the pool, or one of the caches, which are usually created by SQLObject on behalf of the user. I'm open to suggestions -- but the current situation where the SQLObject or DBConnection class has to know what options to pass through isn't that bad (even though it does imply some coupling, in that everytime you add an option to the cache or pool object you'll have to update the SQLObject or DBConnection class). -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X