数据库操作-TCL

TCL

TCL（Transaction Control Language），事务控制语言。

主要用途：对数据库进行事务控制的指令。

操纵命令：COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION

COMMIT：提交事务。

ROLLBACK：回滚操作。

SAVEPOINT：设置保存点。

SET TRANSACTION：改变事务选项。

1、什么是事务？

事务是数据库操作的逻辑单元，包含一系列数据操作，是一个不可分割的整体。

事务的特性——ACID：

原子性(Atomicity)，其包含的所有数据库操作指令要么都执行，如有执行失败会回滚为全都不执行。

一致性(Consistency)，执行前后的数据库有效性是一致的。

隔离性(lsolation)，一个事务不受其他事务的干扰，在多用户并发访问时相互不受干扰。

持久性(Durability)，一旦提交了事务，数据的改变永久保存本地。

2、事务的提交：COMMIT

事务的执行内容只有使用commit进行提交后，其中的修改操作才可以成为永久性操作。

先转换MYSQL结束符：
DELIMITER //

事务结构：
START TRANSACTION｜BEGIN:
查询主体;
COMMIT;
//


案例：
对user表进行如下事务操作：
1. 姓名为Olivia的人员年龄修改为40
2. 插入新记录user_id为018的男性在职人员Ian，年龄23，电话13173629832，部门为CS
3. 删除原表中user_id为017的记录
4. 查询上述修改结果

start transaction;
update user set age = '40' where user_name = 'Olivia';
insert user values('018','Ian','23','0','13173629832','0','CS');
delete from user where user_id = '017';
select * from user where user_dept = 'CS' or user_name  = 'Olivia';
commit;
//

我们先查询事务执行前的相关表内容：

执行事务：

3、事务的自动提交：AUTOCOMMIT

autocommit参数记录了数据库是否为自动提交模式，默认状态为on：

案例：

对user表中user_id为018的人员修改姓名为Peter，年龄为50。

我们开启两个终端，并仅将终端一的autocommit参数修改为OFF：

set autocommit = 0//

在终端一进行姓名的修改操作，不执行commit。

我们可以在终端一中查询到修改后的记录：

而在终端二中，则姓名字段为修改前的记录：

若此时在终端二对user表进行年龄的修改操作，会因为正在进行的事务而报错：

将终端一中的事务提交后，我们完成终端二中的修改并查询结果：

4、事务的回滚：ROLLBACK

事务在没有使用commit提交时，使用rollback可以将数据库恢复到事务提交前的状态。

案例：

对user表user_id为016的人员remove信息进行修改，并进行回滚操作。

修改前我们查询该条记录如下：

执行事务后并回滚，可以看到数据修改成功后又回滚到修改前状态：

5、设置保存点：SAVEPOINT

在事务中设置savepoint，可以将事务回滚到savepoint前。

设置保存点：
SAVEPOINT savepoint_name;

回滚到保存点：
ROLLBACK TO SAVEPOINT savepoint_name;

解除保存点：
RELEASE SAVEPOINT savepoint_name;

案例：
start transaction;
update user set remove = 1 where user_id = '016';
savepoint s1;
delete from user where user_id = '016';
rollback to savepoint s1;
release savepoint s1;
//

6、事物隔离级别：SET TRANSACTION

事务的隔离级别可用于防止事务的并发性问题。

事务有如下隔离级别：

​ read uncommitted 读未提交，该模式会出现脏读

​ read committed 读已提交，该模式不支持重复读

​ repeatable read 可重复读，也是MYSQL中默认隔离级别

​ serializable 串行化，不允许读写并发操作

6.1、查看当前的事务隔离级别：

SHOW VARIABLES LIKE 'TRANSACTION ISOLATION';

6.2、修改事务隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL READ 新的事务隔离级别;

global：设置全局默认隔离级别

session：设置本次会话隔离级别

如果二者均不写：仅设置本次事务命令的隔离级别，下一个事务将回到默认隔离级别

案例：将当前会话的隔离级别修改为read committed。

set session transaction isolation level read committed;

当前会话隔离级别已更改为read committed：

新开终端再次查询隔离级别，其又回到了默认值repeatable read：