Add support for row level locking #788
Description
Please summarize your feature request
Support for row level locks when using MySQL or Postgres
Describe the functionality you're seeking in more detail
There is currently no way to implement database-based locking when using fluent. This issue proposes adding support for the following row-based locking functionality:
enum LockMode {
case read
case write
case partialWrite
case writeOrFail
case partialRead
case readOrFail
var postgresLockStatement: String {
switch self {
case .read:
return "FOR SHARE"
case .write:
return "FOR UPDATE"
case .partialWrite:
return "FOR UPDATE SKIP LOCKED"
case .writeOrFail:
return "FOR UPDATE NOWAIT"
case .partialRead:
return "FOR SHARE SKIP LOCKED"
case .readOrFail:
return "FOR SHARE NOWAIT"
}
}
var mysqlLockStatement: String {
switch self {
case .read:
return "LOCK IN SHARE MODE"
case .write:
return "FOR UPDATE"
case .partialWrite:
return "FOR UPDATE SKIP LOCKED"
case .writeOrFail:
return "FOR UPDATE NOWAIT"
case .partialRead:
return "LOCK IN SHARE MODE SKIP LOCKED"
case .readOrFail:
return "LOCK IN SHARE MODE NOWAIT"
}
}
}This issue proposes adding a lockMode method to QueryBuilder that has a parameter of type LockMode. This method would store the lock mode and affected table names (for postgres, tables affected by a lock should be appended to the locking statement with "of" followed by a comma separated list of "\(schemaName).\(tableName)", I have not researched what the correct syntax is for MySQL).
Finally, note that some ORMs require transactions when using a lock mode. The rationale for this is that locks will take no effect at protecting a series of operations as without a transaction, the lock is immediately released after the query runs.
Have you considered any alternatives?
Using advisory locking which is also not supported and using optimistic concurrency control with a version field / timestamp field.