mysql安全读-更新-写

Question

我试图了解 mysql 锁记录机制，这是 Golang 中的展示案例或这里 https://play.golang.org/p/2fGKEyh0Wl

它运行 2 个并发事务，并且它们在同一行上读取更新
- 第一个事务将尝试锁定行，做一些事情(休眠 3 秒)
- 第二个然后尝试在同一个键上读取更新

测试源码

package main

import (
    "github.com/jmoiron/sqlx"
    "github.com/satori/go.uuid"
    "log"
    "sync"
    "time"
    _ "github.com/go-sql-driver/mysql"
)

type Wallet struct {
    ID      string
    Balance int64
}

func main() {
    db, err := sqlx.Connect("mysql", "root:abc123@tcp(mysql:3306)/test?parseTime=true")
    if err != nil {
        log.Println(err)
        return
    }
    db.Exec(`CREATE TABLE test_wallet (
    id varchar(64) NOT NULL,
    balance bigint(20) DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    `)
    var wg sync.WaitGroup
    wg.Add(2)

    wID := uuid.NewV4().String()
    db.Exec("INSERT INTO test_wallet (id,balance) VALUES (?,?)", wID, 10)

    go func() {
        defer wg.Done()
        tx, err := db.Beginx()
        w1 := &Wallet{}
        err = db.Get(w1, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID) // read and lock the record
        if err != nil {
            log.Println(err)
        }
        log.Printf("got %+v on r1\n", w1)
        time.Sleep(time.Second * 3)
        res, err := tx.Exec("UPDATE test_wallet SET balance=? WHERE id=?", w1.Balance+5, wID)
        if err != nil {
            log.Println(err)
        }
        n, err := res.RowsAffected()
        if n != 1 {
            log.Println("update not affected r1")
        }
        tx.Commit()
        log.Println("done on r1")
    }()

    time.Sleep(time.Second) // make sure go-routine lock `id` row

    go func() {
        defer wg.Done()
        tx, err := db.Beginx()
        w2 := &Wallet{}
        err = db.Get(w2, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID)
        if err != nil {
            log.Println(err)
        }
        log.Printf("got %+v on r2\n", w2)
        res, err := tx.Exec("UPDATE test_wallet SET balance=? WHERE id=?", w2.Balance+7, wID)
        if err != nil {
            log.Println(err)
        }
        n, err := res.RowsAffected()
        if n != 1 {
            log.Println("update not affected r2")
        }
        tx.Commit()
        log.Println("done on r2")
    }()
    wg.Wait()
    w := &Wallet{}
    err = db.Get(w, "SELECT * FROM test_wallet WHERE id=?", wID)
    if err != nil {
        log.Println(err)
    }
    log.Printf("%+v\n", w)
}

我终端的结果

2016/10/01 09:57:00 got &{ID:aab7165c-4b3b-406d-b1d0-caf3f45f72be Balance:10} on r1
2016/10/01 09:57:01 got &{ID:aab7165c-4b3b-406d-b1d0-caf3f45f72be Balance:10} on r2
2016/10/01 09:57:01 done on r2
2016/10/01 09:57:03 done on r1
2016/10/01 09:57:03 &{ID:aab7165c-4b3b-406d-b1d0-caf3f45f72be Balance:15}

好像第二个routine没有被lock ???

Answer 1

您滥用了交易。 db 不在事务中，只有tx在。因此，第一个和第二个 go 例程中的语句是

err = db.Get(w1, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID) // read and lock the record
err = db.Get(w2, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID)

不会锁定该行，因为 db 没有包含在事务中。您必须使用 tx 来执行查询，即

err = tx.Get(w1, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID) // read and lock the record
err = tx.Get(w2, "SELECT * FROM test_wallet WHERE id=? FOR UPDATE", wID)

修改后得到如下结果:

2016/10/01 13:26:10 got &{ID:6ff45acd-701c-458f-a17f-84cc4e982c80 Balance:10} on r1
2016/10/01 13:26:14 done on r1
2016/10/01 13:26:14 got &{ID:6ff45acd-701c-458f-a17f-84cc4e982c80 Balance:15} on r2
2016/10/01 13:26:14 done on r2
2016/10/01 13:26:14 &{ID:6ff45acd-701c-458f-a17f-84cc4e982c80 Balance:22}