Race Condition With Explicit Database for Transactions

[ccjernigan]

STEPS TO REPRODUCE

1. Create an in-memory database

   val database: Database = when (type) {
       is Type.Memory -> {
           // https://github.com/JetBrains/Exposed/issues/726
           val cfg: HikariConfig = HikariConfig().apply {
               jdbcUrl = "jdbc:sqlite::memory:?foreign_keys=on"
           }
           val dataSource = HikariDataSource(cfg)
           Database.connect(dataSource)
       }
   }.apply {
       transactionManager.defaultIsolationLevel = java.sql.Connection.TRANSACTION_SERIALIZABLE
   }.also {
       transaction {
           SchemaUtils.createMissingTablesAndColumns(
               SubjectTableDefinition
           }
       }

2. Create a transaction with an explicit reference to the database

       val fixtureSubject = SubjectFixture.newIdentifiedSubject()
   
       transaction(database) {
           SubjectTableDefinition.insertBlocking(fixtureSubject)
       }

   where

   object SubjectTableDefinition : UUIDTable("subject") {
   val sex = enumeration("sex", Sex::class)
   val yearOfBirth = integer("year_of_birth")
   
   fun insertBlocking(identifiedSubject: Identified<Subject>) = insert {
       it[id] = EntityID(identifiedSubject.id, SubjectTableDefinition)
       it[sex] = identifiedSubject.value.sex
       it[yearOfBirth] = identifiedSubject.value.yearOfBirth
   }

RESULTS

Actual

With a high probability, an exception is thrown that the "subject" table does not exist.

Expected

No exception is thrown, because the table clearly exists.

NOTES

This is not 100% reproducible, as it might be a race condition. There seems to be a difference between implicit and explicit database references for transaction, where implicit references seem not to hit the issue. E.g. transaction {} versus transaction(database) {}.

It also seems less likely to occur if I use a file-based database, which might indicate that Hikari plays a role in this although this is not yet clear. (I'm only using Hikari as a workaround for #726.)

I'm only encountering this in my tests, not at runtime with my application. This might also be because my tests are setting up and tearing down databases several hundred times across a number of different tests, which could increase the probability of hitting race conditions.

[ccjernigan]

I've been continuing to hit this intermittently, and the difference between implicit and explicit database references for query/insert/delete transactions might not matter as much as I thought initially. This is the challenge with race conditions.

My current theory is that being explicit for the transaction to create the tables might help. E.g. transaction(database) { SchemaUtils.createMissingTablesAndColumns(...) }.

[Tapac]

Can you try the workaround from https://github.com/JetBrains/Exposed/issues/726#issuecomment-932202379 ?