CODEWITHSUNDEEP
go
Alex
Alex

Reputation: 63

Idiomatic way to do multiple queries in golang in one transaction

I'm currently struggling (my 2nd day) to find the best way to do multiple queries and was wondering if you know a solution.

I have an open *sql.DB Connection, named myDb and use the go-sql-driver

func TruncateGalleryImport() error {

    s := make([]string, 0)

    s = append(s, "TRUNCATE TABLE add_map")
    s = append(s, "TRUNCATE TABLE album")
    s = append(s, "TRUNCATE TABLE album_permission")
    s = append(s, "TRUNCATE TABLE album_view")
    s = append(s, "TRUNCATE TABLE album_watch")
    s = append(s, "TRUNCATE TABLE media")
    s = append(s, "TRUNCATE TABLE media_user_view")
    s = append(s, "TRUNCATE TABLE media_view")
    s = append(s, "TRUNCATE TABLE media_watch")
    s = append(s, "TRUNCATE TABLE private_map")
    s = append(s, "TRUNCATE TABLE attachment")
    s = append(s, "TRUNCATE TABLE attachment_data")

    for _, q := range s {
        _, err := myDb.Exec(q)
        if err != nil {
            return err
        }
    }

    return nil
}

Is it possible to commit all the above queries together using only one transaction?

Cheers

Upvotes: 5

Views: 13515

Answers (2)

snassr
snassr

Reputation: 1548

You can use a wrapped function to do the commit/rollback logic, possibly even expand the error handling using string matching.

// RDBTransaction is a function which abstracts a sql transaction
// into a function with an isolation level (isolvl) parameter.
// the following integers represent the available isolation levels (isolvl)
//  1: SERIALIZABLE
//  2: REPEATABLE READ
//  3: READ COMMITTED
//  4: READ UNCOMMITTED
func RDBTransaction(db *sql.DB, isolvl int, fn func(*sql.Tx) error) (err error) {
    var tx *sql.Tx
    tx, err = db.Begin()
    if err != nil {
        return err
    }

    // transaction isolation level setting
    switch isolvl {
    case 1:
        _, err = tx.Exec(`set transaction isolation level serializable`)
    case 2:
        _, err = tx.Exec(`set transaction isolation level repeatable read`)
    case 3:
        _, err = tx.Exec(`set transaction isolation level read committed`)
    case 4:
        _, err = tx.Exec(`set transaction isolation level read uncommitted`)
    default:
        _, err = tx.Exec(`set transaction isolation level serializable`)
    }
    if err != nil {
        return err
    }

    // catch all, commit/rollback
    defer func() {
        if err != nil {
            tx.Rollback()
            return
        }
        err = tx.Commit()
    }()

    // run transaction
    err = fn(tx)

    return err
}

Upvotes: 3

nussjustin
nussjustin

Reputation: 2100

Use a Transaction, like this (see the comments in the code):

func TruncateGalleryImport() error {
    s := make([]string, 0)

    s = append(s, "TRUNCATE TABLE add_map")
    s = append(s, "TRUNCATE TABLE album")
    s = append(s, "TRUNCATE TABLE album_permission")
    s = append(s, "TRUNCATE TABLE album_view")
    s = append(s, "TRUNCATE TABLE album_watch")
    s = append(s, "TRUNCATE TABLE media")
    s = append(s, "TRUNCATE TABLE media_user_view")
    s = append(s, "TRUNCATE TABLE media_view")
    s = append(s, "TRUNCATE TABLE media_watch")
    s = append(s, "TRUNCATE TABLE private_map")
    s = append(s, "TRUNCATE TABLE attachment")
    s = append(s, "TRUNCATE TABLE attachment_data")

    // Get new Transaction. See http://golang.org/pkg/database/sql/#DB.Begin
    txn, err := myDb.Begin()

    if err != nil {
        return err
    }

    defer func() {
        // Rollback the transaction after the function returns.
        // If the transaction was already commited, this will do nothing.
        _ = txn.Rollback()
    }()

    for _, q := range s {
        // Execute the query in the transaction.
        _, err := txn.Exec(q)

        if err != nil {
            return err
        }
    }

    // Commit the transaction.
    return txn.Commit()
}

Upvotes: 9

Related Questions