Safe file I/O to shared file (or SQLite) from multi-threaded web server

[Steve Holden]

John Nagle wrote:
> python at bdurham.com wrote:
>> I'm looking for the best practice way for a multi-threaded python web
>> server application to read/write to a shared file or a SQLite database.
>>
>> What do I need to do (if anything) to make sure my writes to a regular
>> file on disk or to a SQLite database are atomic in nature when multiple
>> clients post data to my application simultaneously?
> 
>    SQLite can do that correctly, but SQLite isn't intended for use
> as a database engine for a busy database being used by many
> concurrent operations.  Especially if those operations involve
> updates.  Any update in SQLite locks all tables involved for the duration
> of the operation.  When SQLite hits a lock, it returns an error code, and
> the caller should retry after a delay.  If this occurs frequently in
> your application, you've hit the limits of SQLite.  Then it's
> time to move up to MySQL.
> 
Or PostgreSQL, which has superior SQL standards conformance and
excellent high-volume data performance.

>   If you have enough traffic that you need a multi-threaded web server,
> it's probably time to move up.
> 
Yes, but not to MySQL, please. Particularly since there is a sword of
Damocles hanging over its head while the Oracle takeover of Sun is pending.

regards
 Steve
-- 
Steve Holden           +1 571 484 6266   +1 800 494 3119
PyCon is coming! Atlanta, Feb 2010  http://us.pycon.org/
Holden Web LLC                 http://www.holdenweb.com/
UPCOMING EVENTS:        http://holdenweb.eventbrite.com/