WARNING: SET TRANSACTION can only be used in transaction blocks
Spiral's database driver first sets the isolation level and then begins the transaction. That's the expected approach for MySQL, but not for PostgreSQL, unfortunately. According to PostgreSQL docs:
If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect.
It is possible to dispense with SET TRANSACTION by instead specifying the desired transaction_modes in BEGIN or START TRANSACTION. But that option is not available for SET TRANSACTION SNAPSHOT.
This is a serious bug for critical use cases where strict ACID guarantees are required.
wolfy-j
commented
4 years ago
I've noticed the following line in my db logs:
Spiral's database driver first sets the isolation level and then begins the transaction. That's the expected approach for MySQL, but not for PostgreSQL, unfortunately. According to PostgreSQL docs:
This is a serious bug for critical use cases where strict ACID guarantees are required.