mysql 事务与锁

一、事务四大特征(ACID)

• 原子性(A)：事务是最小单位，不可再分
• 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
• 隔离性(I)：事务A和事务B之间具有隔离性
• 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

在事务进行过程中，未结束之前，DML 语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事务结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据

通过 start transaction 启动事务，rollback 回滚事务，commit提交事务

查询当前正在执行中的事务

SELECT * FROM information_schema.innodb_trx;

实际上是对数据进行了增删改查等操作后才开启了一个事务。

查看进程：

SHOW PROCESSLIST;
SELECT * FROM information_schema.processlist;

二、数据库并发事务中存在的问题

如果不考虑事务的隔离性，会发生以下几种问题：

• 脏读：指一个事务读取了另外一个事务未提交的数据。

• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）。

• 幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

幻读和不可重复读的区别是，前者是一个范围，后者是本身

不可重复读的重点是修改：同样的条件, 你读取过的数据, 再次读取出来发现值不一样
幻读的重点在于新增或者删除：同样的条件, 第1次和第2次读出来的记录数不一样

从控制角度来看，不可重复读只需要锁住满足条件的记录，幻读要锁住满足条件及其相近的记录

三、事务四大特性之隔离性(isolation)

事务A和事务B之间具有一定的隔离性，隔离性有隔离级别(4个)

• 读未提交：read uncommitted
• 读已提交：read committed
• 可重复读：repeatable read
• 串行化：serializable

1、读未提交 (read uncommitted)

• 事务A和事务B，事务A未提交的数据，事务B可以读取到
• 这里读取到的数据叫做“脏数据”
• 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

2、读已提交 (read committed)

• 事务A和事务B，事务A提交的数据，事务B才能读取到
• 这种隔离级别高于读未提交
• 换句话说，对方事务提交之后的数据，我当前事务才能读取到
• 这种级别可以避免“脏数据”
• 这种隔离级别会导致“不可重复读取”
• Oracle默认隔离级别

3、可重复读 (repeatable read)

• 事务A和事务B，事务A提交之后的数据，事务B读取不到
• 事务B是可重复读取数据
• 这种隔离级别高于读已提交
• 换句话说，对方提交之后的数据，我还是读取不到
• 这种隔离级别可以避免“不可重复读取”，达到可重复读取
• MySQL默认级别
• 虽然可以达到可重复读取，但是会导致“幻像读”

B事务开启，A事务修改数据后，A事务提交，B事务再次读取数据，读取的依然是 A 事务修改之前的数据

4、串行化 (serializable)

• 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
• 这种隔离级别很少使用，吞吐量太低，用户体验差
• 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

四、查看隔离级别

mysql8 查看事务隔离级别

SELECT @@global.transaction_isolation, @@transaction_isolation;

五、快照读和实时读

首先我们的 SELECT 查询分为快照读和实时读，快照读通过 MVCC（并发多版本控制）来解决幻读问题，实时读通过表锁和行锁来解决幻读问题

MySQL 普通的查询是快照读，是不会看到别的事务插入的数据的。因此， 幻读在“实时读（当前读）”下才会出现

1、快照读

当执行 select 操作是 innodb 默认会执行快照读，会记录下这次select后的结果，之后 select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。

快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

2、实时读

使用以下这两种查询语句进行查询时就是实时读，
此时会锁过滤条件符合的行，不允许其他线程修改

# 在查询时会先申请X锁，即排它锁
SELECT * FROM table_name FOR UPDATE;
# 在查询时会先申请S锁，即共享锁
SELECT * FROM table_name LOCK IN SHARE MODE;

六、锁

1、锁类别

• 共享锁：又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能改

• 排它锁：又称为写锁，简称X锁，顾名思义，排它锁就是不能与其它锁并存，如一个事务获取了一个数据行的排它锁，其它事务就不能再获得该行的其它锁，包括共享锁和排它锁，但是获取此数据行排它锁的事务可以对数据进行读取和修改

2、查看锁

mysql8 查看锁信息：

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits ;

mysql8 之前查看锁信息：

 SELECT * FROM information_schema.innodb_locks;
 SELECT * FROM information_schema.innodb_lock_waits;

查看锁超时时间：

SHOW variables LIKE '%innodb_lock_wait_timeout%';

3、行锁与表锁

对于排它锁和共享锁，若条件是索引列，则给表某些行加锁；
若是条件列不是索引列，则加表级锁

SELECT xxx FROM xxx WHERE xxx FOR UPDATE;
SELECT xxx FROM xxx WHERE xxx LOCK IN SHARE MODE;

4、间隙锁

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;

对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id > 25 FOR UPDATE;

例如

表结构：

CREATE TABLE `t` (
  `id` int,
  `unique_id` int,
  `index_id` int,
  `value_id` int,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`unique_id`),
  KEY `index_name` (`index_id`)
);

有数据：

+----+-----------+----------+----------+
| id | unique_id | index_id | value_id |
+----+-----------+----------+----------+
| 10 |        10 |       10 |       10 |
| 20 |        20 |       20 |       21 |
| 30 |        30 |       30 |       32 |
| 40 |        40 |       40 |       40 |
+----+-----------+----------+----------+

对于 WHERE id > 25 FOR UPDATE，会把 (20, +infinity) 锁住，注意是前开后开区间