SQLite & C# ] How can I control the number of people editing a db file?

Question

Hi,

I'm programming a simple customer-information management software now with SQLite.

One exe file, one db file, some dll files. - That's it :)

2~4 people may be going to run this exe file simultaneously and access to a database.

Not only just reading but frequent editing will be done by them too.

Yeahhh now here comes the one of the most famous problems... "Synchronization"

I was trying to create / remove a temporary empty file whenever someone is trying to edit it. (this is a 'key' to access the db.)

But there must be a better way for it : (

What would be the best way of preventing this problem?

Answer 1

Well, SQLite already locks the database file for each use, the idea being that multiple applications can share the same database.

However, the documentation for SQLite explicitly warns about using this over the network:

SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

So assuming your "2-4 people" are on different computers, using a network file share, I'd recommend that you don't use SQLite. Use a traditional client/server RDBMS instead, which is designed for multiple concurrent connections from multiple hosts.

Your app will still need to consider concurrency issues (unless it speculatively acquires locks on whatever the user is currently looking at, which is generally a nasty idea) but at least you won't have to deal with network file system locking issues as well.

Answer 2

You are looking at some classic problems in dealing with multiple users accessing a database: the Lost Update.

See this tutorial on concurrency: http://www.brainbell.com/tutors/php/php_mysql/Transactions_and_Concurrency.html

At least you won't have to worry about the db file itself getting corrupted by this, because SQLite locks the whole file when it's being written. That being said, SQLite doesn't recommend you to use it if you expect your app to be accessed simultaneously by a multiple clients.