MySQL基础——TCL语言

淡水鳗鱼的咸鱼梦
300 阅读5分钟

四、事务的SQL语句（TCL的一部分）

1、简单介绍

TCL：Transaction Control Language事务控制语言。

事务：一组sql语句要么都执行，要么都不执行（回滚）。

Innodb支持事务，Myisam、memory不支持事务。

#查看存储引擎
SHOW ENGINES;

1.1 事务的ACID原则

  • 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  • 隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

2、事务的创建

  • 隐式事务： 没有明显的开启和结束的标记，比如delete、insert、update语句；每一条语句相当于一条事务；
  • 显式事务：有明显的开启和结束的标记，必须先设置上面这种自动提交功能为禁用，这样才能保证多条sql语句同时执行时，中途出现问题，不会一条一条提交；
#关闭自动提交
SET AUTOCOMMIT = 0;

2.1 使用事务的步骤

#步骤1：开启事务
SET AUTOCOMMIT = 0;
START TRANSACTION;#这条语句不是必须的，因为关闭了自动提交功能就等同于开启了事务

#步骤2：编写事务中的sql语句
#语句1；
#语句2；

#步骤3：结束事务
COMMIT;#提交事务
ROLLBACK;#回滚事务;

delete和truncate事务的区别

delete删除可以回滚，truncate不可以，删了就没了。

3、并发情况下的事务问题

同时运行多个事务访问同一个数据资源时，可能会导致并发问题：

  • 脏读：两个事务T1、T2，T1修改了数据但未提交就被T2读取到了，如果T1回滚，T2读到的数据就是无效的了；（字段）
  • 不可重复读：两个事务T1、T2，T1读取了一个字段T2更新了，T1就读不到以前的那个值了；
  • 幻读：对两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了新行，T1再读这个表会多出几行；（插入行）

3.1 数据库的事务隔离级别

  • Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

  • Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

  • Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

  • Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

image-20210907115101506

3.2 默认事务隔离级别

  • Oracle 支持的 2 种事务隔离级别：Read committed, Serializable。 Oracle 默认的事务隔离级别为: Read committed

  • Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为:Repeatable read

3.3 实际操作

#查看当前事务隔离级别
SELECT @@_tx——isoliation;

#设置当前 mySQL 连接的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 【某个隔离级别】;

#设置数据库系统的全局的隔离级别
SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL;

3.4 保存点

#语法
SAVEPOINT 节点名;

演示如何使用savepoint;

SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
#设置保存点
SAVEPOINT a;
DELETE FROM account WHERE id = 28;
#回滚到a保存点
ROLLBACK TO a;

avatar
文章
阅读
粉丝