[SQLObject] Re: Re: Concurrency issues...

[Chris G. <ch...@il...>]

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.