secmask secmask - 2 months ago 7
MySQL Question

mysql safe read - update - write

I'm try to understand mysql lock record mechanic, this is show case in Golang
or here https://play.golang.org/p/2fGKEyh0Wl

it run 2 concurrent transactions, and they read-update on the same row

- the first transaction will try to lock the row, do something (sleep for 3 secs)

- the second then try to read-update on the same key

the test source code

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)
}


result from my terminal

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}


it seem the second routine has not been lock ???

Answer

You misused the transaction. The db is not in transaction, only tx is. So, the statements in first and second go routine which are

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)

will not lock the row, because db is not wrapped in transaction. You must use tx to execute the query, i.e.

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)

After modification, I got the following result:

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}
Comments