Bug Report: `SET TRANSACTION ...` is turned into `SET SESSION TRANSACTION ...`

[arthurschreiber]

Overview of the Issue

Vitess currently does not support SET TRANSACTION ... commands / changes to the respective @@ variables, and instead turns them into session level changes instead.

I think this is pretty dangerous to do, because read-only and isolation level changes that should've been active for only the next transaction, will suddenly be active for the rest of the session lifetime. This can lead to surprising behavior, like suddenly reading dirty data, unexpected row locking / gap locking, and other weirdness.

(Vitess is already emitting a warning if this happens, but I don't think that's enough.)

Vitess should correctly apply these session level settings only to the next transaction that's started, and then revert back to the current session settings.

Reproduction Steps

SELECT @@session.transaction_isolation; # -- 'REPEATABLE-READ'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@session.transaction_isolation; # -- 'READ_UNCOMMITTED'

Binary Version

N/A

Operating System and Environment details

N/A

Log Fragments

N/A

[Devanshusharma2005]

Hey Arthur ^^
I am exploring and learning the related prs related to this SET TRANSACTION issue and wanted to quickly sanity check my approach with you before I go deeper.
Right now in set.go, I’ve changed the NextTxScope handling so that when we see:
SET TRANSACTION ISOLATION LEVEL, instead of converting it to session scope (and emitting the warning), I think its a good idea to use new SetPendingTxIsolation primitive. The idea is to store it in SafeSession as a pendingTxIsolation, and then apply it only when the next transaction actually starts.
So the rough model I’m aiming for is:
SET TRANSACTION → store as pending override (not touching the SystemVariables btw)
On transaction start → apply override to shards
On COMMIT/ROLLBACK → clear it
@@session.transaction_isolation should always reflect session-level state only
Does that sound like the right architectural direction to you? Am I going in a right way ? Or is there an existing abstraction in VTGate that I should be reusing for transaction-scoped state?
Just wanted to confirm the direction before wiring it into the transaction lifecycle logic.

[mhamza15]

Duplicate of #18634, will close that in favor of this one.