安全基础 --- MySQL数据库解析

MySQL的ACID

（1）ACID是衡量事务的四个特性

• 原子性（Atomicity，或称不可分割性）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

（2）解析

原子性：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是用原子性来定义的。（实现主要基于 undo log）

持久性：保证事务提交后不会因宕机导致数据丢失。（实现主要基于 redo log）

隔离性：保证事务执行尽可能不受其他事务影响；InooDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于 undo log 的版本链、ReadView）

一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

mysql的锁机制

加锁机制：乐观锁和悲观锁

（1）悲观锁

悲观锁：又称悲观并发控制，指的是数据被外界修改持保持态度，整个事务处理中，将数据进行锁定，直到事务处理完毕，才释放锁。

PS：悲观锁认为并发事物会导致问题，屏蔽一切可能违反数据一致性的操作。

特点：完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁，然后进行数据操作，最后再解锁，加锁释放锁的过程会有消耗，所以性能不高；

（2）乐观锁

乐观锁：假设认为数据一般情况下不会造成冲突，所以在数据提交更新时候，才回正式对数据的冲突与否进行检查。

实现方式（常见）：

1. 版本号：表中新增一个版本号字段，每次数据更新都会修改版本号。更新数据会检查数据库中版本号与之前读取版本号是否一致，一致则允许修改
2. 时间戳：类似于版本号的方式，使用数据库时间戳来实现。更新数据时会检查时间戳是否与之前读取的一致
3. 基于字段判断：使用WHERE字句加上之前读取的字段值进行判断。更新行数为0，则表示数据已被其他事务修改，发生冲突

在提交更新之前检查是否有其他事务已经修改了该记录，如果发现修改，则会回滚更新并重试事务。否则，它会将更新提交到数据库。

兼容性：共享锁和排它锁

（1）排它锁

排它锁：又被称为写锁（简称X锁）。不能与其它事务共存，如一个事务获取了一个数据行的排它锁，其他事务就不能获取该行的其他锁，包括共享锁和排它锁。

实质：排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁

实现：

打开第一个命令行界面

set autocommit=0; ---> 关闭自动提交
select * from users where from id=1 for update; ---> 查询语句的同时增加排它锁

打开第二个命令行界面


进程阻塞，不会再去动这个进程，除非上级事务提交

上级事务已提交

查询结果显示

存在问题：用户的体验和性能。如果用排它锁锁住，所有的读、请求都会堵塞在这里，对系统压力较大。n等多个进程全部堵塞，在等待中，释放后这些进程就会开始竞争，其中一个进程竞争到，其他进程相当于什么都没干，系统性能已消耗。 ---  惊群效应

惊群效应：当你往一群鸽子中间扔一块食物，虽然最终只有一个鸽子抢到食物，但所有鸽子都会被惊动来争夺，没有抢到食物的鸽子只好回去继续睡觉， 等待下一块食物到来。这样，每扔一块食物，都会惊动所有的鸽子，即为惊群。

（2）共享锁

共享锁：又被称为读锁（简称S锁）。多个事务对于同一数据可以共享一把锁，都能访问到数据，但是仅只读不能修改。

共享锁相对更好理解一点，就是多个事务只能读数据不能改数据。

实现：

打开第一个命令界面

select * from users where id=1 lock in share mode; --->  做共享锁

打开第二个命令界面

update users set username='oupeng' where id=1; ---> 更新users表中id=1的username

存在问题：修改数据时，该共享锁认为该线程一定会被一定会被多个线程争抢，读线程不受影响，

PS：共享锁和排它锁都属于悲观锁

mysql事务隔离级别

MySQL 事务都是指在 InnoDB 引擎下

（1）级别分类

mysql有四个事务隔离级别，每个级别都有字符或数字编号

• 读未提交（READ-UNCOMMITTED）| 0 ：存在脏读，不可重复读，幻读的问题
• 读已提交（READ-COMMITTED） | 1：解决脏读的问题，存在不可重复读，幻读的问题
• 可重复读（REPEATABLE-READ） | 2：解决脏读，不可重复读的问题，存在幻读的问题，默认隔离级别，使用MVVC机制，实现可重复读
• 序列化（SERIALIZABLE） | 3：解决脏读，不可重复读，幻读的问题，保证事务安全，但完全串行执行，性能最低

查看会话的事务隔离级别：

select @@global.tx_isolation, @@tx_isolation;

默认的隔离级别为可重复读

（2）解决实际问题

• 脏读（Dirty Read）
  脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
• 可重复读（Repeatable Read）
  可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
• 不可重复读（Non-Repeatable Read）
  对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。
• 幻读（Phantom Read）
  幻读多是针对数据插入（INSERT）操作来说的。事务 A 按照一定条件进行数据读取， 期间事务 B 插入了相同搜索条件的新数据，事务 A 再次按照原先条件进行读取时，发现了事务 B 新插入的数据，称为幻读

脏读演示

<1> 开启A,B两个窗口，进行如下操作：

show variables like 'autocommit';  ----  查看当前的自动提交是否开启
set autocommit = off; --- 关闭自动提交

set session transaction isolation level read uncommitted; --> 设置当前会话隔离级别为“读未提交”
select @@tx_isolation; --> 查看当前隔离级别

start transcation; --- 开启事务，（begin; -- 显式开启事务）

<2> 修改数据进行查询

B：update users set username='123456' where id=1; --- 修改id=1的username为123456
A：select * from users where id=1; --- 在A窗口查询id=1的数据 


事务未关闭，显示数据已被更改，此时出现的问题即为脏读。

不可重复读演示

<1> 关闭A,B的自动提交机制，使用读已提交隔离级别，开启事务

<2> 修改数据进行查询

B：update users set username='oupengaaa' where id=1; --- 修改id=1的username为oupengaaa
A：select * from users where id=1; --- 在A窗口查询id=1的数据


​​​​​​id=1的username未发生改变，当B窗口修改事务后未使用(commit;)提交，所以事务未发生改变。

在B窗口提交后，在A窗口进行查询

存在问题：不可重复读 --- 同一事物内，最开始读到的数据和事务结束前任意时刻读到的数据不一致。

可重复读演示

<1> 关闭A,B的自动提交机制，使用可重复读隔离级别，开启事务

<2> 修改数据进行查询

B：update users set username='oupeng1234' where id=1; --- 修改id=1的username为oupeng1234
A：select * from users where id=1; --- 在A窗口查询id=1的数据


​​​​​​id=1的username未发生改变

B窗口提交后继续查看，依旧未改变

A窗口使用(commit;)提交，查询数据改变情况

存在问题：可重复读可能产生幻读问题，幻读问题很少出现。

可重复读的实现机制：MVCC --- 多版本并发控制，类似于乐观锁的实现机制

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

幻读演示(可重复读环境下)

事务T1：

事务T2：

只要 T1 和 T2 时刻执行产生的结果集是不相同的，那就发生了幻读的问题，比如：

• T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 6 条行记录，那就发生了幻读的问题。

• T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 4 条行记录，也是发生了幻读的问题。

由此，可把幻读理解为：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

（3）mysql 幻读问题

MVCC + gap Lock

幻读是指在一个事务中，由于其他事务的并发操作，导致同一个查询在不同时间点返回不同的结果集。简单来说，幻读就是一个事务在读取数据的过程中，发现了一些“幻影”数据，这些数据在事务开始时不存在，但在事务结束时却突然出现了。

两种解决方案：

<1> 针对快照读（普通select语句）：通过 MVCC 方式解决了幻读。MVCC 是一种在并发事务执行过程中管理数据版本的机制。它通过为每个事务分配唯一的事务 ID 和版本号来跟踪数据的变化。

<2> 针对当前读（select ... for update 等语句）：是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。