Read and update value inside transaction in Room DB

    In the post Accessing CoreData from a single background context there is a description of the process to safely update the value in CoreData on iOS. Single background context was used to guarantee that there would be no merge conflicts during update.

    To achieve such result when using a Room DB on Android, the similar approach can also be used. The database writes may be done on a single thread. However, as Room DB is a object relational mapping library, it does not abstract away the underlying SQLite database concepts. Instead of using single thread for writing into DB we may use SQL transaction mechanism. Let's check how this works, employing a simple unit test.

    For testing purpose, there would be two database tables, Tag and PageNumber, with a one-to-one relationship. Each Tag in a database may contain a corresponding unique PageNumer entity, with the 'page' value, indicating the currently active page number for that tag.

@Entity
data class Tag(
    @PrimaryKey(autoGenerate = false)
    val name: String
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = Tag::class,
        parentColumns = arrayOf("name"),
        childColumns = arrayOf("tagName"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class PageNumber(
    @PrimaryKey
    val tagName: String,
    val page: Int
) 

    In the following Data Access Object function that does read-and-update process is called getAndIncrementPageNumberForTag() and has a @Transaction annotation, placing it's body inside a SQLite transaction.

@Dao
interface TagsDao {
	
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertTag(tag: Tag)

    // Inserts new PageNumber or Updates it if it already exists (available since Room 2.5.0)
    @Upsert
    suspend fun upsertPageNumber(pageNumber: PageNumber)

    // Loads the PageNumber for Tag
    @Query("SELECT * FROM tag JOIN pageNumber ON tag.name = pageNumber.tagName")
    suspend fun loadPageNumberForTag(): Map>

    // Gets the current page number for the tag, incrementing this number and saving it afterwards.
    @Transaction
    suspend fun getAndIncrementPageNumberForTag(tagName: String, defaultPageCount: Int): Int {
        val tag = Tag(tagName)
        
        // Inserts new tag if it does not yet exist
        insertTag(tag) 
        
        // Load PageNumber for this tag
        val pageNumber = loadPageNumberForTag()[tag]?.firstOrNull() ?: PageNumber(tagName, defaultPageCount)
        val result = pageNumber.page
        
        // Increment the PageNumber.page value
        upsertPageNumber(PageNumber(tagName, result + 1))
        
        // Return an old PageNumber.page value
        return result
    }
}

  Following is an instrumented unit test to check that all increments took place without interfering with each other.

@RunWith(AndroidJUnit4::class)
class DbTest {
    private lateinit var tagsDao: TagsDao
    private lateinit var db: GuessDatabase

    @Before
    fun createDb() {
        val context = ApplicationProvider.getApplicationContext()
        db = Room.inMemoryDatabaseBuilder(
            context, GuessDatabase::class.java
        ).build()
        tagsDao = db.tagsDao
    }

    @After
    @Throws(IOException::class)
    fun closeDb() {
        db.close()
    }

    @Test
    fun test() = runBlocking {
        var pageNumber = 0
        val cnt = 10
        
        withContext(Dispatchers.Default) {
            repeat(cnt) {
                launch {
                    pageNumber = tagsDao.getAndIncrementPageNumberForTag("tagName", 1)
                }
            }
        }

        Assert.assertEquals(pageNumber, cnt)
    }
}

    If, however, the @Transaction annotation would be removed, the test will fail. Without single transaction, the function getAndIncrementPageNumberForTag() accessed simultaneously from multiple threads from Dispatchers.Default pool would put incorrect results into database due to the race conditon.

    Would putting the limit of 1 active thread on a Dispatchers.Default pool - like Dispatchers.Default.limitedParallelism(1), also protect from the race condition, just like a transaction did? In this case - no. The function getAndIncrementPageNumberForTag() would be called on a same worker actually, but the functions that it invokes - insertTag(), upsertPageNumber(), ... are all a suspend functions with implementations provided by Room library, and they would anyway run on different workers. This is why the approach with limitedParallelism(1) call on dispatcher would also require removing the suspend modifier from mentioned functions.

    Summing up, in Room DB the SQL transaction mechanism may be used to prevent the merge conflicts during read and update data operations.