mysql数据库脚本
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` decimal(10,2) NOT NULL DEFAULT '0.00',
`version` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of `user`
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES ('1', '12313', '9999.00', '1'), ('2', '12313', '9999.00', '2');
COMMIT;
for update 的条件是 balance = 8888 balance非索 则表级锁
package main
import (
"fmt"
"game_server/log"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
"time"
)
type (
User struct {
Id int64
Name string `xorm:"varchar(255) notnull 'name'"`
Balance float32 `xorm:"float notnull 'balance'"`
//Version int `xorm:"version"`
}
)
var (
mysqlEngine *xorm.Engine
err error
)
func main() {
mysqlEngine, err = xorm.NewEngine("mysql", "root:123456@tcp(localhost:3306)/test?charset=utf8")
if err != nil {
log.Fatal("init mysql failed [%+v]", err)
}
mysqlEngine.ShowSQL(true)
go TestTransaction()
time.Sleep(2 * time.Second)
user := new(User)
user.Id = 2
user.Name = "12313"
user.Balance = 9999
go TestUpdate(user)
time.Sleep(30 * time.Second)
}
func TestTransaction() {
session := mysqlEngine.NewSession()
defer session.Close()
err = session.Begin()
if err != nil {
panic(err)
}
session.Query("select * from user where balance = 8888 for update")
//session.Where("balance = ?", 8888).ForUpdate() //不起作用
session.Where("id = ?", 1).Update(&User{
Balance: 88837,
})
time.Sleep(20 * time.Second)
session.Commit()
}
func TestUpdate(user *User) {
//使用context设置超时
//ctx,cancelFunc := context.WithTimeout(context.TODO(), 5*time.Second)
//defer cancelFunc()
//affected, err := mysqlEngine.Context(ctx).Exec("update user set name = ?, balance = ? where id = ?", user.Name, user.Balance, user.Id)
//fmt.Printf("######## %#v, %+v\n", affected, err)
//cancelFunc()
//系统自动超时
mysqlEngine.Exec("update user set name = ?, balance = ? where id = ?", user.Name, user.Balance, user.Id)
fmt.Printf("执行在COMMIT前 就是没有锁\n")
}
执行结果:
[xorm] [info] 2019/09/15 20:33:33.212445 [SQL] BEGIN TRANSACTION
[xorm] [info] 2019/09/15 20:33:33.212653 [SQL] select * from user where balance = 8888 for update
[xorm] [info] 2019/09/15 20:33:33.216061 [SQL] UPDATE `user` SET `balance` = ? WHERE (id = ?) []interface {}{88837, 1}
[xorm] [info] 2019/09/15 20:33:35.207591 [SQL] update user set name = ?, balance = ? where id = ? []interface {}{"12313", 9999, 2}
[xorm] [info] 2019/09/15 20:33:53.222873 [SQL] COMMIT
执行在COMMIT前 就是没有锁
没有使用for update ,但是事务里面的条件是主键ID 为 1, TestUpdateVersion 函数更新的是ID为1 , 有锁
func main() {
user := new(User)
user.Id = 1
user.Name = "12313"
user.Balance = 9999
}
func TestTransaction() {
....
session.Where("id = ?", 1).Update(&User{
Balance: 88837,
})
...
}
[xorm] [info] 2019/09/15 20:35:09.674575 [SQL] BEGIN TRANSACTION
[xorm] [info] 2019/09/15 20:35:09.674927 [SQL] UPDATE `user` SET `balance` = ? WHERE (id = ?) []interface {}{88837, 1}
[xorm] [info] 2019/09/15 20:35:11.669606 [SQL] update user set name = ?, balance = ? where id = ? []interface {}{"12313", 9999, 1}
[xorm] [info] 2019/09/15 20:35:29.679462 [SQL] COMMIT
执行在COMMIT前 就是没有锁
没有使用for update, 事务条件ID为1 ,TestUpdateVersion函数更新ID为2的数据,ID为2的数据没有锁,所以事务条件为语句,是行级锁
func main() {
user := new(User)
user.Id = 2
user.Name = "12313"
user.Balance = 9999
}
func TestTransaction() {
....
session.Where("id = ?", 1).Update(&User{
Balance: 88837,
})
...
}
[xorm] [info] 2019/09/15 20:38:23.217078 [SQL] BEGIN TRANSACTION
[xorm] [info] 2019/09/15 20:38:23.217437 [SQL] UPDATE `user` SET `balance` = ? WHERE (id = ?) []interface {}{88837, 1}
[xorm] [info] 2019/09/15 20:38:25.207137 [SQL] update user set name = ?, balance = ? where id = ? []interface {}{"12313", 9999, 2}
执行在COMMIT前 就是没有锁
[xorm] [info] 2019/09/15 20:38:43.221392 [SQL] COMMIT
for update条件为 id = 2 , 事务里面update 条件 id = 1, 结果产生了锁,所以 for update 条件为索引,则锁行
func main() {
...
user := new(User)
user.Id = 2
user.Name = "12313"
user.Balance = 9999
...
}
func TestTransaction() {
...
session.Query("select * from user where id = 2 for update")
session.Where("id = ?", 1).Update(&User{
Balance: 88837,
})
...
}
[xorm] [info] 2019/09/15 20:41:29.932293 [SQL] BEGIN TRANSACTION
[xorm] [info] 2019/09/15 20:41:29.932448 [SQL] select * from user where id = 2 for update
[xorm] [info] 2019/09/15 20:41:29.933547 [SQL] UPDATE `user` SET `balance` = ? WHERE (id = ?) []interface {}{88837, 1}
[xorm] [info] 2019/09/15 20:41:31.925843 [SQL] update user set name = ?, balance = ? where id = ? []interface {}{"12313", 9999, 1}
[xorm] [info] 2019/09/15 20:41:49.939323 [SQL] COMMIT
执行在COMMIT前 就是没有锁
事务中使用了balance = 9999.00 是id为1的 ,balance非索引,有锁, (表级锁)
func main() {
...
user := new(User)
user.Id = 2
user.Name = "12313"
user.Balance = 9999
...
}
func TestTransaction() {
...
session.Where("balance = ?", 9999.00).Update(&User{
Balance: 88837,
})
...
}
[xorm] [info] 2019/09/15 20:54:48.451605 [SQL] BEGIN TRANSACTION
[xorm] [info] 2019/09/15 20:54:48.451942 [SQL] UPDATE `user` SET `balance` = ? WHERE (balance = ?) []interface {}{88837, 9999}
[xorm] [info] 2019/09/15 20:54:50.448837 [SQL] update user set name = ?, balance = ? where id = ? []interface {}{"12313", 9999, 2}
[xorm] [info] 2019/09/15 20:55:08.457835 [SQL] COMMIT
执行在COMMIT前 就是没有锁