Transaction Isolation Level

[repomaa]

I'm missing support for setting the transaction isolation level. Preferably directly from Connection#transaction. It could be an optional enum type argument and the default should be set in the individual implementations since the defaults differ across mysql and postgres at least. In sql standard it is SERIALIZABLE but in postgres it's READ COMMITTED

[jgaskins]

I was looking into this and realized that this may require special handling inside of the various drivers because they all seem to handle it differently. For example

DB	Query syntax
Postgres	BEGIN TRANSACTION ISOLATION LEVEL #{level};
MySQL	SET TRANSACTION ISOLATION LEVEL #{level}; BEGIN;
SQLite	all transactions are SERIALIZABLE

I don't know why MySQL requires a separate SET TRANSACTION command to use non-default isolation levels, but START TRANSACTION doesn't seem to support it. This means that the COMMIT would likely need to then reset the transaction isolation level back to the default.

Just some things to keep in mind as this feature is being developed or reviewed.