fluent-kit
fluent-kit copied to clipboard
vapor
Transaction isolation levels
In MySQL and PostgreSQL, you can set the isolation level of your transactions globally or by session. The isolation level basically says how the data from the transaction queries is handled. For example, I can set the level to READ UNCOMMITTED, so a SELECT operation will retrieve data that has been added using INSERT or UPDATE even before the transaction is committed.
I propose we have DatabaseConnectable.transaction method that takes in the isolation level for the transaction that will be run. A type-specific MySQL implementation that I created looks like this:
import MySQL
extension MySQLDatabase {
enum IsolationLevel: String {
case repeatable = "REPEATABLE READ"
case committed = "READ COMMITTED"
case uncommitted = "READ UNCOMMITTED"
case serializable = "SERIALIZABLE"
static let `default`: IsolationLevel = .repeatable
static let sessionColumn: String = "@@global.transaction_isolation"
static func parse(_ level: String) -> IsolationLevel? {
switch level {
case "REPEATABLE-READ": return .repeatable
case "READ-COMMITTED": return .committed
case "READ-UNCOMMITTED": return .uncommitted
case "SERIALIZABLE": return .serializable
default: return nil
}
}
static func current(from conn: MySQLConnection) -> EventLoopFuture<MySQLDatabase.IsolationLevel> {
let sessionColumn = MySQLDatabase.IsolationLevel.sessionColumn
return conn.simpleQuery("SELECT " + sessionColumn).map { data in
let raw = data.first?[.init(name: sessionColumn)]?.string()
guard let string = raw, let level = MySQLDatabase.IsolationLevel.parse(string) else {
throw MySQLError(
identifier: "unknowIsolationLevel",
reason: "Received unknown MySQL isolation level `\(raw ?? "NULL")` from database"
)
}
return level
}
}
var query: String {
return "SET GLOBAL TRANSACTION ISOLATION LEVEL " + self.rawValue
}
}
}
extension DatabaseConnectable {
func transaction<T>(
on db: DatabaseIdentifier<MySQLDatabase>,
level: MySQLDatabase.IsolationLevel,
_ closure: @escaping (MySQLConnection)throws -> Future<T>
) -> Future<T>
{
return databaseConnection(to: db).flatMap { conn in
return MySQLDatabase.IsolationLevel.current(from: conn).flatMap { sessionLevel in
return conn.simpleQuery(level.query).transform(to: sessionLevel)
}.flatMap { sessionLevel in
return conn.simpleQuery("START TRANSACTION").transform(to: sessionLevel)
}.flatMap { sessionLevel in
return try closure(conn).flatMap { result in
return conn.simpleQuery("COMMIT").transform(to: result)
}.flatMap { result in
return conn.simpleQuery(sessionLevel.query).transform(to: result)
}.catchFlatMap { error in
return conn.simpleQuery("ROLLBACK").flatMap { _ in
return conn.simpleQuery(sessionLevel.query)
}.map { _ in
throw error
}
}
}
}
}
}
It could probably be better, but it's a start. To call it, you just add the level parameter to the normal .transaction call:
connection.transaction(.mysql, level: .uncommitted) { transaction in
// Do stuff here...
}
Is there any way to set the isolation level for a given transaction without setting it globally? Given that connections are re-used in Fluent, I don't think it would be a good idea to set it globally.
I tired to do that. In theory you should be able to to set it without the GLOBAL keyword:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
But for some reason that didn't seem to work, as though I had never set the isolation level at all. This might need more research.
@0xTim what do you think the effort level would be like to get this added to Fluent? Right now we have a case where a nightly long-running transaction kicked off by a client is blocking access to the site because it's locking up a bunch of tables that get called frequently. We (Transeo) would be happy to contribute dev effort towards this if we got pointed in the right direction.
Honestly I'm not sure, it's outside my SQL knowledge. @gwynne any idea? From the looks of @calebkleveter 's implementation it should be fairly straight forward, we'd just need to add it to MySQL and Postgres as they'll be the only ones that support it I think
Sorry for the delay in replying! To answer @jdmcd's question: Getting this added at the FluentKit/SQLKit layers is actually pretty straightforward, just need an extra property in SQLDialect and to update the drivers that do provide isolation level support, which is just MySQL and Postgres (arguably SQLite's BEGIN EXCLUSIVE TRANSACTION can be considered an isolation level as well, but that's probably overdoing it since it'd require a whole separate set of syntax support, as with the mess that is enum handling). I could probably do the work in an hour or two if I put my mind to it.
@gwynne did you happen to have any update on this? It's a feature that I'd find very useful.
I tired to do that. In theory you should be able to to set it without the
GLOBALkeyword:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDBut for some reason that didn't seem to work, as though I had never set the isolation level at all. This might need more research.
@calebkleveter did you initiate the transaction first? According to this, if you don't do that, the isolation level won't be considered.
The most common issues when trying to do this with Fluent are:
- For MySQL users,
SET TRANSACTION ISOLATION LEVELcan not be issued through the full query interface that FluentKit and SQLKit use, you have to drop down to MySQLKit-level API to use thesimpleQuerycommand. - More globally, the isolation level setting is per-connection, and unless explicitly told not to, Fluent can send any given query on any connection it has to the database on the same event loop, including opening a new one if all existing connections are considered busy at the time. The only ways around this are to either use the
Database.withConnection(_:)API to request a specific connection to issue the isolation level query on and then callDatabase.transaction(_:)on that connection, or to setmaxConnectionsPerEventLoopto1so there will only ever be one connection on any given event loop (which is almost certainly not what you want).
The API I have in mind for providing transaction isolation level support will take care of both of these problems transparently.
