When performing a query on SQLiteDatabase
, the SQLiteProgram
from android.database.sqlite
performs SQLiteStatementType.getSqlStatementType(mSql)
check to guess the query type and to prepare a SQL statement for execution.
See - https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/java/android/database/sqlite/SQLiteProgram.java;l=60-64;drc=ee976fd24cfbd567c4543da39aadb1b3e4567042
This query type guessing is very basic and naive, relying on the first 3 letters of the SQL statement - https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/java/android/database/DatabaseUtils.java;drc=ee976fd24cfbd567c4543da39aadb1b3e4567042;l=1566
So for example, if the query is a Select query, but contains a comment like this:
-- comment
select * from employees limit 1;
It will NOT be recognized as a Select query and assumeReadOnly
will be false.
Most of the time it doesn't matter, but with WAL mode enabled, we expect the SQLite to be able to perform read queries during writing, but this assumeReadOnly
breaks this behavior.
See the sample code that demonstrates the issue
https://github.com/Dimezis/WALtest/blob/master/app/src/main/java/com/example/waltest/MainActivity.kt.
Pay attention to fastReadQuery
method and its comment.
This app performs basic insert transactions on 1 writer thread, and reads with select
on the other thread with DB in WAL mode.
If you remove the comment -- comment
from the query, you can see that the reading is done concurrently according to logs and their timestamps:
- WAL write start
- WAL read start
- WAL read end
- WAL write end
If you leave the -- comment
though, concurrent reading doesn't work anymore and the output is:
- WAL write start
- WAL read start
- WAL write end
- WAL read end