START TRANSACTION;

COMMIT;

ROLLBACK;

START TRANSACTION;
-- 在事務(wù)中執(zhí)行一系列數(shù)據(jù)庫操作
INSERT INTO users (name, age) VALUES ('Alice', 25);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
DELETE FROM logs WHERE user_id = 1;
-- 如果一切正常，提交事務(wù)
COMMIT;

START TRANSACTION;

-- 在事務(wù)中執(zhí)行一系列數(shù)據(jù)庫操作
INSERT INTO users (name, age) VALUES ('Bob', 30);
UPDATE accounts SET balance = balance - 200 WHERE user_id = 2;

-- 發(fā)生錯誤或異常，回滾事務(wù)
ROLLBACK;

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB;

// BeginTx starts a transaction.
//
// The provided context is used until the transaction is committed or rolled back.
// If the context is canceled, the sql package will roll back
// the transaction. Tx.Commit will return an error if the context provided to
// BeginTx is canceled.
//
// The provided TxOptions is optional and may be nil if defaults should be used.
// If a non-default isolation level is used that the driver doesn't support,
// an error will be returned.
func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error) {
	var tx *Tx
	var err error
	err = db.retry(func(strategy connReuseStrategy) error {
		tx, err = db.begin(ctx, opts, strategy)
		return err
	})
	return tx, err
}
// Begin starts a transaction. The default isolation level is dependent on
// the driver.
//
// Begin uses context.Background internally; to specify the context, use
// BeginTx.
func (db *DB) Begin() (*Tx, error) {
	return db.BeginTx(context.Background(), nil)
}

// rollback aborts the transaction and optionally forces the pool to discard
// the connection.
func (tx *Tx) rollback(discardConn bool) error {
	if !tx.done.CompareAndSwap(false, true) {
		return ErrTxDone
	}
	if rollbackHook != nil {
		rollbackHook()
	}
	// Cancel the Tx to release any active R-closemu locks.
	// This is safe to do because tx.done has already transitioned
	// from 0 to 1. Hold the W-closemu lock prior to rollback
	// to ensure no other connection has an active query.
	tx.cancel()
	tx.closemu.Lock()
	tx.closemu.Unlock()
	var err error
	withLock(tx.dc, func() {
		err = tx.txi.Rollback()
	})
	if !errors.Is(err, driver.ErrBadConn) {
		tx.closePrepared()
	}
	if discardConn {
		err = driver.ErrBadConn
	}
	tx.close(err)
	return err
}
// Rollback aborts the transaction.
func (tx *Tx) Rollback() error {
	return tx.rollback(false)
}

// Commit commits the transaction.
func (tx *Tx) Commit() error {
	// Check context first to avoid transaction leak.
	// If put it behind tx.done CompareAndSwap statement, we can't ensure
	// the consistency between tx.done and the real COMMIT operation.
	select {
	default:
	case <-tx.ctx.Done():
		if tx.done.Load() {
			return ErrTxDone
		}
		return tx.ctx.Err()
	}
	if !tx.done.CompareAndSwap(false, true) {
		return ErrTxDone
	}
	// Cancel the Tx to release any active R-closemu locks.
	// This is safe to do because tx.done has already transitioned
	// from 0 to 1. Hold the W-closemu lock prior to rollback
	// to ensure no other connection has an active query.
	tx.cancel()
	tx.closemu.Lock()
	tx.closemu.Unlock()
	var err error
	withLock(tx.dc, func() {
		err = tx.txi.Commit()
	})
	if !errors.Is(err, driver.ErrBadConn) {
		tx.closePrepared()
	}
	tx.close(err)
	return err
}

package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql" // 匿名導入 自動執(zhí)行 init()
)

var db *sql.DB

func initMySQL() (err error) {
	//DSN (Data Source Name)
	dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test"
	// 注意：要初始化全局的 db 對象，不要新聲明一個 db 變量
	db, err = sql.Open("mysql", dsn) // 只對格式進行校驗，并不會真正連接數(shù)據(jù)庫
	if err != nil {
		return err
	}

	// Ping 驗證與數(shù)據(jù)庫的連接是否仍處于活動狀態(tài)，并在必要時建立連接。
	err = db.Ping()
	if err != nil {
		fmt.Printf("connect to db failed, err: %v\n", err)
		return err
	}
	// 數(shù)值需要根據(jù)業(yè)務(wù)具體情況來確定
	db.SetConnMaxLifetime(time.Second * 10) // 設(shè)置可以重用連接的最長時間
	db.SetConnMaxIdleTime(time.Second * 5)  // 設(shè)置連接可能處于空閑狀態(tài)的最長時間
	db.SetMaxOpenConns(200)                 // 設(shè)置與數(shù)據(jù)庫的最大打開連接數(shù)
	db.SetMaxIdleConns(10)                  //  設(shè)置空閑連接池中的最大連接數(shù)
	return nil
}

type user struct {
	id   int
	age  int
	name string
}

// 事務(wù)操作
func transactionDemo() {
	// 啟動事務(wù)。默認隔離級別取決于驅(qū)動程序。
	tx, err := db.Begin() // 開啟事務(wù)
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滾 中止事務(wù)
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	sqlStr1 := "UPDATE user SET age=? WHERE id=?"
	ret1, err := tx.Exec(sqlStr1, 22, 2)
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec sql1 failed, err: %v\n", err)
		return
	}
	// RowsAffected 返回受更新、插入或刪除影響的行數(shù)。并非每個數(shù)據(jù)庫或數(shù)據(jù)庫驅(qū)動程序都支持此功能。
	affRow1, err := ret1.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec ret1.RowsAffected() failed, err: %v\n", err)
		return
	}

	sqlStr2 := "UPDATE user SET age=? WHERE id=?"
	ret2, err := tx.Exec(sqlStr2, 100, 5)
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec sql2 failed, err: %v\n", err)
		return
	}
	affRow2, err := ret2.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec ret2.RowsAffected() failed, err: %v\n", err)
		return
	}

	fmt.Println(affRow1, affRow2)
	if affRow1 == 1 && affRow2 == 1 {
		fmt.Println("事務(wù)提交...")
		tx.Commit() // 提交事務(wù)
	} else {
		tx.Rollback()
		fmt.Println("事務(wù)回滾...")
	}

	fmt.Println("exec trans success!")
}

func main() {
	if err := initMySQL(); err != nil {
		fmt.Printf("connect to db failed, err: %v\n", err)
	}
	// 檢查完錯誤之后執(zhí)行，確保 db 不為 nil
	// Close() 用來釋放數(shù)據(jù)庫連接相關(guān)的資源
	// Close 將關(guān)閉數(shù)據(jù)庫并阻止啟動新查詢。關(guān)閉，然后等待服務(wù)器上已開始處理的所有查詢完成。
	defer db.Close()

	fmt.Println("connect to database success")
	// db.xx() 去使用數(shù)據(jù)庫操作...

	//	事務(wù)
	transactionDemo()
}

Code/go/mysql_demo via ?? v1.20.3 via ?? base 
? go run main.go
connect to database success
1 0
事務(wù)回滾...
exec trans success!

Code/go/mysql_demo via ?? v1.20.3 via ?? base 
? go run main.go
connect to database success
1 1
事務(wù)提交...
exec trans success!

Code/go/mysql_demo via ?? v1.20.3 via ?? base 
? 

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   16 |
|  2 | 小喬   |   12 |
|  5 | 昭君   |   12 |
|  6 | 黛玉   |   16 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> select * from user;  # 第一次執(zhí)行后查詢
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   16 |
|  2 | 小喬   |   12 |
|  5 | 昭君   |   12 |
|  6 | 黛玉   |   16 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> select * from user;   # 第二次執(zhí)行后查詢
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   16 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |  100 |
|  6 | 黛玉   |   16 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql>