Go中处理MySQL死锁

package main

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

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 连接到 MySQL
	dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 确保连接成功
	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	// 执行事务
	if err := executeTransaction(db); err != nil {
		log.Printf("Transaction failed: %v", err)
	}
}

func executeTransaction(db *sql.DB) error {
	var err error

	for i := 0; i < 5; i++ { // 重试最多 5 次
		tx, err := db.Begin()
		if err != nil {
			return err
		}

		// 执行查询或更新操作
		_, err = tx.Exec("UPDATE table1 SET value1 = value1 + 1 WHERE id = 1")
		if err != nil {
			tx.Rollback()
			return err
		}

		_, err = tx.Exec("UPDATE table2 SET value2 = value2 - 1 WHERE id = 1")
		if err != nil {
			tx.Rollback()
			return err
		}

		// 提交事务
		err = tx.Commit()
		if err == nil {
			return nil // 成功
		}

		// 检查是否为死锁错误
		if isDeadlockError(err) {
			log.Printf("Deadlock detected, retrying... (%d/5)", i+1)
			time.Sleep(time.Second) // 等待一段时间后重试
			continue
		}

		// 其他错误
		return err
	}

	return fmt.Errorf("transaction failed after 5 retries: %v", err)
}

func isDeadlockError(err error) bool {
	if mysqlErr, ok := err.(*mysql.MySQLError); ok {
		// Error code 1213: Deadlock found when trying to get lock; try restarting transaction
		return mysqlErr.Number == 1213
	}
	return false
}