SQlite/Firebird: Does any of them support multiple concurrent write access ?

Question

Question: I currently store ASP.net application data in XML files.

Now the problem is I have asynchronous operations, which means I ran into the problem of simultanous write access on a XML file...

Now, I'm considering moving to an embedded database to solve the issue. I'm currently considering SQlite and embeddable Firebird.

I'm not sure however if SQlite or Firebird can handle multiple concurrent write access.
And I certainly don't want the same problem again.
Anybody knows ?
SQlite certainly is better known, but which one is better - SQlite or Firebird ? I tend to say Firebird, but I don't really know.

No MS-Access or MS-SQL-express recommodations please, I'm a sane person.

Answer 1

It sounds like SQLite will be a good fit. We use SQLite in a number of production apps, it supports, actually, it prefers transactions which go a long way to handling concurrency.

http://stackoverflow.com/questions/923740/transactional-sqlite-in-c

Answer 2

I wll choose Firebird for many reasons and for this too

Although it is transactional, SQLite does not support concurrent transactions, so if your embedded application needs two or more connections, they must be serialized. An embedded Firebird database is simple to upgrade to a fully shared database - just change the shared library.

May be you can also check this

Answer 3

SQLITE can be configured to gracefully handle simultaneous writes in most situations. What happens is that when one thread or process begins a write to the db, the file is locked. When the second write is attempted, and encounters the lock, it backs off for a short period before attempting the write again, until it succeeds or times out. The timeout is configurable, but otherwise all this happens without the application code having to do anything special except enabling the option, like this:

// set SQLite to wait and retry for up to 100ms if database locked
sqlite3_busy_timeout( db, 100 );

All this works very well and without any difficulty, except in two circumstances:

1. If an application does a great many writes, say a thousand inserts, all in one transaction, then the database will be locked up for a significant period and can cause problems for any other application attempting to write. The solution is to break up such large writes into seperate transactions, so other applications can get access to the database.

2. If the database is shared by different processes running on different machines, sharing a network mounted disk. Many operating systems have bugs in network mounted disks that making file locking unreliable. There is no answer to this. If you need to share a db on a network mounted disk, you need another database engine such as MySQL.

I do not have any experience with Firebird. I have used SQLITE in situations like this for many applications over several years.