Go language operation MySQL transaction operation

Tips: This article needs about 5 minutes and 9 seconds to read. Please give me more advice on the inadequacies. Thank you for reading. Subscribe to this site

affair

Database transaction is a sequence of database operations that access and may operate various data items. These operations are either all executed or not executed. It is an inseparable work unit. A transaction consists of all database operations performed between the start and end of the transaction.

MySQL storage engines are classified into MyISAM, Innodb, Memory, Merge, etc., but the most commonly used are MyISAM and Innodb. Of these two engines, Innodb (the default MySQL engine) is the engine that supports transactions. Pay attention to the corresponding engine when creating databases.

Here you can see the article on MySQL selection engine:

How to choose MySQL storage engine - Debug Inn gracefully

Transaction ACID

Generally, transactions must meet four conditions (ACID): Atomicity (or indivisibility), Consistency, Isolation (also known as independence) and persistence.

condition explain
Atomicity All operations in a transaction are either completed or not completed, and will not end in an intermediate phase. If an error occurs during the execution of a transaction, it will be rolled back to the state before the transaction starts, as if the transaction has never been executed.
Consistency Before the transaction starts and after the transaction ends, the integrity of the database is not destroyed. This means that the written data must fully comply with all the preset rules, including the accuracy and serialization of the data, and the subsequent database can automatically complete the scheduled work.
Isolation The database allows multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including Read uncommitted, read committed, repeatable read, and Serializable.
Persistence After the transaction is completed, the modification to the data is permanent and will not be lost even if the system fails.

Database ACID

Go operation MySQL usage transaction

The Go language uses the following three methods to implement transaction operations in MySQL:

// Start transaction
func (db *DB) Begin() (*Tx, error)
// Rollback transaction
func (tx *Tx) Rollback() error
// Commit transaction
func (tx *Tx) Commit() error

Example code:

// Transaction update operation
func transActionUpdate() {
    tx, err := db.Begin()
    if err != nil {
        if tx != nil {
            _ = tx.Rollback()
        }
        fmt.Printf("begin trans action failed, err:%v\n", err)
        return
    }
    sqlStr1 := "UPDATE user SET age = ? WHERE id = ?"
    result1, err := tx.Exec(sqlStr1, 20, 1)
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    n1, err := result1.RowsAffected()
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec result1.RowsAffected() failed, err:%v\n", err)
        return
    }
    sqlStr2 := "UPDATE user SET age = ? WHERE id = ?"
    result2, err := tx.Exec(sqlStr2, 20, 6)
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    n2, err := result2.RowsAffected()
    if err != nil {
        _ = tx.Rollback()
        fmt.Printf("exec result1.RowsAffected() failed, err:%v\n", err)
        return
    }

    if n1 == 1 && n2 == 1 {
        _ = tx.Commit()
        fmt.Printf("transaction commit success\n")
    } else {
        _ = tx.Rollback()
        fmt.Printf("transaction commit error, rollback\n")
        return
    }
}

Reference (learning) article: Go language operation MySQL - liwenzhou's personal blog

Tags: MySQL Go Transaction

Posted by jolle99 on Tue, 31 May 2022 13:00:05 +0530