Exposed: Optimistic/Pessimistic locking

[nsm257]

How can I use optimistic / pessimistic locking with exposed?
In Spring/hibernate it can be done using @Version annotation on a version attribute in a table or using @Lock attribute on the JPA Query.

Is there any way to do something like that here?

[Tapac]

Not at the moment

[taylancapraz]

@Tapac As I see in source code, forUpdate() function in Query class provides Pessimistic lock.

I don't see it in any document, is there a special reason for this? Is there any objection to using this function?

[HoffiMuc]

any news on this?

[AleksanderBrzozowski]

It could be implemented on your own, consider such table:

object UserTable : Table("user") {
    val name = text("name")
    val version = integer("version")

    override val primaryKey = PrimaryKey(name)
}

When performing update, you can check that version is equal to provided version:

val updatedRows = UserTable.update({ (UserTable.name eq name) and (UserTable.version eq version) }) {
    it[UserTable.version] = version + 1
    // update something else
}

if (updatedRows == 0) {
    throw RuntimeException("Optimistic locking occurred")
}

This could be improved by such extensions:

interface OptimisticLockingAware {
    val optimisticLockingVersion: Column<Int>
}

fun <T> T.optimisticLockingAwareUpdate(where: (SqlExpressionBuilder.() -> Op<Boolean>)? = null,
                                       version: Int,
                                       limit: Int? = null,
                                       body: T.(UpdateStatement) -> Unit
): Int where T : Table, T : OptimisticLockingAware {
    val optimisticLockingAwareWhereClause: (SqlExpressionBuilder.() -> Op<Boolean>) = {
        val versionEquals = [email protected] eq version
        when (where != null) {
            true -> where(this).and(versionEquals)
            else -> versionEquals
        }
    }

    val updateVersionAwareStatement: T.(UpdateStatement) -> Unit = {
        body(it)
        it[this.optimisticLockingVersion] = version + 1
    }

    return this.update(optimisticLockingAwareWhereClause, limit, updateVersionAwareStatement)
}

Then, UserTable should implement OptimisticLockingAware interface:

object UserTable : Table("user"), OptimisticLockingAware {
    val name = text("name")
    val version = integer("version")

    override val optimisticLockingVersion = version
    override val primaryKey = PrimaryKey(name)
}

And here is improved invocation of update which is aware of optimistic locking:

val updatedRows = UserTable.optimisticLockingAwareUpdate(where = { UserTable.name eq name }, version = version) {
    // update something
}

if (updatedRows == 0) {
    throw RuntimeException("Optimistic locking occurred")
}

Note that exception could be thrown inside optimisticLockingAwareUpdate, it depends on your use case ;)

[magnomp]

Any updates on this?
While it's easy for us at table level, I think it's not doable with DAO without framework support