4.事务隔离与锁机制

1. 课程回顾

1.1 事务的日志流程

一条绳子上的蚂蚱

1）创建阶段：事务创建一条日志；
2）日志刷盘：日志写入到磁盘上的日志文件； （ib_logfile里面）
3）数据刷盘：日志对应的脏页数据写入到磁盘上的数据文件；
4）写CKP：日志被当作Checkpoint写入日志文件；（ib_data里面）

1.2 innodb_flush_log_at_trx_commit

-- 查看日志文件设置状态
show variables like 'innodb_%';
-- 更改
set @@global.innodb_flush_log_at_trx_commit = 0; -- 0，1，2
show variables like 'innodb_flush_log_at_trx_commit';

性能： 0 》2 》1 安全： 1 》2 》0

1.3 锁基础

1.3.1 概述

1. MyISAM 和 Memory 存储引擎使用的是表级锁,BDB 引擎使用的是页级锁，也支持表级锁。由于 BDB 引擎基本已经成为历史，因此就不再介绍了。
2. InnoDB 存储引擎既支持行级锁，也支持表级锁，默认情况下使用行级锁。
3. 所谓表级锁，它直接锁住的是一个表，开销小，加锁快，不会出现死锁的情况，锁定粒度大，发生锁冲突的概率更高，并发度最低。
4. 所谓行级锁，它直接锁住的是一条记录，开销大，加锁慢，发生锁冲突的概率较低，并发度很高。
5. 所谓页级锁，它是锁住的一个页面，它的开销介于表级锁和行级锁中间，也可能会出现死锁，锁定粒度也介于表级锁和行级锁中间，并发度也介于表级锁和行级锁中间。
6. 行级锁更适合大量按照索引条件并发更新少量不同的数据，同时还有并发查询的应用

看你的mysql现在已提供什么存储引擎:
mysql> show engines;

看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;

1.3.2 innoDB行级锁

1. 行级锁本身与表级锁的实现差别就很大，而事务的引入也带来了很多新问题，尤其是事务的隔离性，与锁机制息息相关。
2. 对于事务的基本操作，对于不同隔离级别可能引发的问题，像脏读、不可重复读等问题。
3. 数据库实现事务隔离的方式，基本可以分为两种：
   (1)在操纵数据之前，先对其加锁，防止其他事务对数据进行修改。这就需要各个事务串行操作才可以实现。
   (2)不加任何锁，通过生成一系列特定请求时间点的一致性数据快照，并通过这个快照来提供一致性读取。
4. 上面的第二种方式就是数据多版本并发控制，也就是多版本数据库，一般简称为 MVCC 或者 MCC，它是 Multi Version Concurrency Control 的简写。
5. 数据库的事务隔离越严格，并发的副作用就越小，当然付出的代价也就越大，因为事务隔离机制实质上是使得事务在一定程度上”串行化”,这与并行是矛盾的。

InnoDB有两种类型的行级锁，两种内部使用的意向锁；

• 共享锁(S):允许一个事务读一行数据时，阻止其他的事务读取相同数据的排他锁。
• 排他锁(X):允许获得排他锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排他锁。
• 意向共享锁(IS):事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁(IX):事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。
• 悲观锁(抽象，不真实存在的锁)
• 乐观锁(抽象，不真实存在的锁)

4种锁的共存逻辑关系表

锁模式	共享锁(S)	排他锁(X)	意向共享锁(IS)	意向排他锁(IX)
共享锁(S)	兼容	冲突	兼容	冲突
排他锁(X)	冲突	冲突	冲突	冲突
意向共享锁(IS)	兼容	冲突	兼容	兼容
意向排他锁(IX)	冲突	冲突	兼容	兼容

共享锁语句

select * from table_name lock in share mode;

排他锁语句

select * from table_name for update;

1.2.3 共享锁-悲观锁实例

可以通过执行 select @@innodb_lock_wait_timeout查看

-- 窗体1代码
-- START TRANSACTION;
-- SELECT * FROM count WHERE prefix = 'd1' LOCK IN SHARE MODE;--
-- UPDATE count SET count = 4 where prefix = 'd1';
-- COMMIT;

-- 窗体2代码
-- SELECT * FROM count WHERE prefix = 'd1';
-- SELECT * FROM count WHERE prefix = 'd1' LOCK IN SHARE MODE;
--
-- -- SELECT * FROM count WHERE prefix = 'd1' FOR UPDATE;
-- UPDATE count SET count = 3 where prefix = 'd1';
-- COMMIT;

2. 事务的隔离级别

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

在并发下事务会容易出现一些问题：（先不急着演示这些情况）

• 数据更新丢失：两个事务同时操作一条数据，一个事务因为异常导致数据更新丢失
• 脏读 ：一个失误开始读取了某行数据，另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。
• 不可重复读：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。例如，在两次读取的中途，有另外一个事务对该型数据进行了修改，并提交。
• 幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据（SQL不一定一样）。这是因为在两次查询过程中有另外一个事务插入数据

在MySQL中存在（InnoDB）事务存在着4中隔离级别，不同的隔离级别对事务的处理不同。

• 未授权读取（未提交读 Read Uncommitted）：READ-UNCOMMITTED | 0：存在脏读，不可重复读，幻读的问题。如果一个事务已经开始写数据，则另外一个数据则不会允许同时进行写操作，但允许其他事务读此行数据。隔离级别可以通过“排他写锁”实现。
• 授权读取（已读提交 Read committed）：READ-COMMITTED | 1：解决脏读的问题，存在不可重复读，幻读的问题。这个可以通过“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
• 可重复读取（Repeatable Read）：REPEATABLE-READ | 2：解决脏读，不可重复读的问题，存在幻读的问题，默认隔离级别。可通过“共享锁”，“排他锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。
• 序列化（Serializable）：SERIALIZABLE | 3：解决脏读，不可重复读，幻读，可保证事务安全，但完全串行执行，性能最低。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须要通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

事务隔离级别

隔离级别	读数据一致性	脏读	不可重复读的问题	幻读
未提交读 Read Uncommitted	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已读提交 Read committed	语句级	否	是	是
可重复读取 Repeatable Read	事务隔离级别	否	否	是
序列化 Serializable	最高级别，事务级	否	否	否

可以通过 show variables like '%iso%';查看当前的隔离级别

SELECT @@global.tx_isolation, @@tx_isolation; --查看
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
-- 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
-- SET [GLOABL] config_name = 'foobar';
-- SET @@[session|global].config_name = 'foobar';
-- SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';
SET @@gloabl.tx_isolation = 'READ-COMMITTED';
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

2.1 事务异常现象展示与解决

2.1.1 级联回滚

隔离级别：任意

在程序运行过程中实际上，我们的事务往往是会并发运行的，可能在某一段时间内运行几个事务；而其中会有一个很现象是很难解决的但是可以减少该现象的影响，就是级联回滚；

级联回滚：就是在一段时间同时有两个及以上的事务在运行，由于最先运行的事务出现了 “异常” 导致事务不得不回滚的情况

演示

session1	session2	session3
begin;	begin;	begin;
select * from count where prefix = 'dz10021'	|
update count set count = 1 where prefix = 'dz10021';	|
| select * from count where prefix = 'dz10021'
| update count set count = 2 where prefix = 'dz10021';	select * from count where prefix = 'dz10021'
|	update count set count = 3 where prefix = 'dz10021';
这里添加一条错误的信息	|
insert into count values('dz10021', 0, 2);	|
这里因为异常回滚	因为session1异常回滚	因为session1异常回滚
[Err] 1062 - Duplicate entry 'dz10021' for key 'PRIMARY'	[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction	[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

解释：如上的情况就是因为与session1发生了异常，但是因为session2与session3依赖于session1实现某一些业务，为了保证数据的一致性的话，是会对于相互依赖操作的数据进行级联的回滚这是很有必要的，但是对于程序来说体验不好，事情快要做好了，突然因为别人的一个岔子打断，会很不舒服。

解决：可以在select * from count where prefix = 'dz10021' 加上 for update

2.1.2 幻读

隔离级别：RR

session1	session2
start transaction	start transaction
select * from count where prefix = 'dz1'
没有发现这条数据准备新增	select * from count where prefix = 'dz1'
| 这也没发现这条数据新增
这里因为其他事情耽搁了	insert into count values('dz1', 1, 1)
| commit
处理之后新增
insert into count values('dz1', 0, 1)
出现主键冲突，如果没有设置主键就是数据重复提交

如下是数据重复提交的问题

PHP代码操作： 

php - 1
<?php
// 幻读
$db->execute('START TRANSACTION');
var_dump('T1-- 第一次 查询数据 === 》》 ');
$data = $db->query("SELECT * FROM count WHERE prefix = 'dd'");
var_dump($data);
// 故意延迟5秒更新
sleep(5);
if (empty($data)) {
    var_dump('T1-- 五秒后新增 === 》》 ');
    var_dump($db->execute("insert into count values('dd', 881,31)"));
    var_dump('T1-- 查询数据 === 》》');
    var_dump($db->query("SELECT * FROM count WHERE prefix = 'dd' "));
}
$db->execute('COMMIT');
?>
php - 2
<?php
// 幻读
$db->execute('START TRANSACTION');
var_dump('T2---新增数据 start === 》》 ');
var_dump($db->execute("insert into count values('dd', 881,31)"));
var_dump('T2---新增数据 end === 》》 ');
var_dump($db->query("SELECT * FROM count WHERE prefix = 'dd' "));
$db->execute('COMMIT');
?>

T1事务：查询数据库中是否存在prefix(主键)为dd的数据，如果没有就新增一条

T2事务：干扰的情况就是直接添加数据吗。

在程序的执行过程中，然后T1执行完了判断操作进行新增数据，因为t2在t1延时的时候执行了新增操作；这个时候t1再执行新增的时候失败了，然后再去读数据发现并没有这条数据，这个时候t1就出现了所谓的 ‘幻读’

解决：

1. select * from count where prefix = 'dz1' for update;
2. SET @@gloabl.tx_isolation = 'SERIALIZABLE';

其他效果自行测试：

3. InnoDB行级锁，表级锁的实现方法 && 悲观锁与乐观锁

4. 死锁

https://www.cnblogs.com/zejin2008/p/5262751.html

--