SQLite Ok with transaction/commit or just use MySQL?

[maddogfargo]

I'm debating this for a new project:

Is it OK/safe to share SQLite across several modules, all running locally, with transactions/commits coded in...or should I just use a local MySQL server instead?

Suggestions/recommendations welcome.

My goal is fast, low overhead data storage for multiple modules running on the same local, physical box.
_________________
* illegible scribble *

[eeriegeek]

That should work as long as you make sure all your instances reference the same .sqlite file. The main issue would probably be that sqlite locks at the db level, so updates from other instances will block until the current transaction is committed (so keep db update transactions small). Reads can run concurrently. You may get frustrated if you need high performance updates as sqlite's transaction rate is actually quite low compared to RDBMSs like Postgres and MySQL (see the FAQ Item #19). There is a pretty good reference to sqlite's niche here: When to Use SQLite.

[maddogfargo]

Excellent info. Thanks!

I plan to run multiple instances of a module that will be using the database fairly heavily. So it sounds like MySQL might be a better option.

However I will be storing everything for this NWN server on SSD so could be transactions with SQLite will go much faster that with rotational media. Will have to test and see I suppose.
_________________
* illegible scribble *