我试图理解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}
看起来第二个例程没有被锁定???
SET balance=balance+?
这样的原子操作来执行,这是可靠的。使用事务而不是锁定,以确保原子性和完整性。 - tadmantx.Beginx
),并将mysql隔离级别设置为SERIALIZABLE
,但它仍然没有像预期的那样工作。我的情况需要在外部读取行,验证某些内容,然后使用新值更新它,它应该确保自上次查询以来余额未更改,有没有一种方法可以实现这一点? - secmask