[Python-Dev] SQLite module for Python 2.5

[Bob Ippolito]

On Oct 21, 2004, at 10:23, Skip Montanaro wrote:

>
>>> By including PySQLite we'd somehow be blessing it as a better SQL
>>> solution than the other options.  That means it will almost certainly
>>> be stretched beyond its limits and used in situations where it isn't
>>> appropriate (multiple writers, writers that hold the database for a
>>> long time, etc).  That will reflect badly on both SQLite and Python.
>
>     Bob> I can't imagine how that rather contrived scenario could 
> reflect
>     Bob> badly on Python or SQLite.
>
> You assume it was contrived, but it wasn't at all.  We hit exactly 
> these
> problems almost upon first use.  We were in the process of copying a 
> large
> amount of data from our corporate Sybase database.  Because SQLite's 
> lock
> granularity is the entire file, the SQLite database was unusable until 
> the
> entire update process was complete, even though many tables were 
> completely
> updated long before the update process finished.  We also encountered a
> major performance problem almost immediately.  It seems that using 
> BETWEEN
> is much worse (order of magnitude worse) than two comparison clauses 
> using
>> =, <, etc.
>
> We are in the process of deciding which server-based SQL solution to 
> move
> to.

The concurrency problem makes it sound like you were using SQLite 2.x, 
not SQLite 3.x.  If it was SQLite 3.x, then you could've used separate 
files for each table:

"""
A limited form of table-level locking is now also available in SQLite. 
If each table is stored in a separate database file, those separate 
files can be attached to the main database (using the ATTACH command) 
and the combined databases will function as one. But locks will only be 
acquired on individual files as needed. So if you redefine "database" 
to mean two or more database files, then it is entirely possible for 
two processes to be writing to the same database at the same time. To 
further support this capability, commits of transactions involving two 
or more ATTACHed database are now atomic.
"""
( from http://www.sqlite.org/version3.html -- see also 
http://www.sqlite.org/lockingv3.html )

-bob