TCL-事物隔离级别

原创已于 2023-10-01 14:05:19 修改 · 67 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2020-02-28 23:48:00 首次发布

数据库专栏收录该内容

40 篇文章

订阅专栏

本文详细介绍了事务控制语言的功能，包括处理意外中断、事务的ACID属性，以及如何在SQL中使用事务。此外，还对比了DELETE与TRUNCATE在事务中的不同行为，并探讨了并发事务可能引发的问题及解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

事物控制语言：用于处理意外中断

事物

一个或一组SQL语句组成一个执行单元，要么全部执行，要么全不执行

执行失败、产生错误，整个单元将会回滚。(撤销所有操作)

所有影响的数据返回到事物开始前的状态。

如果单元中所有SQL语句执行成功，则事物被顺利执行

事物具备的属性：ACID(acid)属性

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

删除就是删了，没办法撤销，只能重新插入

查看MySQL支持的存储引擎

SHOW ENGINES;

InnoDB	当前服务器默认的存储引擎	支持事物
MEMORY	使用的也很多	不支持事物
MyISAM	5.5版本之前使用的	不支持事物

事物的创建

隐式事物(自动事物)

事物没有明显的开启和结束的标记(自动的开启自动的结束)
比如insert、update、delete语句

显示事物

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

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

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

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

显示自动提交的状态

SHOW VARIABLES LIKE 'autocommit';

autocommit自动提交

value on 表示开启

1.事物控制的步骤：

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

MySQL没有办法演示事物出现异常回滚，没有异常提交，jdbc可以。

单独无法达到更具情况，必须联合应用程序

#演示事物的使用步骤

#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的操作被撤销了

4.并发事物

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

类似多线程的线程安全，五个人同时上一个厕所，所以要启用加锁的方式，实现线程同步，解决

脏读：读到了其他事物更新但没有提交的数据

不可重复读：一个事物多次读取，到的结果不一样。

幻读：读到了其他事物插入但没提交的数据
第一个事物更新之前是三条数据，其他事物还没有提交或添加。这时候第二个事物提交了数据，第一个事物一更新影响了四行数据。

可以通过设计数据库的隔离级别：来解决并发问题。

从上往下级别逐次增高，性能从上往下逐次降低

	脏读	不可重复读	幻读
读未提交 read uncommitted	√	√	√
读已提交 read committed	×	√	√
可重复读 repeatable read	×	×	√
串行化 serializable	×	×	×

MySQL默认第三个隔离级别：repeatable read
oracle默认第二个隔离级别：read committed，还支持第四个：serializable

查看隔离级别

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

select @@tx_isolation

设置当前隔离级别

set session transaction isolation level 隔离级别;

设置全局隔离级别

set global transaction isolation level 隔离级别;