vapor/sql-kit

Support `SELECT ... FOR UPDATE`

rb-de0 opened this issue · 4 comments

rb-de0 commented

I want to use SELECT ... FOR UPDATE syntax to lock rows in a transaction (Pessimistic locking).
cf: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

Thanks!

tanner0101 commented

I've implemented this in #53 against master, usage looks like:

let db = TestDatabase()
try db.select().column("*")
    .from("planets")
    .where("name", .equal, "Earth")
    .for(.update)
    .run().wait()
XCTAssertEqual(db.results[0], "SELECT * FROM `planets` WHERE `name` = ? FOR UPDATE")

Does that seem like it solves your problem?

rb-de0 commented

Thank you for your quick implementation!
There is one point which bothers me.

It seems that this PR doesn't include support for LOCK IN SHARE MODE.
In older versions of MySQL, we need to use LOCK IN SHARE MODE for shared locking.
Are there plans to support this?

Thanks!

tanner0101 commented

@rb-de0 the current design allows you to pass in a custom SQLExpression as the locking clause. Here's an example:

let db = TestDatabase()
try db.select().column("*")
    .from("planets")
    .where("name", .equal, "Earth")
    .lockingClause(SQLRaw("LOCK IN SHARE MODE"))
    .run().wait()
XCTAssertEqual(db.results[0], "SELECT * FROM `planets` WHERE `name` = ? LOCK IN SHARE MODE")

As you said this is for "older versions of mysql", do you think this type of solution suffice?

It would also be possible to add this case to SQLLockingExpression, but I think if it's no longer in use in recent versions, it might be better to leave it out.

rb-de0 commented

@tanner0101 I missed that design. I think that solution is suffice.
Thanks!!

  • 👍1