go mysql driver加锁_go-sql-driver/mysql 为什么不死锁？

事务 1：

begin;

select * from table1 where id = 1 for update;

sleep(1)

select * from table2 where id = 1 for update;

commit;

事务 2：

begin;

select * from table2 where id = 1 for update;

sleep(1)

select * from table1 where id = 1 for update;

commit;

两个表在同一个 database 里面，且两个表中记录都存在。在 MySQL 客户端肯定报死锁错误。

但是用 go-sql-driver/mysql 实现直连 MySQL，用两个 goroutine 来跑这两个事务，几乎不报死锁(加长 sleep 时间偶尔能出现 Deadlock 错误)，而且 sleep 后查出来的结果行数会为 0。

在 go-sql-driver/mysql 里加日志，发现 MySQL Server 响应包里确实不是 error。实在不知为何。请指教。

代码如下：

func Atomic(ctx context.Context, db *sql.DB, txFunc func(tx *sql.Tx)error) (err error) {

var tx *sql.Tx

if tx, err = db.Begin(); err != nil {

clog.Errorf(ctx, "begin error: %v", err)

return

}

defer func() {

if err == nil {

if err = tx.Commit(); err != nil {

clog.Errorf(ctx, "commit error: %v", err)

return

}

} else {

_ = tx.Rollback()

}

}()

err = txFunc(tx)

return

}

func testDBLocal(ctx context.Context) {

dsn := "abc:[email protected](127.0.0.1:3306)/account_db?timeout=10s&readTimeout=10s&allowNativePasswords=True"

wDB , _ := sql.Open("mysql", dsn)

wDB2, _ := sql.Open("mysql", dsn)

if wDB == nil || wDB2 == nil {

panic(errors.New("config error"))

}

wDB.SetMaxOpenConns(100)

wDB.SetMaxIdleConns(100)

wDB.SetConnMaxLifetime(time.Hour)

wDB2.SetMaxOpenConns(100)

wDB2.SetMaxIdleConns(100)

wDB2.SetConnMaxLifetime(time.Hour)

var wg sync.WaitGroup

wg.Add(2)

clog.Infof(ctx, "start")

go func() {

defer wg.Done()

ctx := clog.GetCtxWithLogid(context.Background(), "first")

_ = Atomic(ctx, wDB, func(tx *sql.Tx) error {

// A

if rows, err := tx.Query("select * from account_tab where userid = 203802 for update"); err != nil {

clog.Errorf(ctx, "query account_tab error: %v", err)

return err

} else {

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

clog.Infof(ctx, "A done")

}

time.Sleep(5*time.Second)

// B

if rows, err := tx.Query("select * from account_audit_tab where userid = 203802 for update"); err != nil {

clog.Errorf(ctx, "query account_audit_tab error: %v", err)

return err

} else {

cnt := 0

for rows.Next() {

cnt += 1

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

clog.Infof(ctx, "B done, query account_audit_tab count: %v", cnt)

}

return nil

})

clog.Infof(ctx, "first done")

}()

go func() {

defer wg.Done()

ctx := clog.GetCtxWithLogid(context.Background(), "second")

_ = Atomic(ctx, wDB2, func(tx *sql.Tx) error {

// B

if rows, err := tx.Query("select * from account_audit_tab where userid = 203802 for update"); err != nil {

clog.Errorf(ctx, "query account_audit_tab error: %v", err)

return err

} else {

cnt := 0

for rows.Next() {

cnt += 1

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

clog.Infof(ctx, "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 {

clog.Errorf(ctx, "query account_tab error: %v", err)

return err

} else {

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

if e := rows.Close(); e != nil {

clog.Errorf(ctx, "close rows error: %v", e)

}

clog.Infof(ctx, "A done")

}

return nil

})

clog.Infof(ctx, "second done")

}()

wg.Wait()

clog.Infof(ctx, "all done")

}