Does SQLite write operation block database only for other processes or for threads of single process too?

Question

Does SQLite write operation block database only for other processes or for threads of single process too? Is it possible to use SQLite in multithreading (=multisession) ASP.Net application?

Answer 1

This entry from the SQLite's FAQ might help you :

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.

So, I'd say only one process/thread/whatever can write to your DB at a given instant.

(there's more to read on the FAQ page ; you should read it : I didn't quote everything)

And, about the multithreading thing :

Is SQLite threadsafe?

SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way.

(same note)

Answer 2

You'll probably want to look at File Locking And Concurrency In SQLite Version 3.

In particular, I think you are interested in the "EXCLUSIVE" database lock:

EXCLUSIVE

An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

When a thread or process has an exclusive lock on a database, all other operations (read or write) are disallowed.

Regarding multisession ASP.net, I would assume that most of the time, you'll only be reading from the database:

SHARED

The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active.

There is no problem with many concurrent reads on an SQLite database; you will only be limited by the underlying OS/filesystem. If you're doing a lot of reads and writes concurrently, you might be interested in Appropriate Uses for SQLite, which states:

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

Also, in response to your comment on Pascal's answer, if you check the above link, you will see that SQLite makes no distinction between processes or threads, because all locks depend on the filesystem, not any program-level locks.

The pager module effectively controls access for separate threads, or separate processes, or both. Throughout this document whenever the word "process" is written you may substitute the word "thread" without changing the truth of the statement.