Reading uncommitted changes (dirty read) from SQLite

Question

I wrote an application (using Qt in C++) which inserts data into a SQLite database. Another application reads data from the same database, but I noticed that it is not possible to read uncommitted data (dirty read). I would like instead to be able to read data even if it is not committed yet.

Reading the SQLite documentation I found the pragma read_uncommitted which, from what I understand, should be what I need. Problem is that, even if I set that to true, I cannot get uncommitted data.

I tried to run my application which performs the insertion process, and, at the same time, start the sqlite3 client. I set the pragma to true and I try to count the records inside the table. What I get is always 0 (database was empty before my insertion process started), until the entire process finishes where I immediately get the entire data.

Am I doing something wrong? Isn't this pragma supposed to make the sqlite3 client behave differently?

Campania

Why would you want to read uncommitted data? If you don't care about transactions, you can insert everything with autocommit on, and every row will be visible immediately. – Anniceannie

Yes, sure, I know. But the entire process must not be committed in case a failure of some king happens before it finishes completely. Anyway, I want the user to be able to see the changes even before the entire transaction ends. Sure the user is warned those are not complete results. Moreover it seems that using transactions like this increase performance of the insertion... – Campania

Answer 1

I answer to myself: no, it seems it is not possible. The read_uncommitted isolation mode requires to enable the shared cache, which is possible currently only for different threads living in the same process. This seems the best place to study this: http://www.sqlite.org/sharedcache.html.

Campania

Answer 2

According to my experiments, all anomalies below don't occur with READ UNCOMMITTED and SERIALIZABLE(default) in SQLite. *SQLite doesn't have READ COMMITTED and REPEATABLE READ :

Anomaly	READ UNCOMMITTED	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Dirty Read	No	-	-	No
Non-repeatable Read	No	-	-	No
Phantom Read	No	-	-	No
Lost Update	No	-	-	No
Write Skew	No	-	-	No

*I ran the commands below for my experiments:

PRAGMA read_uncommitted=true; -- READ UNCOMMITTED

PRAGMA read_uncommitted=false; -- SERIALIZABLE 

Adherent