mysql sql 事务写作_mysql事务

WHAT

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

例如，一个转账操作：

CREATE TABLE `jwentest` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`balance` int(3) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

update jwentest set balance = balance - 10 where id=1;

update jwentest set balance = balance + 10 where id=2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

HOW

mysql-demo

事务commit成功

-- 事务commit成功

BEGIN;

update jwentest set balance = balance - 10 where id=1;

update jwentest set balance = balance + 10 where id=2;

COMMIT;

事务rollback成功

-- 事务回滚

BEGIN;

update jwentest set balance = balance - 10 where id=1;

update jwentest set balance = balance + 10 where id=2;

ROLLBACK;

更明显的例子

重新开个查询窗口，逐步执行事务，在新开窗口查看到的数据和现有窗口查看到的数据，进行对比

-- 窗口A

BEGIN;

update jwentest set balance = balance - 10 where id=1;

update jwentest set balance = balance + 10 where id=2;

select * from jwentest; -- 查看数据，发现数据update成功

-- 切到窗口B

select * from jwentest; -- 查看数据，发现数据没有update

-- 切到窗口A

COMMIT;

-- 切到窗口B

select * from jwentest;

在事务处理的过程中，该mysql连接(或者说进程)把数据库表lock住了

同理ROLLBACK操作一次：

-- 窗口A

BEGIN;

update jwentest set balance = balance - 10 where id=1;

update jwentest set balance = balance + 10 where id=2;

select * from jwentest; -- 查看数据，发现数据update成功

-- 切到窗口B

select * from jwentest; -- 查看数据，发现数据没有update

-- 切到窗口A

ROLLBACK;

-- 切到窗口B

select * from jwentest;

异常case，可以讨论下

BEGIN;

update jwentest set balance = balance - 10 where id=1;

INSERT INTO `test`.`jwentest`(`id`, `balance`) VALUES (1, 1000); --Duplicate entry '1' for key 'PRIMARY'

COMMIT;

按照对事务的理解，一起成功一起失败，上面的case应该是update不成功，因为第二个sql语句是肯定失败的 ，但执行完发现：

在本窗口，数据update了的，而在其他窗口来看，其实没有update成功了的，因为事务lock住了

事务失败是程序检测到错误主动调用rollback

你忽略了错误继续调用commit那成功执行的sql就保存了

还有其他异常case：可参考： https://www.cnblogs.com/jkko123/p/10184532.html

没有手动commit，直接关闭窗口(断开连接)

手动commit

没有手动commit，直接新开事务，新开事务会自动提交会话中的事务

相关查询

先查看表是不是支持事务，innodb支持事务

SELECT

table_name,

`engine`

FROM

information_schema.TABLES

WHERE

table_name = 'jwentest';

查看事务超时时间

-- 查看lock的超时时间

show variables like 'innodb_lock_wait_timeout';

set innodb_lock_wait_timeout = 120;

查看mysql process和kill操作

-- 查看process

SELECT * from information_schema.`PROCESSLIST` where DB='test' ORDER BY TIME desc;

-- kill process

kill 12323

select * from information_schema.innodb_trx;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

python-demo

# -*- coding: utf-8 -*-

# @Time : 2020/6/15 17:59

# @Author : jwen

# @File : demo1.py

import pymysql

if __name__ == "__main__":

print("start")

# 打开数据库连接

db = pymysql.connect("host", "root", "password", "test")

# 使用 cursor() 方法创建一个游标对象 cursor

cursor = db.cursor()

try:

print("update db ...")

# 使用 execute() 方法执行 SQL 查询

sql1 = "update jwentest set balance = balance - 10 where id=1;"

cursor.execute(sql1)

# insert错误

sql2 = "INSERT INTO `test`.`jwentest`(`id`, `balance`) VALUES (1, 1000);"

# sql2 = "update jwentest set balance = balance + 10 where id=2;"

cursor.execute(sql2)

db.commit()

print("commit success")

except Exception as e:

print(e)

print("rollbacking...")

db.rollback()

# 关闭数据库连接

db.close()

实际代码

问题重现

异常case

apply素材，进去事务中，锁住数据

DB update 完毕

调用offer-sync接口同步

offer-sync服务新建连接查询数据，这个时候查询的数据还是update之前的数据，不符合素材同步的条件

commit，释放锁

正常case

apply素材，进去事务中，锁住数据

DB update 完毕

调用offer-sync接口同步

commit，释放锁

offer-sync服务新建连接查询数据，这个时候查询的数据已经是update之后的数据，符合素材同步的条件

问题解决

第三方调用不要放在事务中，再调用offer-sync的时候确保DB update成功了并释放了锁

问题引申

高并发下的话。。问题有没有解决呢 ？

并发调用apply接口修改同一份数据，第二次等待lock超时？

小结

ACID4个特性

A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；

C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；

I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；

D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读(Dirty Read)。

在Read Committed隔离级别下，一个事务可能会遇到不可重复读(Non Repeatable Read)的问题。不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

在Repeatable Read隔离级别下，一个事务可能会遇到幻读(Phantom Read)的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

参考资料