MySQL事务精讲
1.事务初识
事务简单来说就是要做的事情,我每天需要做的事情就是开心,这就是一个事务。在MySQL中也是如此,一个操作就是一个事务。都说技术源于生活,接着举个生活中的例子。比如老板给员工发了2000元工资,一个完整的操作包含了老板账户扣除2000,和员工账户加2000,如果运行到一半中止了,就必须通过某种机制保证执行过程的完整性,那么要么全部执行完或者全部不执行。而这些要求的保证就是事务。其次应该将已经执行完成是事务尽早COMMIT写入到磁盘上,从而保证数据持久化。
2.ACID测试
前提:支持事务的关系型数据库必须满足ACID测试,例如MySQL的InnoDB引擎支持,Myisam不支持;
A:原子性:一个操作可能有多组SQL语句,事务需要保证全部运行要么全部回滚;
C:一致性:数据库总是从一个一致性状态转移到另一个一致性状态;
I: 隔离性:在一个事务所做出的操作在提交之前,是不能被其他事务看到;
D:持久性:一旦事务提交,则其所做的修改就会永久保存到数据库中,其次此时即使数据库崩溃,数据也不会丢失;
3.事务运行过程
开启一个事务
start transaction;
结束事务的两种方式
COMMIT #提交事务
ROLLBACK #回滚事务
如何关闭自动提交功能(个人建议)
4.事务保存点
简介:类似于Ansible中定义的tag,可以在运行playbook中指明只运行的tag。而保存点是恢复到保存的位置。
eg.
创建一个保存点
DELETE from student WHERE UID=20;
SAVEPOINT sp1; #创建保存点并命名为sp1;
恢复保存点sp1
ROLLBACK TO sp1;
删除保存点sp1
RELEASE SAVEPOINT sp1;
5.MySQL隔离级别
READ UNCOMMITTED (读未提交)
READ COMMITED (读提交)
REPOATABLE READ (可重读)
SERIALIZABILE (可串行化)
注释:MySQL默认的事务隔离级别是第三级,隔离级别越低,问题越大;
事务隔离级别存在的问题
脏读 | 不可重复读 | 幻读 | 加锁读 | |
READ UNCOMMITTED | √ | √ | √ | × |
READ COMMITTED | × | √ | √ | × |
REPOATABLE READ | × | × | √ | × |
SERIALIZABILE | × | × | × | √ |
注解:安全级别越高,并发处理越差。并发场景不建议用SERIALIZABILE级别,虽然安全性是最高的,但是为了保证结果的一致性其开销实在是太大了。
问题详解
脏读:读到别人未提交的数据。 脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
eg. 1.Tom的中彩票为1000, 后台人员将Tom的中奖金额改为了500万(但未提交事务)
2.Tom读取自己的中奖金额 ,发现自己的中奖金额变为了500万,欢天喜地!
3.而后台人员发现操作有误,回滚了事务,Tom的奖金又变为了1000
像这样,Tom记取的奖金500万就是一个脏数据。
不可重复读:两次读到的数据不一致,只有提交过的数据才能看到。指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。两次读到的数据不一致,只有提交过的数据才能看到。
eg. 1.在事务1中,Tom 读取了自己的奖金2000,操作并没有完成;
2.在事务2中,这时财务人员修改了Tom的工资为8000,并提交了事务;
3.在事务1中,Tom 再次读取自己的工资时,工资变为了8000;
解决办法:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。
幻读:两端开启事务,A删除第3行数据时未提交,在B依旧能看到。接着A在COMMIT之后B依旧看不到。底层已经没有数据,但是B依旧能看到,这就是幻读。
eg. 目前公司所有的员工一共有30人;
1.事务1,读取所有的员工人数为30人;
2.这时新来一个员工,事务2向employee表插入了一条员工记录,并且执行COMMIT提交;
3.事务1再次读取所有的员工人数依旧是30条记录;
解决办法:只有在事务一也执行COMMIT时看到的结果才是一致的;
加锁读:数据加锁,别人读不到(串行化)。
例子:在使用select 语句时候为了保证所看到的结果是一致的,此时另外一个事务中进行修改操作会直接阻塞,执行超时,说明执行过程有一定的先后顺序,其并发效率是最低的,在得不到确定结果之前不允许操作,但是安全性却是最高的。