main.go

package main

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

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

func main() {
        dsn := "abc:abc@tcp(127.0.0.1:3306)/account_db?timeout=10s&readTimeout=10s&allowNativePasswords=True"
        wDB, err := sql.Open("mysql", dsn)
        if err != nil {
                panic(err)
        }
        wDB2, err := sql.Open("mysql", dsn)
        if err != nil {
                panic(err)
        }

        if wDB == nil || wDB2 == nil {
                panic("config error")
                return
        }
        defer wDB.Close()
        defer wDB2.Close()

        testDeadLock(wDB, wDB2)
}

func Atomic(db *sql.DB, txFunc func(tx *sql.Tx) error) (err error) {
	var tx *sql.Tx
	if tx, err = db.Begin(); err != nil {
		log.Printf("begin error: %v", err)
		return
	}
	defer func() {
		if err == nil {
			if err = tx.Commit(); err != nil {
				log.Printf("commit error: %v", err)
				return
			}
		} else {
			_ = tx.Rollback()
		}
	}()

	err = txFunc(tx)
	return
}

func testDeadLock(db1 *sql.DB, db2 *sql.DB) {
	db1.SetMaxOpenConns(100)
	db1.SetMaxIdleConns(100)
	db1.SetConnMaxLifetime(time.Hour)

	db2.SetMaxOpenConns(100)
	db2.SetMaxIdleConns(100)
	db2.SetConnMaxLifetime(time.Hour)

	var wg sync.WaitGroup
	wg.Add(2)
	log.Println("start")

	go func() {
		defer wg.Done()

		_ = Atomic(db1, func(tx *sql.Tx) error {
			// A
			if rows, err := tx.Query("select * from account_tab where userid = 203802 for update"); err != nil {
				log.Printf("first:A: query account_tab error: %v", err)
				return err
			} else {
				cnt := 0
				for rows.Next() {
					if rows.Err() != nil {
						log.Printf("first:A: error: %v", rows.Err())
					}
					cnt += 1
				}
				if e := rows.Close(); e != nil {
					log.Printf("first:A: close rows error: %v", e)
				}
				if e := rows.Close(); e != nil {
					log.Printf("first:A: close rows error: %v", e)
				}
				log.Printf("first A done, query account_tab count: %v", cnt)
			}

			time.Sleep(5 * time.Second)

			// B
			if rows, err := tx.Query("select * from account_audit_tab where userid = 203802 for update"); err != nil {
				log.Printf("first B: query account_audit_tab error: %v", err)
				return err
			} else {
				cnt := 0
				for rows.Next() {
					if rows.Err() != nil {
						log.Printf("first B: error: %v", rows.Err())
					}
					cnt += 1
				}
				if e := rows.Close(); e != nil {
					log.Printf("first B: close rows error: %v", e)
				}
				if e := rows.Close(); e != nil {
					log.Printf("first B: close rows error: %v", e)
				}
				log.Printf("first B done, query account_audit_tab count: %v", cnt)
			}

			return nil
		})

		log.Println("first done")
	}()

	go func() {
		defer wg.Done()

		_ = Atomic(db2, func(tx *sql.Tx) error {
			// B
			if rows, err := tx.Query("select * from account_audit_tab where userid = 203802 for update"); err != nil {
				log.Printf("second:B: query account_audit_tab error: %v", err)
				return err
			} else {
				cnt := 0
				for rows.Next() {
					if rows.Err() != nil {
						log.Printf("second:B: error: %v", rows.Err())
					}
					cnt += 1
				}
				if e := rows.Close(); e != nil {
					log.Printf("second:B: close rows error: %v", e)
				}
				if e := rows.Close(); e != nil {
					log.Printf("second:B: close rows error: %v", e)
				}
				log.Printf("second B done, query account_audit_tab count: %v", cnt)
			}

			time.Sleep(5 * time.Second)

			// A
			if rows, err := tx.Query("select * from account_tab where userid = 203802 for update"); err != nil {
				log.Printf("second:A: query account_tab error: %v", err)
				return err
			} else {
				cnt := 0
				for rows.Next() {
					if rows.Err() != nil {
						log.Printf("second:A: error: %v", rows.Err())
					}
					cnt += 1
				}
				if e := rows.Close(); e != nil {
					log.Printf("second:A: close rows error: %v", e)
				}
				if e := rows.Close(); e != nil {
					log.Printf("second:A: close rows error: %v", e)
				}
				log.Printf("second A done, query account_tab count: %v", cnt)
			}

			return nil
		})

		log.Println("second done")
	}()

	wg.Wait()
	log.Println("all done")
}