数据库操作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;
//

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

image-20200614215919792

执行事务:

image-20200614215953216

3、事务的自动提交:AUTOCOMMIT

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

image-20200614222114098

案例:

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

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

set autocommit = 0//

image-20200614222243940

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

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

image-20200614222411977

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

image-20200614222508525

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

image-20200614222723258

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

image-20200614222851509

4、事务的回滚:ROLLBACK

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

案例:

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

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

image-20200614224038318

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

image-20200614224156208

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;
//
image-20200615000422781

6、事物隔离级别:SET TRANSACTION

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

事务有如下隔离级别:

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

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

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

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

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

SHOW VARIABLES LIKE 'TRANSACTION ISOLATION';

image-20200616114343560

6.2、修改事务隔离级别:

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

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

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

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

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

set session transaction isolation level read committed;

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

image-20200616120501297

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

image-20200616121039519

评论