Mysql: Transaction Level

[pjebs]

I was wondering what the best way was to set the transaction level and have it revert back to default after the transaction - particularly in case of a panic:

I'm currently doing this:

    if err := gorm.Exec("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;").Error; err != nil {
        //Handle Error
        return
    }
    tx := gorm.Begin()

...

    //Do something with tx. And call `tx.Rollback()` or `tx.Commit()`

[pjebs]

Bear in mind, gorm.Exec(...) just passes raw statement to underlying mysql connection directly and Rollback() / Commit() does the obvious.

[pjebs]

No need to do anything special. Solved:
"Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level. "

[pjebs]

Actually, I had to reopen it. I am using mysql-go driver's connection pooling.

How can I guarantee that gorm.Exec(...) command will apply to the very next tx := gorm.Begin() and not to some other connection being run on another goroutine etc.

[methane]

Why don't you use tx.Exec()?
Transaction is strictly bound to connection, of course.

[pjebs]

https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed
while a transaction is in progress

The documentation states it has to be called BEFORE starting transaction.

[methane]

Have you read readme?
https://github.com/go-sql-driver/mysql#system-variables

[methane]

If you want to change variable only for one connection, possible solution is:

• Use specialized sql.DB for special case.
• Stop using database/sql. mymysql may help you.

[pjebs]

I believe that system variable changes the default transaction level for everything, whereas I only want to change the transaction level for 1 particular query.

Using sql.DB won't help. It will just use underlying mysql driver and it's connection pooling. In fact, I always create a sql.DB and feed it into gorm, without allowing gorm to manage it for me.

I've known of that alternative driver but been hesitant to use it because it doesn't have anywhere near active user base (hence very few updates).

[pjebs]

@arnehormann @julienschmidt Any suggestions?

[pjebs]

The only solution I can think of is fork this package, and rename the id that database/sql package uses to use the mysql go driver.

That way for this particular query, I can call: Open(driverName, dataSourceName string) (*DB, error) with drivername being "mysql-fork".

A messy hack unfortunately.

[methane]

Using sql.DB won't help. It will just use underlying mysql driver and it's connection pooling. In fact, I always create a sql.DB and feed it into gorm, without allowing gorm to manage it for me.

I meant having two *sql.DB, normal one and specialized one for the query.

[pjebs]

Hmmm. I'm quite confused by your suggestion.

If I create a standard one and a specialized one.
The sql.Open("mysql"...) will just use the same mysql driver and that will use it's own shared connection pool that will be shared by both sql.DB objects.

sql1= sql.Open("mysql", "XXX")
sql2= sql.Open("mysql", "XXX?tx_isolation=READ-UNCOMMITTED")

[methane]

No. Each sql.DB has own pool. Connections are not shared between separated pools.

[pjebs]

I had no idea. Thanks @methane I'll test it out.

[julienschmidt]

If you really need it per-connection, a hackish solution might be to use sql.Tx, manually abort the transaction and start a new one.

Otherwise settings can only be made per connection pool / sql.DB instance. The ?tx_isolation=XXX DSN parameter should be the way to go then.

[pjebs]

@julienschmidt Can you clarify what you mean by use sql.Tx and then abort?

Is it this?

    tx := gorm.Begin()
if err := tx.Exec("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;").Error; err != nil {
        tx.Rollback()
        return
    }

 tx.Rollback()

//What do I do now?

The documentation states SET TRANSACTION has to be called BEFORE starting transaction (See above comment).

[julienschmidt]

I don't know the gorm syntax, but it should be something like:

tx := gorm.Begin()
if err := tx.Exec("ROLLBACK").Error; err != nil { ... } // Abort
if err := tx.Exec("START TRANSACTION [options]").Error; err != nil { ... } // Start new transaction with custom options
// Do whatever you wanted to do in the transaction
tx.Commit() // Again, I don't know the syntax, but finish the transaction as usual.

However this is a quite nasty workaround and causes some extra overhead (logging, extra network round-trips etc). If possible, I would use the DSN parameter instead.

[pjebs]

thanks @julienschmidt @methane

[mayur-tolexo]

You can try this:

tx, err = dbConn.DB().BeginTx(context.Background(),
            &sql.TxOptions{Isolation: sql.LevelReadUncommitted})

but this will be of *sql.Tx type