Will SQLite database be locked when some suspend function to operate it in Android?

Question

I use SQLite with Room in my Android Studio project.

There are some suspend functions to operate a SQLite database, some suspend functions in background service to modify records.

Will the SQLite database maybe locked?

Answer 1

Will the SQLite database maybe locked?

Yes.

Typically Room will use WAL (Write-ahead logging) rather than Journal Mode. i.e. by default API 16+ will utilise WAL and under API 16 will utilise journal mode.

• With WAL changes are made to a separate file (database file name suffixed with -wal). At times the changes will be applied to the actual database file (checkpointed). Rollback is basically removal of the data from the -wal file.
• With Journal made, changes are applied to the database file and a record (the journal) is kept. Rollback is the process of undoing the changes made to the database file.

With WAL mode readers and writers do not typically lock each other. To quote the SQLite documentation

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Of course implicit with the above writers(LOCK) block writers. Explained later in the documentation by:-

Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.

Implicitly, and as would be expected, readers do not block readers.

So if you have multiple asynchronous suspended functions that are updating the database, then a locked database may be encountered. Perhaps refer to Composing Suspending Functions

If you are using Journal Mode, see setJournalMode, then concurrency (locking) is more restrictive see File Locking and Concurrency