0

Base on article on http://www.codeproject.com/KB/cs/SQLiteCSharp.aspx

Like all databases, SQLite has its list of shortcomings. It is not suitable for a client server application or as a networked database. It’s not suited well for a multi user scenario and can have serious file locking issues when accessed simultaneously over the network. Quite for the same reason, SQLite is not suited for a multi-threaded or a multi-process application-database access scenario.

Is it true that SQLite not suitable for a client server app? because I want to develop bookstore and karaoke application? If SQLite is not suitable what databases are suitable which self-contained, serverless, zero-configuration?

Willy
  • 1,689
  • 7
  • 36
  • 79
  • 3
    If you're only running under light load, SQLite should work okay in a client-server setup. (Assuming the "server" is your application backend, only opens a single connection to SQLite, and properly synchronises access to it.) – millimoose Oct 17 '11 at 23:51
  • possible duplicate of [SQLite for client-server](http://stackoverflow.com/questions/1321493/sqlite-for-client-server) – MPelletier Oct 18 '11 at 18:48

2 Answers2

1

If you're only running under a single thread, and your app is otherwise fast enough to serve all requests, then you're fine. As you noted, multiple thread, or multiple simultaneous user writes (via multiple threads, usually), is where you run into issues.

My answer to this question outlines in more detail some of the problems with database locks, etc., which you've already hinted at with SQLite.

Community
  • 1
  • 1
jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • 1
    SQLite allows for multiple reads across many threads, but not multiple writes. – Gregor Brandt Oct 18 '11 at 17:53
  • Yes, thank you for the technical correction. I changed it from `multiple simultaneous user access` to `multiple simultaneous user writes` - which is what I initially intended. – jefflunt Oct 18 '11 at 18:34
1

The SQLite FAQ says:

(5) Can multiple applications or multiple instances of the same application access a single database file at the same time? Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

There is more information under the link.

So all you really need to do is ensure that you are using some kind of locking mechanism for writes.

Gregor Brandt
  • 7,659
  • 38
  • 59
  • Correct. SQLite has its own locking mechanism, and will pass a SQLite::Busy error back to you during a DB lock, which you can use to react appropriately (e.g. try again later, cancel the write, etc.) – jefflunt Oct 18 '11 at 18:32
  • SQLite contains it's own locking mechanism, it will not allow simultaneous writes. The problem, according to the SQLite documentation, is that various networked file systems are not reliable when it comes to implementing the file locking mechanisms that underlie SQLite. – Larry Lustig Oct 18 '11 at 18:41