MySQL事务的四大隔离级别

一、并发控制

1.1 锁机制

索可以分为如下类型：

• 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
• 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：

• 表级锁：MyISAM
• 行级锁：InnoDB

实现

• 存储引擎：自行实现其锁策略和锁粒度
• 服务器级：实现了锁，表级锁，用户可显式请求

分类：

• 隐式锁：由存储引擎自动施加锁
• 显式锁：用户手动请求
• 锁策略：在锁粒度及数据安全性寻求的平衡机制

显示使用锁语法：

# 加锁
LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias] lock_type] ...


lock_type: 
READ   #读锁
WRITE  #写锁


# 解锁
UNLOCK TABLES


关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK] 

查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

二、事务的概念

事务，由一个有限的数据库操作序列构成（sql语句），这些操作（sql语句）要么全部执行,要么全部不执行，是一个不可分割的工作单位。

假如userA转账给userB 1000 元，先从userA的账户里扣除1000 元，再在user B 的账户上加上 1000 元。
如果扣完userA的1000元后，还没来得及给userB加上，银行系统异常了，最后导致userA的余额减少了，
userB的余额却没有增加。所以就需要事务，将userA的钱回滚回去。

2.1 事务的作用

保证了并发情况下操作数据的安全性

2.2 事务的四大特性

• 原子性(atomicity)： 一个事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行，是一个不可分割的单位。
• 一致性(consistency)： 事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态，一致性跟原子性是密切相关的。在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转100块钱，不管成功与否，A和B账户的总金额是不变的。
• 隔离性(Isolation)： 一个事务所做出的操作在提交之前，是不能为其它事务所见；多个事务并发访问时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，多个并发事务之间要相互隔离。
• 持久性(durability)： 表示事务完成提交后，该事务对数据库所作的操作更改，将持久地保存在数据库之中，接下来的其他操作或者故障不应该对其有任何的影响。

事务并发存在诸多问题，一个事务是怎么干扰到其他事务的呢？

创建test_01数据库，创建account表：

SHOW DATABASES;
CREATE DATABASE test_01 charset 'utf8';
USE test_01;
CREATE TABLE account ( id INT NOT NULL PRIMARY KEY, 
NAME VARCHAR ( 20 ),
balance INT );

插入数据：

INSERT INTO account
VALUES
	(1,"甄姬", 1000 ),
	(2,"曹操",1000 ),
	(3,"狄仁杰",1000 );

id	name	balance
1	甄姬	1000
2	曹操	1000
3	狄仁杰	1000

• 脏读（dirty read） ：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样
  假设现在有两个事务A、B：
  假设现在A的余额是1000，事务A正在准备查询甄姬的余额这时候，事务B先扣减了甄姬的余额，扣了100
  最后A 读到的是扣减后的余额900

时间序列	事务A	事务B
1	begain
2		begain
3		updata account set balance=balance-100 where name=“甄姬”
4	select balance from account where name=“甄姬”
5	事务A读到的结果为1000-100=900

  由上可知，事务A、B交替执行时，事务A读到了事务B还没提交的数据，说明事务A被事务B干扰了，这就是脏读

• 不可重复读（unrepeatable read）：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
  假设现在有两个事务A和B：
  事务A先查询甄姬的余额，查到结果是1000
  这时候事务B 对甄姬的账户余额进行扣减，扣去100后，提交事务
  事务A再去查询甄姬的账户余额发现变成了900

时间序列	事务A	事务B
1	begain
2	select balance from account where name=“甄姬”
3	读到的数据为1000
4		begain
5		updata account set balance=balance-100 where name=“甄姬”
6		commit
7	select balance from account where name=“甄姬”
8	读到的结果为900

    在事务A范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。事务A又被事务B干扰了。

• 幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样。
  假设现在有两个事务A、B：
  事务A先查询id大于等于2的账户记录，得到记录id=2和id=3的两条记录
  这时候，事务B开启，插入一条id=4的记录，并且提交了
  事务A再去执行相同的查询，却得到了id=2,3,4的3条记录了。

时间序列	事务A	事务B
1	begain
2	select * from account where id>=2
3	返回2，3两条记录
4		begain
5		insert into account value (4，“孙尚香”，1000)
6		commit
7	select * from account where id>=2
8	返回2，3，4三条记录

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

2.3 事务的四种隔离级别

并发事务存在脏读、不可重复、幻读等问题，所以InnoDB引擎通过设置隔离级别来解决并发问题。

隔离级别	描述	脏读	不可重复读	幻读	加读锁
READ UNCOMMITTED	读未提交	√	√	√	否
READ COMMITTED	读已提交	x	√	√	否
REPEATABLE READ	可重复读	x	x	√	否
SERIALIZABLE	串行化	x	x	x	是

重点：

• mysql 中默认 可重复读（ REPEATABLE READ）

• oracle中默认读已提交 （READ COMMITTED）

• 查看隔离级别:
  SELECT @@tx_isolation 或者 SHOW GLOBAL VARIABLES LIKE '%isolation%'

• 设置隔离级别:
  SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

• 显示启动事务

  方式1. BEGIN
  方式2.BEGIN WORK
  方式3.START TRANSACTION
  

• 结束事务

  #提交,相当于vi中的wq保存退出
  COMMIT
  #回滚,相当于vi中的q!不保存退出
  ROLLBACK
  

只有事务型存储引擎中的DML语句方能支持回滚
oracle 默认是手动提交 ,mysql默认自动提交

set autocommit=1|0 #0代表手动提交 1代表自动提交



mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

建议：显式请求和提交事务，而不要使用"自动提交"功能

死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

2.4 MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定，默认为REPEATABLEREAD，可在GLOBAL和SESSION级进行设置

#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

或在配置文件中指定

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE