[Python-Dev] SQLite module for Python 2.5 (original) (raw)

Bob Ippolito bob at redivi.com
Thu Oct 21 16:32:38 CEST 2004

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

More information about the Python-Dev mailing list