Add support for row level locking

[itspolly]

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.

[gwynne]

The current recommendation for this is to use SQLKit rather than Fluent to perform row-level locking. An example in use in production can be found here (although it's something of an ugly example thanks to the tormented logic used to support old versions of MySQL).