MySQL学习笔记之事务控制语言TCL、事务的创建、修改、删除、MYSQL的隔离级别

SHOW ENGINES; #查看当前的存储引擎

一、事务控制语言TCL的含义与语法

• TCL语言：指的是事务控制语言，Transaction Control LANGUAGE

• 事务：

  • 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行成功，要么全部执行失败。

• 存储引擎：

  • 在mysql中的数据用各种不同的技术存储在文件或内存中。innodb支持事务机制，myisam、memory不支持事务机制

• 事务的特性ACID：

  • A：(Atomicity) 原子性，一个事务不可以再分割，要么都执行，要么都不执行
  • C：(Consistency) 一致性，一个事务的执行会使数据从一个一致状态转换到另一个一致状态
  • I：(Isolation) 隔离性，一个事务的执行不会受其他事务的干扰
  • D：(Durability) 持久性，一个事务一旦提交，则会永久的改变数据库的数据

• 事务的分类：

  • 隐式事务：默认的语句是开启了隐式事务，自动提交
  • 显式事务：事务具有明显的开启和结束的标记，如果要使用显式事务，需要做如下工作

• 开启显式事务的前提：必须先设置好自动提交功能为禁用

查看自动提交功能：show variable like 'autocommit';
关闭自动提交功能：set autocommit=0; -- 1表示开启了自动提交也就还是隐式事务

• 使用显式事务的步骤：

  • 步骤1：开启事务

  set autocommit=0;	
  start transaction;可选的，因为上一句执行后就默认开启了事务
  

  • 步骤2：编写事务中的sql语句（select insert update delete）

  语句1;
  语句2;
  语句3;
  

  • 步骤3：结束事务

  commit;   提交事务，提交就表示上述执行成功，写入数据库，持久化
  rollback; 回滚事务，回滚表示回到该事务执行最初的数据库状态
  

二、事务的隔离级别，也就是所谓的事务之间如何不相互干扰

• MySQL中事务的隔离级别：有4种

  • read uncommitted、read committed、repeatable read、serializable

• 各种级别的功能

  隔离级别	脏读	不可重复读	幻读
  read uncommitted	×(不可避免)	×(不可避免)	×(不可避免)
  read committed	√(可避免)	×(不可避免)	×(不可避免)
  repeatable read	√(可避免)	√(可避免)	×(不可避免)
  serializable	√(可避免)	√(可避免)	√(可避免)

• 脏读：

  A事务读取数据时，正好B事务更新了该数据，并没有提交，之后B事务回滚，导致A事务读取的数据是错误的数据

• 不可重复读：

  A事务第一次读取某数据，假设其值为m，然后B事务修改了该数据为n并且提交，然后A事务再次读取该数据时发现数据为n，这与之前读取的数据m不相同

• 幻读：

  A事务读取某表格发现之后m行，于此同时B事务插入了几行新的数据，当A事务再次操作B数据时却已经不是m行数据，这个现象称为幻读

• 注意：

  MySQL默认的隔离级别为 repeatable read
  Oracle默认的隔离级别为 read commited

相关的代码指令

• 查看隔离级别：

select @@tx_isolation;

• 设置隔离级别

set session|global transaction isolation level 隔离级别;

• savepoint 节点名;

  设置保存点，可以用Rollback TO回滚到当前点，如下案例1

• delete和truncate在事务方面删除中的作用区别：

  1、delete 删除支持回滚
  2、truncate 删除不支持回滚 如下案例2

案例1

-- 先创建一个表
CREATE TABLE account(
	id INT UNIQUE AUTO_INCREMENT,
	NAME VARCHAR(10)
);

INSERT INTO account VALUES(1,'hhsh'),
(NULL,'ssh'),
(NULL,'shhhs');

SELECT * FROM account;
-- 案例尝试事务
SET autocommit=0;
START TRANSACTION;

DELETE FROM account WHERE id = 1;
SAVEPOINT a;
DELETE FROM account WHERE id = 2;
ROLLBACK TO a; 	#此时删除id=2的记录就不会生效
COMMIT;

SELECT @@tx_isolation;

案例2

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account; --truncate 不支持回滚 所以回滚无效的
ROLLBACK;