Choosing the right database-system for a small project

[Gerhard Häring]

Thomas Weholt wrote:
> Ok, I'm going to do some more testing on both MetaKit and SQLite, but I'd
> like to know of any trouble I might run into with concurrent writes or reads
> to the database. This will be used on a webserver, not with heavy load, but
> to be sure it would be nice to have at least some simple locking mechanism.
> 
> Any tips?

I can only speak for PySQLite.

SQLite does the necessary locking automatically, and the locking 
behaviour is also configurable.

PySQLite uses transactions by default, and while you're in a 
transaction, SQLite locks the *whole* database. Depending on your app 
not using transactions might be an option, if perhaps only for reading.

To disable transactions, use autocommit=1 in the connect call:

cx = sqlite.connect(database="/path/to/db", autocommit=1)

Now if you use transactions (default behaviour), as I said SQLite locks 
the whole database while in a transaction. So be sure to commit or 
rollback as soon as you can.

In the case where you try to access the database while it is locked, you 
will by default get a DatabaseError exception thrown at you. That's 
probaly not what you want :-)

So you can tell SQLite how long to wait for the lock to go away. This

cx = sqlite.connect(database="path/to/db", timeout=5000)

will wait 5 seconds for any lock to go away before throwing an Exception 
at you.

-- Gerhard