duanji7881
duanji7881
2015-01-02 11:09
浏览 170
已采纳

在一个事务中用golang进行多个查询的惯用方式

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douqi2804
    douqi2804 2015-01-02 11:39
    已采纳

    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()
    }
    
    点赞 评论
  • duangan4070
    duangan4070 2017-06-13 12:57

    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
    }
    
    点赞 评论

相关推荐