SQL语句

目录

TCL事务控制语言

简概

• 事务
  • 事务（transaction），是一个完整的业务逻辑单元，不可再分
• 应用场景
  • 举例：A向B转账10000，则需要执行两条updata语句。这两条语句必须同时成功或同时失败，不允许一条成功一条失败
• TCL与DML
  • 和事务相关的语句只有DML语句
  • 因为这三个语句都和数据相关，而事务的存在是为了保证数据的完整性、安全性
  • 假设所有的业务都能使用1条DML语句搞定，则不需要事务机制
• 事务的原理
  • (1) 开启事务机制
  • (2) 执行语句（语句执行成功后悔把执行记录到操作历史中，并不会真正修改硬盘上的数据）
  • (3) 重复多次步骤2
  • (4) 提交事务或者回滚事务（这步才会真正修改硬盘上的数据）
• 事务的四大特性：ACID
  • A：原子性：（Atomicity）事务是最小的工作单元，不可再分
  • C：一致性：（Consistency)事务必须保证多条DML语句同时成功或同时失败
  • I：隔离性（Isolation）事务A与事务B之间具有隔离
  • D：持久性（Durability）持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束

事务使用

MySQL事务默认情况下是自动提交：只要执行任意一条DML语句则提交一次

关闭自动提交：start transaction;

演示 - 准备表

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,
    username varchar(255)
);

演示 - 没关闭自动提交的情况

insert into t_user(username) values('zs'); 	-- 插入（自动COMMIT了，无法回滚）
select * from t_user;						-- 数据更新了
rollback;									-- 回滚（此处无法回滚，因为前面提交了）
select * from t_user;						-- 数据不变

演示 - 关闭自动提交的情况

start transaction;							-- 开启事务并关闭自动提交
insert into t_user(username) values('ls');	-- 插入（没有COMMIT，可以回滚）
select * from t_user;						-- 数据更新了
-- commit;									-- 此时可以手动提交，然后后面就无法回滚了
rollback;									-- 回滚
select * from t_user;						-- 数据变回原来的情况

COMMIT（提交）

略，见事务使用

ROLLBACK（回滚）

略，见事务使用

SAVEPOINT（保存点）

作用：回滚时会回滚到保存点

隔离级别与演示

四个隔离级别

隔离性补充——四个隔离级别（隔离性存在隔离级别，理论上隔离级别包含4个）

• 第一级别：读未提交（read uncommited）
  • 对方事务还没提交，我们当前事务可以读取到对方未提交的数据（缓存中、还未持久化的数据）
  • 存在的问题：脏读（dirty read）现象（表示读到了脏的数据、不稳定数据）
• 第二级别：读已提交（read committed）
  • 对方事务提交之后的数据我方可以读取到
  • 解决了问题：脏读现象
  • 存在的问题：不可重复读（这个名字有点歧义，其实是表示读不到以前的数据）
• 第三级别：可重复读（repeatable read）
  • 解决了问题：不可重复读
  • 存在的问题：读取到的数据是假
• 第四级别：序列化读/串行化读（serializable）
  • 解决了问题：所有问题
  • 存在的问题：效率低，需要事务排队
• 其他补充
  • Oracle数据库默认的隔离级别：读已提交
  • MySQL数据库默认的隔离级别：可重复读

演示、实验

读未提交（read uncommited）

准备

• 打开两个窗口分别登陆上数据库
• set global transaction isolation level read uncommited，设置全局事务隔离级别为读未提交
• select @@global.tx_isolation;，查看事务隔离级别（不行就把tx_isolation换成transaction_isolation）
• 两个窗口都start transaction;，均设置为自动提交事务

演示

• A：select * from t_user;，读到7条数据
• B：insert into t_user(name) values('SMITH');，插入一条数据（未提交）
• A：select * from t_user;，读到8条数据

读已提交（read committed）

准备

• 打开两个窗口分别登陆上数据库
• set global transaction isolation level read commited，设置全局事务隔离级别为读已提交
• select @@global.tx_isolation;，查看事务隔离级别（不行就把tx_isolation换成transaction_isolation）
• 两个窗口都start transaction;，均设置为自动提交事务

演示

• A：select * from t_user;，读到7条数据
• B：insert into t_user(name) values('SMITH');，插入一条数据（未提交）
• A：select * from t_user;，读到7条数据
• B：commit;，提交
• A：select * from t_user;，读到8条数据

可重复读（repeatable read）

准备

• 打开两个窗口分别登陆上数据库
• set global transaction isolation level repeatable read，设置全局事务隔离级别为可重复读
• select @@global.tx_isolation;，查看事务隔离级别（不行就把tx_isolation换成transaction_isolation）
• 两个窗口都start transaction;，均设置为自动提交事务

演示

• A：select * from t_user;，读到7条数据
• B：insert into t_user(name) values('SMITH');，插入一条数据（未提交）
• B：commit;，提交
• A：select * from t_user;，读到7条数据

串行化读（serializable）

准备

• 打开两个窗口分别登陆上数据库
• set global transaction isolation level serializable，设置全局事务隔离级别为串行化读
• select @@global.tx_isolation;，查看事务隔离级别（不行就把tx_isolation换成transaction_isolation）
• 两个窗口都start transaction;，均设置为自动提交事务

演示

• A：select * from t_user;，读到7条数据
• B：insert into t_user(name) values('SMITH');，插入一条数据（未提交）
• A：select * from t_user;，光标在此闪烁停顿。当B进行COMMIT后，这里才会显示，显示的是8条数据