mysql之TCL-事物隔离级别

mysql之TCL-事物隔离级别

1、事物概念

事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务。在SQL层面可以理解为一个事务，是由多条SQL组成用以完成一个业务功能的共同体，事务影响到SQL的CRUD等操作。仅INNODB搜素引擎支持事务，在MYISAM中不支持事务，但在MYISAM中使用事务操作如BEGIN和COMMIT不会报错，使用ROLLBACK无效果（不能回滚）。如果发现在代码中使用了事务但不生效的情况，可以优先查看使用到的表是否均已经使用到了事务。

一个或一组SQL语句组成一个执行单元，要么全部执行，要么全不执行。执行失败、产生错误，整个单元将会回滚。（撤销所有操作）所有影响的数据返回到事物开始前的状态。如果单元中所有SQL语句执行成功，则事物被顺利执行

2、事物四大特性：

1.A原子性：代表事物是一个不可分割的工作单位（最小单位），要么全部执行，要么全不执行
2.C一致性：执行一个事物会使数据，从一个一致的状态，切换到，另一个一致的状态。（准确、完整、可靠）
3.I隔离型：一个事物的执行，不受其他事物的干扰（隔离级别控制）
4.D持久性：一个事物一旦提交数据就是永久性的改变，接下来操作和故障不会对其产生影响

3、事物的创建

隐式事物（自动事物）：
事物没有明显的开启和结束的标记（自动的开启自动的结束）
比如insert、update、delete语句

显示事物：
事物具有明显的开启和结束的标记

将两个隐式语句和为一个事物
前提：必须禁用自动提交
禁用自动提交：set autocommit=0;
只正对当前事物有效，不是永久关闭

案例：转账
张三丰 1000
郭襄 1000
UPDATE 表 SET 张三丰余额=500 WHERE NAME=‘张三丰’
意外中断
UPDATE 表 SET 郭襄余额=1000 WHERE NAME='郭襄’3
钱错了

但是update本身就是一个事物，所以必须禁用自动提交

4、显示自动提交的状态
SHOW VARIABLES LIKE ‘autocommit’;mysql中事务默认是开启状态的

autocommit自动提交
value on 表示开启

1.事物控制的步骤：

#步骤1：开启事物
set autocommit=0;#禁用自动提交
start transaction；#可选的
#步骤2：编写事物中的SQL语句
（只支持 insert update delete，增删改查才有用。select可以使用但是没什么意义）ddl语言没有事物之说
语句1；
语句2；
…
#步骤3：结束事物
commit;提交事物，没有意外，语句可以全部执行
rollback;回滚事物，有意外回滚，语句不能全部执行

2、案例

#1.开启事物
SET autocommit=0;
START TRANSACTION;

#2.编写一组事物的语句
UPDATE account SET balance =500 WHERE username=‘张无忌’;
UPDATE account SET balance =1500 WHERE username=‘赵敏’;

#3.结束事物
COMMIT;#提交事物，更改

#1.开启事物
SET autocommit=0;
START TRANSACTION;

#2.编写一组事物的语句
UPDATE account SET balance =1000 WHERE username=‘张无忌’;
UPDATE account SET balance =1000 WHERE username=‘赵敏’;

#3.结束事物
ROLLBACK;#回滚事物，撤销

在结束事物之前数据存储在内存，并没有提交到磁盘文件。
执行到结束任务，才决定：提交到磁盘文件、还是撤销

2.delete和truncate在事物中使用时的区别
delete 可以回滚撤销
truncate 不支持回滚撤销

#delete演示
SET autocommit=0;
START TRANSACTION;

DELETE FROM account;
ROLLBACK;

SELECT * FROM account;

#truncate演示
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

3.演示 savepoint 的使用
savepoint 节点名（自定义）；设置保存点
搭配
ROLLBACK TO 节点名;#回滚到保存点，撤销保存点后的操作

#查询表中的数据
SELECT * FROM account;

#1.开启事务
SET autocommit=0;
START TRANSACTION;
#2.删除
DELETE FROM account WHERE id=1;
savepoint a;#设置保存点
DELETE FROM account WHERE id=3;
ROLLBACK TO a;#回滚到保存点
只删除了：1,3的操作被撤销了

二、事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

多个事物，同时 操作 数据库中相同数据。如果没有采取必要的隔离机制，就会导致各种并发问题

类似多线程的线程安全，五个人同时上一个厕所，所以要启用加锁的方式，实现线程同步，解决
脏读：读到了其他事物更新但没有提交的数据
不可重复读：一个事物多次读取，到的结果不一样。
幻读：读到了其他事物插入但没提交的数据
第一个事物更新之前是三条数据，其他事物还没有提交或添加。这时候第二个事物提交了数据，第一个事物一更新影响了四行数据。

设置隔离级别只针对当前连接有效，
登录一个是一个连接，查询不是连接都是一个连接下的
select @@tx_isolation

设置当前隔离级别
set session transaction isolation level 隔离级别;

设置全局隔离级别
set global transaction isolation level 隔离级别;