Skip to content

Latest commit

 

History

History
36 lines (26 loc) · 1.86 KB

README.md

File metadata and controls

36 lines (26 loc) · 1.86 KB
Raw

SQLite WAL mode bug report

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

https://issuetracker.google.com/issues/270105918