MySql 事务和隔离级别

隔离级别	脏读(读未提交)	不可重复读(一次事务中同样的sql读取结果不一致)	幻读(一次事务中同样的sql读取到另一个事务插入的记录	是否加锁
READ-UNCOMMITTED	YES	YES	YES	NO
READ-COMMITTED(Oracle默认)	NO	YES	YES	NO
REPEATABLE-READ(Mysql默认)	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

• 脏读
  有2个事务 事务A和B 事务B读取了A没有提交的数据; READ-COMMITTED 解决了不可重复读, Oracle默认的隔离级别;
• 不可重复读
  一次事务中同样的sql语句读取的结果不一致;
• 幻读
  一个事务读取了另一个事务已经提交并且是 insert的语句;
• 查看事务方法

#查看隔离级别 
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

• 演示
• 初始化数据

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `money` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `atguigudb`.`account` (`id`, `money`, `name`) VALUES (1, 100, '张三');
INSERT INTO `atguigudb`.`account` (`id`, `money`, `name`) VALUES (2, 0, '李四');`

• 演示脏读, READ-UNCOMMITTED

-- 演示脏读
-- 窗口1
SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
-- 窗口2
SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
-- 窗口1
begin;
-- 窗口2
begin;
-- 窗口1
update account set money=50  where id=1;

-- 窗口2查询读取了窗口1 未提交的数据
select * from account;
-- +----+-------+--------+
-- | id | money | name   |
-- +----+-------+--------+
-- |  1 |    50 | 张三   |
-- |  2 |     0 | 李四   |
-- +----+-------+--------+

-- 窗口1 回滚
ROLLBACK;
-- 窗口2 再次读
-- +----+-------+--------+
-- | id | money | name   |
-- +----+-------+--------+
-- |  1 |    100 | 张三   |
-- |  2 |     0 | 李四   |
-- +----+-------+--------+

• 演示去除脏读 READ-COMMITTED

-- 窗口1
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
-- 窗口2
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
-- 窗口1
begin;
-- 窗口2
begin;
-- 窗口1
update account set money=50  where id=1;

-- 窗口2查询解决了脏读, 窗口1未提交的数据没有读出来
select * from account;
-- +----+-------+--------+
-- | id | money | name   |
-- +----+-------+--------+
-- |  1 |    100 | 张三   |
-- |  2 |     0 | 李四   |
-- +----+-------+--------+

-- 窗口1 提交
COMMIT;
-- 窗口2 再次读, 在同一个事务中读取了事务A已经提交的数据 这个就是不可重复读
-- +----+-------+--------+
-- | id | money | name   |
-- +----+-------+--------+
-- |  1 |    50 | 张三   |
-- |  2 |     0 | 李四   |
-- +----+-------+--------+

• 演示解决不可重复读REPEATABLE-READ

-- 窗口1 
mysql> select * from account;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   100 | 张三   |
|  2 |     0 | 李四   |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money=50  where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 窗口2 
mysql> select * from account;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   100 | 张三   |
|  2 |     0 | 李四   |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   100 | 张三   |
|  2 |     0 | 李四   |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+-------+--------+
| id | money | name   |
+----+-------+--------+
|  1 |   100 | 张三   |
|  2 |     0 | 李四   |
+----+-------+--------+

• 总结

脏读我们是无法接受的, mysql默认是REPEATABLE-READ 其实 不可重复读和幻读还是能够接受的 也无所谓
oracle默认就是READ-COMMITTED 所以在数据库优化角度也可以将mysql设置成 READ-COMMITTED;

my.cnf配置

[mysqld]
transaction-isolation = READ-COMMITTED

redo 日志和 undo日志

redo 日志

• 概述

用来保障 数据库的持久性一致性设计的一个临时存储文件

• 执行过程

1. 在添加或更新一条数据的时候, 先从磁盘读取到 InnoDB 共享池中,修改数据 将修改后的值产生一条 rodo 日志, 将日志写入 redo_buffer_pool 共享池 再按照刷盘策略按照一定的策略刷新到 redo 日志中;
2. show variables like '%innodb_log_buffer_size%'; 查看缓冲区默认16M;
3. 当事务执行 commit; 的时候将 redo_buffer 里面的数据保存到磁盘redo log 中;
   

• 刷盘策略

innodb_flush_log_at_trx_commit 值用来控制少策略, 表示事务提交后如何操作
0 表示 每次事务提交是啥也不做, 由mysql的线程每隔1秒从 InnoDB缓存中同步到page_cache 和刷盘;
1 表示每次事务提交都进行 同步和 刷盘 (默认值,最安全, 性能最差);
2 每次事务提交将更新的日志 交给操作系统 的 page_cache 由操作系统进行刷盘操作;

• 第一种情况
  
  每次都在事务提交的时候进行写入page_cache和 刷新到磁盘的操作
• 第二种情况
  

当innodb_flush_log_at_trx_commit=2 时 commit提交事务的时候 只写入 page_cache, 操作系统同步到磁盘, 降低了每次commit需要刷盘的次数 提高了吞吐量, 但是随之也带来了问题就是操作系统挂了后 1秒的数据就丢失了;

• 第三种策略
  

当innodb_flush_log_at_trx_commit=0 时不做任何操作, 也不做从innoDB buffer 同步到 redo buffer 的操作是最危险的, mysql存储引擎会启动一个后台线程 从 innoDB buffer 同步到 redo buffer 和 page cache 和刷盘的操作

• 相关的参数

-- 设置redo log buffer size , 默认 16M
innodb_log_buffer_size
-- 查看 redo log 保存目录 下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件
innodb_log_group_home_dir
-- 设置 redo log 的文件个数默认是2个
innodb_log_files_in_group
-- 设置每个 redo log 文件的大小 默认是48M, redolog 总大小= innodb_log_files_in_group * innodb_log_file_size
innodb_log_file_size
-- 设置刷盘策略 默认为1
innodb_flush_log_at_trx_commit

undo日志

• 概述

undo log 是事务 更新时候的前置操作, 是为了事务回滚 和 多版本并发控制的前置条件;
管理 undo log innodb采用 回滚段（rollback segment）方式管理, 每个回滚段记录了 1024 个 undo log segment, 最大支持回滚段 128个 使用以下参数查看 show variables like ‘innodb_undo_logs’;

• 执行过程

undo log 和redo log 配合的执行过程

• undo log生成过程

在数据添加的时候都会有隐藏的行格式进行添加 row_id 我们不指定主键的时候默认生成, trx_id 事务Id,roll_ptrh=回滚指针记录了undo log的地址

1. 当我们执行insert时
   
   
2. 当我们执行UPDATE时
   
   当我们执行的时候每一行的数据都会加上它上一次的回滚记录地址, 这样在多版本并发的时候就能够找到历史记录

数据库中的锁

锁	范围
操作数据划分	共享锁/读锁,排它锁/写锁
锁粒度划分	表级锁,行级锁,页级锁
态度划分	悲观锁,乐观锁
加锁方式	隐士,显示
其他	全局锁,死锁

• 锁监控方法

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time ：从系统启动到现在锁定总时间长度；（等待总时长）
Innodb_row_lock_time_avg ：每次等待所花平均时间；（等待平均时长）
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；（等待总次数）

-- 查询正在被锁阻塞的sql语句。
SELECT * FROM information_schema.INNODB_TRX\G;
-- 查询锁等待情况
SELECT * FROM data_lock_waits\G;
-- 查询锁的情况
 SELECT * from performance_schema.data_locks\G;
 

MVCC多版本并发控制

• 概述

MVCC （Multiversion Concurrency Control） 多版本并发控制, 主要解决了多个事务之间 隔离级别之间 读取历史快照版本的记录的. 是不加锁的读取快照;

• 快照读

-- 默认就是快照读
select * from tables where ....

• 当前读

-- 排它锁读
select * from tables for update
-- 共享锁读取
SELECT * FROM tables LOCK IN SHARE MODE;

-- 排它锁
INSERT INTO student values ...
-- 排它锁
DELETE FROM student WHERE ...
-- 排它锁
UPDATE student SET ...

• undo log 中的隐藏列

trx_id 事务ID 每个事务修改数据将数据写入 undo log的时候 默认吧事务ID写入;
roll_pointer 历史版本的 下一条 undo log , undo log 构成了一个链表记录了不同的版本记录;

• undo log 生成过程

举例说明undo log 链的过程

• MVCC实现原理之ReadView

概述
MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。
READ UNCOMMITTED 不使用ReadView, 啥问题也没解决;
SERIALIZABLE 使用加锁的方式不需要 ReadView;
只有在 READ COMMITTED 和 REPEATABLE READ 才能使用到ReadView

• ReadView

ReadView 包含了以下几个重要的字段
creator_trx_id 创建这个 ReadView 的事务ID;
trx_ids 当前系统活跃的事务ID列表;
up_limit_id 活跃事务ID 最小值;
low_limit_id 当前系统中最大事务ID;

• 举例说明

在 READ COMMITTED ：每次读取数据前都生成一个ReadView。

-- 现在有两个 事务id 分别为 10 、 20 的事务在执行：
# Transaction 10 
BEGIN; 
UPDATE student SET name="李四" WHERE id=1; 
UPDATE student SET name="王五" WHERE id=1; 

# Transaction 20 
BEGIN; 
# 更新了一些别的表的记录 ...

-- 假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：
# 使用READ COMMITTED隔离级别的事务 
BEGIN; # 
-- SELECT1：Transaction 10、20未提交 
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

-- 之后，我们把 事务id 为 10 的事务提交一下：
# Transaction 10 
COMMIT;

-- 然后再到 事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录
# Transaction 20 
BEGIN; 
# 更新了一些别的表的记录 ... 
UPDATE student SET name="钱七" WHERE id=1; 
UPDATE student SET name="宋八" WHERE id=1;

此刻，表student中 id 为 1 的记录的版本链就长这样：

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务 
BEGIN; 
# SELECT1：Transaction 10、20均未提交 
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三' 
# SELECT2：Transaction 10提交，Transaction 20未提交 
SELECT * FROM student WHERE id = 1; 
# 得到的列name的值为'王五'

• ReadView的规则

有了这个ReadView，步骤判断记录的某个版本是否可见。
如果被访问版本的trx_id属性值与ReadView中的creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在trx_ids 列表中。
如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

• READ COMMITTED隔离级别下

READ COMMITTED ：每次读取数据前都生成一个ReadView。 所以自然就解决了 读取已经提交的数据;
顺便吧幻读也解决了一下