MySQL之锁和事务

最新推荐文章于 2022-12-16 13:55:35 发布

Zzzyett

最新推荐文章于 2022-12-16 13:55:35 发布

阅读量453

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_43867524/article/details/106012712

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、锁

死锁

不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。

解决死锁：

1、超时机制，超时后，其中一个事务进行回滚；

2、wait-for graph检测死锁：事务请求锁并发生等待时，判断是否存在回路，若存在，选择回滚undo量最小的事务。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以下方式避免死锁：

通过表级锁来减少死锁产生的概率；
多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
同一个事务尽可能做到一次锁定所需要的所有资源。

二、事务

事务特性

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

隔离性（Isolation）

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

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

隔离级别

READ_UNCOMMITTED脏读: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。任何操作都不加锁。
READ_COMMITTED读/写提交: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。
REPEATABLE_READ可重复读: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。——默认
SERIALIZABLE序列化: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。读加共享锁，写加排他锁，读写互斥。

为什么选用可重复读当作默认隔离级别？—主从复制

主从复制，是基于bin log复制的。bin log有三种格式：

statement: 记录的是修改SQL语句
row: 记录的是每行实际数据的变更
mixed: statement和row模式的混合

Mysql在5.0版本以前，只支持STATEMENT格式。而这种格式在RC下主从复制是有bug的，如下图所示，在主(master)上执行如下事务:
在这里插入图片描述
此时在主库中查询：

select * from t;

输出结果：

+---+---+
| c1 |c2
+---+---+
| 2 | 2
+---+---+
1 row in set

而从库中查询，输出结果：

Empty set

这里出现了主从不一致性的问题。原因其实很简单，就是在master上执行的顺序为先删后插，而bin log中语句的顺序以commit为序。从(slave)同步的是bin log，因此从机执行的顺序和主机不一致。

脏读

一个事务读到另一个事务未提交的更新数据。

不可重复读

指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读

是指当事务不是独立执行时发生的一种现象。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

多版本并发控制MVCC

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE等成熟的数据库，出于提升并发性能的考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

多版本控制指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，InnoDB是在undo log中实现的，通过undo log可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

InnoDB存储引擎在数据库每行数据的后面添加了三个字段：

6字节的事务ID(DB_TRX_ID)字段: 用来标识最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(rollback segment)的 undo log。如果一行记录被更新, 则undo log包含重建该行记录被更新之前内容所必须的信息。
6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。 InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚集索引的时候, InnoDB会帮我们自动生成聚集索引，但聚集索引会使用DB_ROW_ID的值来作为主键；如果我们有自己的主键或者合适的唯一索引, 那么聚集索引中也就不会包含 DB_ROW_ID 了。

举例来说，

事务 A 的操作过程为：假设事务 A 对值 x 进行更新之后，该行即产生一个新版本和旧版本。

对 DB_ROW_ID = 1 的这行记录加排他锁
把该行原本的值拷贝到 undo log中， DB_TRX_ID 和 DB_ROLL_PTR 都不动
修改该行的值，更新 DATA_TRX_ID 为修改记录的事务 ID ，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE ， Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁。
记录 redo log ，包括 undo log 中的修改

又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录
事务提交，释放锁

在innodb中，创建一个新事务的时候，innodb会将当前系统中的活跃事务列表创建一个副本（read view），副本中保存的是系统当前不应该被本事务看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候，innodb会将该行当前的版本号与该read view进行比较。

具体的算法是（可重复读级别）：

假设当前数据行事务ID为 T0 ，read view 中保存的最老的事务id T_min ,最新的事务id 为 T_max,当前进行的事务id 为 T_new 。

如果 T0 < T_min ,那么该行数据可见。

因为 T0 在 T_new 事务开始前已经提交。
如果 T0 > T_max ,数据行不可见。根据DB_ROLL_PTR 指针找到下一个数据版本，再次进行数据可见性判断。

因为 T0事务在 T_new 开始前并不存在，也就是说T0 在T_new 开始后创建。
如果 T_min <= T0 <= T_max ，判断T0 是否在read_view 中，如果不在该行数据可见。如果不可见根据DB_ROLL_PTR 指针找到下一个数据版本，再次进行数据可见性判断。

在RR级别，事务在begin/start transaction之后的第一条select读操作后，会创建一个快照(read view)，将当前系统中活跃的其他事务记录记录起来；

在RC级别，事务中每条select语句都会创建一个快照(read view)；

正是因为Read Commited和 Repeatable read的read view 生成方式和时机不同，导致在不同隔离级别下,read committed 总是读最新一份快照数据，而repeatable read 读事务开始时的行数据版本。

MVCC在可重复读级别下解决读的幻读问题：

保证取出的数据不会有后启动的事务中创建的数据。

但不能解决写的幻读问题

在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

当执行select操作时Innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。

如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

使用串行化读的隔离级别
MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

Next-Key锁是行锁和GAP（间隙锁）的合并，行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。GAP锁防止别的事务新增（在区间内加gap锁），行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

Zzzyett

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
MySQL之锁和事务

一、锁锁分类Mysql为了解决并发、数据安全的问题，使用了锁机制。可以按照锁的粒度把数据库锁分为表级锁和行级锁。表级锁对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。行级锁只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种。Record Lock:
复制链接

扫一扫