Insert Parallelism

[silvanheller]

Since 0.15, cottontail has no possibility to not use transactions for inserts. This means that you cannot have external parallelism for data imports, since locking is done on a table-level.

Since this is not a severe issue, I thought i'd open a discussion on whether this is desirable behaviour.

A simple test demonstrating the problem would look as follows:

    @Test
    fun testParallelInsert() {
        val entityName = TEST_SCHEMA.entity("test")
        val entryString = "one"
        this.client.create(CreateEntity(entityName.fqn).column(STRING_COLUMN_NAME, Type.STRING))

        val outer = 4
        val inner = 1_000
        repeat(outer) {
            Thread(kotlinx.coroutines.Runnable {
                run {
                    repeat(inner) {
                        val insert = Insert().into(entityName.fqn).value(STRING_COLUMN_NAME, entryString)
                        this.client.insert(insert)
                    }
                }
            }).start()
        }

        Assertions.assertEquals(outer*inner, countElements(this.client, entityName)!!.toInt())
    }

[ppanopticon]

Thanks for bringing this up. The issue you're describing is not so much one of locking but that of snapshot isolation and the optimistic acquisition of write-transactions currently employed. In your example, you are implicitly starting four different transactions in parallel, each of which executes their workload on a dedicated snapshot of the database. All transactions that try to COMMIT after the first one will find, that the snapshot they operate on is out-of-date and COMMIT will therefore fail. We currently don't have reconciliation algorithms in place that handle these cases and while this would be perfectly possible to do, this is not going to happen anytime soon.

However, stating that parallelism for INSERT is not possible, is not quite right. In the example above, what you can do is execute the INSERT in a single, explicit transaction like this:

val outer = 4
val inner = 1_000
val txId = this.client.begin()
runBlocking {
    val jobs = (0 until outer).map {
        launch {
            repeat(inner) {
                val insert = Insert().into(TEST_ENTITY_NAME.fqn)
                .value(INT_COLUMN_NAME, it)
                .value(STRING_COLUMN_NAME, RandomStringUtils.randomAlphanumeric(5),)
                .value(DOUBLE_COLUMN_NAME, 1.0)
                .txId(txId)
                 this@DMLServiceTest.client.insert(insert)
            }
        }
    }
    jobs.forEach { it.join() }
}

this.client.commit(txId)

Assertions.assertEquals(TEST_COLLECTION_SIZE + outer*inner, countElements(this.client, TEST_ENTITY_NAME)!!.toInt())

Cottontail DB will then serialise the individual statements that take place within the transaction.

I'm currently also experimenting with making the type of transaction more explicit when starting it. In your example, this would then lead to locks being acquired when creating the transaction and the individual INSERTS in your example would wait for the preceding one to complete before executing the workload. Like this, we can prevent failure of the transaction at COMMIT time. Of course, these locks will lead to contention and limit the amount of parallelism possible.

Ultimately, there is a certain bottleneck with regards to write-parallelism even if we went along to build these reconciliation algorithms. Again this could be further optimised by careful engineering but again, this is not planned in the near future (at least not from my side).