Android Room slow migration race condition

Question

Room version 2.2.0-rc01 Android versions 7.1, 8.1

I have a migration that is a bit large. Adding a couple tables, indices, etc. When it runs, it locks the database. Since it takes a while, it is causing android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5) in every other part of the application.

Is there a way to await the migrations finishing before trying to utilize the db?

Abbreviated migration:

  try {
            // database.beginTransaction()

            database.execSQL("ALTER TABLE DeviceItem ADD COLUMN #### INTEGER NOT NULL DEFAULT 0")
            database.execSQL("ALTER TABLE DeviceItem ADD COLUMN #### TEXT NOT NULL DEFAULT ''")

            database.execSQL("CREATE INDEX IF NOT EXISTS ### ON #### (###, ###)")
           //helper method that just creates the table
            createLocalityTable(database)
            //database.setTransactionSuccessful()


        } catch (e: SQLiteException) {
            Crashlytics.logException(e)
            throw e
        } catch (e: Throwable) {
            Crashlytics.logException(e)
            throw e
        } finally {
            // database.endTransaction()
            database.close()
        }

This is my appDatabase singleton

package com.company.app.data

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.TypeConverters

@Database(entities = [someClass::class, otherClass::class, etc::class], version = 3)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun somethingDao(): somethingDao

    @Synchronized
    override fun close() {
        if (INSTANCE != null) {
            super.close()
            INSTANCE = null
        }
    }
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        var canGo = false
        fun getAppDatabase(context: Context): AppDatabase? {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }

            synchronized(this) {
                val instance = Room.databaseBuilder(
                        context.applicationContext,
                        AppDatabase::class.java,
                        "myDb"
                ).addMigrations(MIGRATION_X_X).build()

                INSTANCE = instance
                return instance
                }
        }
    }
}

Answer 1

I believe that your issue is that you should not be trying to close the the database as when the migration is called the database itself has already been opened by Room (hence how it knows the version and whether or not to call the migration).

Room does not expect the database to be closed when the migration has completed, so it considers that something else has closed/accessed the database.

Furthermore there is no need to use transactions as Room calls the migrate method within a transaction itself.

This method is already called inside a transaction and that transaction might actually be a composite transaction of all necessary Migrations.

Migration

I'd suggest that your migration code should just be :-

        database.execSQL("ALTER TABLE DeviceItem ADD COLUMN #### INTEGER NOT NULL DEFAULT 0")
        database.execSQL("ALTER TABLE DeviceItem ADD COLUMN #### TEXT NOT NULL DEFAULT ''")
        database.execSQL("CREATE INDEX IF NOT EXISTS ### ON #### (###, ###)")
        //helper method that just creates the table
        createLocalityTable(database)

• The createLocalityTable method should also not close the database.