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?

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. — user330315
– user330315, Commented Sep 10, 2011 at 10:26
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... — Luca Carlon
– Luca Carlon, Commented Sep 10, 2011 at 10:35

Luca Carlon · Accepted Answer · 2011-09-14 06:55:11Z

9

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.

answered Sep 14, 2011 at 6:55

Luca Carlon

10k15 gold badges62 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-01 21:32:25Z

0

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

edited Dec 1, 2023 at 21:32

answered Oct 15, 2022 at 13:43

Super Kai - Kazuya Ito

42.8k23 gold badges258 silver badges257 bronze badges

Collectives™ on Stack Overflow

Reading uncommitted changes (dirty read) from SQLite

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related