Inconsistent Behavior with Nested Transactions Depending on Parent Context

[hmdyt]

GORM Playground Link

https://github.com/go-gorm/playground/pull/659

Description

I am developing a server application using gorm—thank you for providing such a useful ORM tool. In production, we operate directly on the database itself, but in our testing environment, we utilize transactions (tx) to maintain test parallelism. This difference seems to be leading to the behavioral discrepancies as demonstrated in the playground.

Is this a bug, or is it an inevitable aspect of the nested transaction implementation? We observe that when a transaction is nested within another transaction (tx within tx), it behaves differently compared to when the parent context is the database connection itself.

We would expect the behavior to be consistent regardless of the parent context being a transaction or a direct database connection, to ensure reliability and predictability in both testing and production environments.

[ivila]

@hmdyt It's on purpose, I can explain you the logic of the Transaction method.

First of all, you might need to check about the database/sql#DB struct and database/sql#Tx struct, to have the basic knowledge of the design of Golang sql.

Then, in your codes of the reproduction steps, you have two kinds of *gorm.DB instance, both of them are the pointer of gorm.DB struct, but with different concept: 1, the db variable: it's the same as database/sql#DB, holds a connection pool inside, when you are doing operation with it, it takes a connection from its connection pool, and execute with the connection, and return it to the connection pool after the operation. 2, the tx variable: it's the same as database/sql#Tx, on its creation, it takes a connection from the connection pool, and holds it inside, every operation with the tx variable, will use the same connection.

And the Transaction method, it has judgement inside: 1, if the receiver instance is a transaction instance (what compares to database/sql#Tx), it just write a savepoint, and use the same connection to continue. 2, else the receiver instance is a db instance (what compares to database/sql#DB), it takes a connection from the connection pool, and create a tx instance.

So what you found is on design, you misuse the Transaction method, and you can't find the new created user record with db.First is because of your isolation level.

[hmdyt]

@ivila

Thank you for your prompt response and the detailed explanation. I appreciate the clarification on the design and behavior of the Transaction method in relation to the database/sql#DB and database/sql#Tx structures.

I understand that not finding the new record with db.First is expected due to the isolation level. However, my concern is specifically with the fact that the record can be found using tx.First within the nested transaction. I believe this behavior within a nested transaction context may pose a problem.

Could you provide further insight into whether this is intended behavior when dealing with nested transactions and if so, how it aligns with the isolation principles?

[ivila]

@hmdyt I think we should mainly focus on the "connection" thing, not the transaction part. For the nested transaction, you did open a nested transaction, but with a same connection. A gorm.DB instance is just like a communication guy with a sql builder, and when you are: 1, using the db variable: there are a bunch of communication guys(connection pool) and a standalone communication guy(the new created tx variable), and the tx is communicating with the server, no one else know what they are talking. 2, using the tx variable: there are always one communication guy, with a bunch of sql builder(your parentTx and childTx just differ at sql builder, like parentTx = conn1 + sql builder1, childTx = conn2 + sql builder2), but still, they share a same connection.

and for the isolation principles, in nested transaction, a parent transaction may not see the changes happened at its child transaction, but a connection can see them all, and that's why I show you the database/sql#DB and database/sql#Tx, gorm is hiding something for your convenience, but we should know what it really is.