Transaction isolation levels about fluent-kit HOT 12 OPEN

+1 for this!

from fluent-kit.

The most common issues when trying to do this with Fluent are:

1. For MySQL users, SET TRANSACTION ISOLATION LEVEL can not be issued through the full query interface that FluentKit and SQLKit use, you have to drop down to MySQLKit-level API to use the simpleQuery command.
2. 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 call Database.transaction(_:) on that connection, or to set maxConnectionsPerEventLoop to 1 so 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.

from fluent-kit.

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.

from fluent-kit.

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.

from fluent-kit.

@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.

from fluent-kit.

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

from fluent-kit.

bump @gwynne

from fluent-kit.

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.

from fluent-kit.

@gwynne did you happen to have any update on this?
It's a feature that I'd find very useful.

from fluent-kit.

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.

@calebkleveter did you initiate the transaction first?
According to this, if you don't do that, the isolation level won't be considered.

from fluent-kit.

Thanks for continuing to look into it @gwynne. 🙏🙏🙏🙏🙏

from fluent-kit.

@gwynne any update on this?

from fluent-kit.