MySQL 安全读-更新-写

Question

我试图理解MySQL锁记录机制，以下是使用Golang演示的案例：
或者在此处查看https://play.golang.org/p/2fGKEyh0Wl

代码会运行两个并发事务，并且它们都对同一行进行读取和更新
- 第一个事务将尝试锁定该行，执行某些操作（休眠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}

看起来第二个例程没有被锁定？？？

- secmask

---

这是一个不应该使用平衡更新的典型例子，因为这样会打开竞态条件，并试图通过锁定机制来减轻这种情况，这种机制最终注定会引发问题。对于任何涉及货币或类似货币的事物，一定要记录个别的借方和贷方，永远不要基于潜在过时信息直接分配余额。而是采用像 SET balance=balance+? 这样的原子操作来执行，这是可靠的。使用事务而不是锁定，以确保原子性和完整性。 - tadman

是的，我尝试了许多解决方案，例如使用事务（我在上面使用了tx.Beginx），并将mysql隔离级别设置为SERIALIZABLE，但它仍然没有像预期的那样工作。我的情况需要在外部读取行，验证某些内容，然后使用新值更新它，它应该确保自上次查询以来余额未更改，有没有一种方法可以实现这一点？ - secmask

因为在更新“balance”之前我需要进行一些验证，所以我不能使用“SET balance=balance+？”… - secmask

验证所有你想要的，但是在你开始时使用差分更新，并且始终，始终要有一个完整的记录，说明余额是如何变成这样的。每次单独的更改都应该被记录在某个表格中作为一行。 - tadman

Answer 1

你错误地使用了事务。只有tx处于事务状态，而不是db。因此，第一个和第二个协程中的语句应为：

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}

- putu

---

@secmark，请务必阅读并理解这篇教程。Go语言处理数据库的方法与其他流行平台不同，你必须了解这些差异。 - kostix

谢谢，非常感谢！！为了这个问题我一直在苦苦思索。 - rajya vardhan