Re: [SQLObject] SQLite threadsafety?

[Ian B. <ia...@co...>]

On Wednesday, October 8, 2003, at 03:50 PM, Luke Opperman wrote:
> Hello -
>
> Finally had need to use SQLObject with something besides Postgres, 
> this time
> SQLite (also, MSAccess vi mx.ODBC, but meh).
>
> Quick question though: running into problems with multiple threads 
> (Webware
> servlets) hitting the db at the same time. This is on a single-user 
> install
> hence SQLite, but some of the pages make multiple requests to Webware 
> at once.
>
> Looking through it all, the pysqlite.sf.net webpage and my copy of the 
> code
> (rev1.17) both show a threadsafety of 1, and only supports one 
> concurrent
> cursor/query per connection. The SQLObject DBConnection code though 
> assumes
> better threadsafety, sharing a single connection. Is there a newer 
> version of
> SQLite that I'm just not finding, or is the SQLObject code wrong?

MySQL is also 1, and some Postgres adapters.  SQLObject should work 
with that level.  But...

> (The problem manifests itself because there is just a connection-level 
> flag for
> transactions in pysqlite, shared by all cursors of the connection. So 
> BEGIN
> and COMMIT calls start getting out of sync, failing at the sqlite 
> level.)

Hmm... well, there's a couple possibilities.

One is that SQLite doesn't have good concurrency support.  I should 
look more closely at this, but you may specifically encounter problems 
with using the .select() iterators, which hold a cursor open -- if you 
do another query at the same time weird stuff can happen.  You can use 
list() to force the iterator to flush.

Another possibility is that it's not doing autocommit.  If you aren't 
explicitly using transactions everything should be automatically 
committed, and a connection isn't reused until after the last query on 
that connection was committed.  Only if you are using transactions 
should you require an explicit commit (and even then, when the 
Transaction is disposed of and the connection returned a commit or 
rollback should occur).

Another possibility is you encountered a transaction bug.  Huh... now 
that I think about it someone sent me a bug about that just recently, 
but I think I was busy at that moment and forgot it.  (I don't forget 
SF bugs, but there isn't one) -- anyway, I made a bunch of fixes to 
transactions, but I might have missed something, and your transaction 
might accidentally be getting a connection from the pool when it should 
be using the connection associated with your transaction.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org