MySQL学习TCL事务
Transaction Control Language 事务控制语言
1、事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
案例:转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name=‘张三丰’
意外
update 表 set 郭襄的余额=1500 where name=‘郭襄’
2、事务的特性:
ACID
事务的ACID(acid)属性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么 都发生,要么都不发生。 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 - 隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的,并发 执行的各个事务之间不能互相干扰。 - 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影 响
3、事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事务:只对当前会话有效
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…
步骤3:结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没 有采取必要的隔离机制, 就会导致各种并发问题:
(1)脏读: 对于两个事务T1, T2, T1 读取了已经被T2 更新但还没有被提交的字段. 之后, 若T2 回滚, T1读取的内容就是临时且无效的.
(2)不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
(3)幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后T2 在该表中插 入了一些新的行. 之后, 如果T1 再次读取同一个表, 就会多出几行.
4、事务的隔离级别:
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔 离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就 越好, 但并发性越弱.
脏读 | 不可重复读 | 幻读 | |
---|---|---|---|
read uncommitted(读未提交数据) | √ | √ | √ |
read committed(读已提交数据) | × | √ | √ |
repeatable read(可重复度) | × | × | √ |
serializable(串行化) | × | × | × |
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
开启事务的语句;
update 表 set 张三丰的余额=500 where name=‘张三丰’
update 表 set 郭襄的余额=1500 where name=‘郭襄’
结束事务的语句;
SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;
#1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#commit;
SELECT * FROM account;
#2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
5、事务隔离级别演示
(1)读未提交数据
设置为最低级别:读未提交数据
修改name但还没提交回滚事务,没有结束
再开个连接
也修改默认级别为读未提交
开启一个新事务
我们会发现虽然事务1中还没有提交事务,但事务2中读到的已经是改变后的名字,这就是脏读
当我们将事务1回滚:
再在事务2中查询:
发现是改变前的数据,这是不可重复读和幻读
(2)读已提交数据
设置为读已提交数据
开启事务并更改:
也设置为读已提交数据,并开启事务,查询
没有改成张飞,这说明已经避免的脏读,但是依然没有避免不可重复读和幻读,如下:
将事务1提交:
事务2再查询:
和一开始查出来是john不一样,这就是不可重复读。没有避免
(3)可重复读
修改两个为可重复度:
开启事务1,并修改
但不提交
开启事务2,查询:
查询出来的是修改前的数据,说明没有脏读
事务1提交
事务2再次查询,就已经和上次查询一样了,说明避免了不可重复读,但幻读依然没有解决
幻读效果演示:
一般是针对插入
在可重复度隔离级别下:
事务1线查询:
事务1想更新所有记录的名字,但还没回车执行:
此时,事务2插入一条新的数据(此时记录变成了4行):
事务1的修改记录名字回车执行,事务1中修改所有记录的姓名:
影响数为4行,(因为不知道事务2新增了新的数据,之前明明是三条记录,怎么四条记录受到影响?)这就是胡幻读。
也就是,事务1在准备修改记录时,此时事务2在事务1修改命令结束前先增加了一条数据,使得事务1的修改记录影响数变化,这就幻读,事务1并不知道发生了什么,为什么会这样
(4)串行化
修改两个的隔离级别为串行化:可以解决任何问题
查看是否解决幻读:
事务1准备修改记录(还回车未执行):
事务2新增数据:
回车并没有反应:阻塞,相当于锁,在等待事务1提交释放锁。
现在即使事务1回车,更新了4条数据,事务2的新增依然没有反应:
直到事务1提交事务:
事务2才有反应:
但也没有操作成功,因为等待超时了。