4

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?

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Luca Carlon
  • 9,546
  • 13
  • 59
  • 91
  • 1
    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. –  Sep 10 '11 at 10:26
  • 2
    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 Sep 10 '11 at 10:35

2 Answers2

7

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.

Luca Carlon
  • 9,546
  • 13
  • 59
  • 91
0

I enabled READ_UNCOMMITTED:

PRAGMA read_uncommitted=true;

And, set WAL mode which seems recommanded in Use of shared-cache is discouraged as shown below:

Shared-cache mode is an obsolete feature. The use of shared-cache mode is discouraged. Most use cases for shared-cache are better served by WAL mode.

PRAGMA journal_mode=wal;

Then, I tried dirty read with 2 transactions (2 command prompts) but it didn't occur.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129