SQLite simultaneous reading and writing

by

On Android 3.0 and higher SQLiteDatabases support WAL mode (write-ahead logging):

When write-ahead logging is not enabled (the default), it is not
possible for reads and writes to occur on the database at the same
time. Before modifying the database, the writer implicitly acquires an
exclusive lock on the database which prevents readers from accessing
the database until the write is completed.

In contrast, when write-ahead logging is enabled, write operations
occur in a separate log file which allows reads to proceed
concurrently. While a write is in progress, readers on other threads
will perceive the state of the database as it was before the write
began. When the write completes, readers on other threads will then
perceive the new state of the database.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging()

To start a transaction in WAL mode use beginTransactionNonExclusive() instead of beginTransaction().
While beginTransaction() starts a transaction in EXCLUSIVE mode, beginTransactionNonExclusive() starts one in IMMEDIATE mode

  • EXCLUSIVE mode uses exclusive locks (http://www.sqlite.org/lockingv3.html#excl_lock) meaning no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete
  • IMMEDIATE mode uses reserved locks (http://www.sqlite.org/lockingv3.html#reserved_lock) meaning no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE, other processes can continue to read from the database, however.

In simpler words: call beginTransactionNonExclusive() for IMMEDIATE mode and we can read while another thread is writing (the state before the write transaction started because we won’t use read_uncommitted connections -> http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Dirty_reads).

More Related Contents:

  1. Android SQLite data display to ListView
  2. How can I avoid concurrency problems when using SQLite on Android?
  3. Android SQLite database: slow insertion
  4. Pros and Cons of SQLite and Shared Preferences [closed]
  5. Share SQLite database between 2 android apps?
  6. Android: upgrading DB version and adding new table
  7. How do I view the SQLite database on an Android device? [duplicate]
  8. Best way to work with dates in Android SQLite [closed]
  9. Android SQLite Example [closed]
  10. Android database encryption
  11. Cursor adapter and sqlite example [closed]
  12. How to add new Column to Android SQLite Database?
  13. What is a Full Android Database Helper class for an existing SQLite database? [closed]
  14. How do I create a database in android? [closed]
  15. How to save images into Database
  16. Android SimpleCursorAdapter doesn’t update when database changes
  17. Android SQLite Database, WHY drop table and recreate on upgrade
  18. update sql database with ContentValues and the update-method
  19. How does one check if a table exists in an Android SQLite database?
  20. Create SQLite database in android
  21. Could not allocate CursorWindow
  22. Saving ArrayLists in SQLite databases
  23. android, how to exec a sql file in sqlitedatabase
  24. SQLiteOpenHelper – creating database on SD card
  25. SQLite android column not found
  26. How to store JSON object in SQLite database
  27. H2 Database vs SQLite on Android [closed]
  28. SQLiteOpenHelper onUpgrade() Confusion Android
  29. Using COLLATE in Android SQLite – Locales is ignored in LIKE statement
  30. When to close db connection on android? Every time after your operation finished or after your app exit

Leave a Comment