Using Suspended Transactions with Exposed

[MayureshGharat]

Hi Folks, I am trying to use Exposed with Transactions (Kotlin) and was thinking of using suspended transactions as listed here : https://github.com/JetBrains/Exposed/wiki/Transactions#working-with-coroutines.

I have an use-case, wherein I read a value from a database tables using "SELECT..where key = "K"... FOR UPDATE" in-order to ensure row level locking at database level, process the read value and upsert it back to the table. Here is what my current code looks like :

private suspend fun Exe(abc: ABC) {
        withContext(Dispatchers.IO) {
            transaction {
                abc.id?.let {
                         val oldData = databaseWrapperInterfaceImpl.getDataById(it)
                         val newData = process(oldData)
                        databaseWrapperInterfaceImpl.upsertData(newData)
                        }
                } ?: logAndThrow("Some error msg")
            }
        }
    }

databaseWrapperInterfaceImpl.getDataById(it) is responsible for executing "SELECT..where key = "K"... FOR UPDATE". Currently, databaseWrapperInterfaceImpl.getDataById(it) is not a suspend function.

Since this databaseWrapperInterfaceImpl.getDataById(it) can block if some other instance of the application is already holding the lock, I was thinking my current thread in my current instance that is executing Exe(..) will also block instead of yielding/ suspending as it would for normal suspending functions in Kotlin (correct me if my understanding is wrong here), which is why I was thinking of using the suspended transactions.

However there is a line here (https://github.com/JetBrains/Exposed/wiki/Transactions#working-with-coroutines.) : **Please note what such code remains blocking (as it still uses JDBC) and you should not try to share a transaction between multiple threads as it will lead to undefined behaviour.** which is confusing, since if a transaction is suspended, it can be run by different threads at different points in time and there is no guarantee that it is not shared. Let me know if I am misunderstanding anything, here.

[Tapac]

You are right, coroutines doesn't work well with blocking code (like JDBC) where any call to DB can stuck for unpredictable amount of time. And then you'll get thread blocked on Dispatchers.IO pool.

The way to go is to use R2DBC but its support is not yet implemented but you can follow #456 to be notified when it will be ready

[MayureshGharat]

@Tapac I had another question about this. We have two tables TableA and TableB with each having there own wrapper classes (aka repo functions), doing following:

@Component
class TableARepo {

 suspend fun insertTableA(data){
    withContext(Dispatchers.IO) {
      transaction {
       TableA.insert(....)
    }
  }

and

@Component
class TableBRepo {

 suspend fun insertTableB(data) {
    withContext(Dispatchers.IO) {
      transaction {
         TableB.insert(....)
    }
  }

Now I have a another function at some other point in the code that does this:

val tableARepo = TableARepo()
val tableBRepo = TableBRepo()

fun suspend otherFunc(dataA, dataB) {
   newSuspendedTransaction(transactionIsolation = IsolationLevel.TRANSACTION_REPEATABLE_READ.levelId,) 
       {
          tableARepo.insertTableA(dataA)
          tableBRepo.insertTableB(dataB)
        }
}

The idea here is that both the upserts go through or none at all. Do you think, this might lead to issues, since we have the Table.insert(...) inside transaction block in both the repo functions, but the calling function (otherFunc) in this case uses suspendedTransaction?