I am currently writing software that will be shipped directly to
consumers, and have SQLite embedded.
The application is multi-threaded and I have:
begin transaction; write; write; read; write; read; end transaction;
read; read; read
For several reasons, it is important to me that one thread
continuously writes and reads while another thread continuously reads.
In order to get decent write performance while in FULL synchronous
mode, I have as many as hundreds of pending writes committed in a
single transaction. This gets me terrific write performance, but the
reader thread doesn't see any of the pending data until the
transaction is actually committed.
If I put the database in synchronous NORMAL or synchronous OFF mode,
then write performance gets a lot better and I can use autocommit
transactions... but then the database is considerably more sensitive
to OS crashes and power failure.
This is actually okay for me as long as I can reliably detect that the
database file is corrupt. Failure is okay, but byzantine failure is
Does SQLite detect database corruption? If so, what kind of
corruption is detected and how does it do so?
Wilson Yeung <[hidden email]> wrote:
> Does SQLite detect database corruption? If so, what kind of
> corruption is detected and how does it do so?
SQLite does attempt to detect corruption in database files
and returns SQLITE_CORRUPT when it finds it. The regression
test suite for SQLite runs thousands of tests in which
random binary data overwrites random small sections of a
database file. These tests confirm that the corruption is
detected and reported properly.
(1) Corruption is only detected when SQLite tries to use
the corrupted section of the database. If the corruption
occurs in table ABC and you are only accessing table PQR,
then you will never see the SQLITE_CORRUPT error.
(2) If the corruption occurs in the middle of (say) a large
BLOB, the BLOB will be changed of course, but as SQLite
does not store checksums or other error detecting codes
on data, there is no way for SQLite to know this.
(3) Corrupt detection has historically been a buggy area of
SQLite. In spite of the many thousands of test cases that
are run against the library, users do from time to time
find creative ways to corrupt databases that lead to
to segfaults. There are no outstanding issues in this
area that I am aware of, but it has historically been