MySQL锁机制

陈岂几真幸运

已于 2023-10-01 15:03:16 修改

阅读量494

点赞数

分类专栏： MySQL 文章标签：数据库分布式 mysql sql c++

于 2023-05-07 20:23:02 首次发布

本文链接：https://blog.csdn.net/super8ayan/article/details/130497547

版权

MySQL 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

事务要允许并发执行就会导致数据的安全性&一致性和并发效率问题，导致脏读、不可重复读、幻读。

串行化：由锁来实现，会给所有的事务排序，导致并发效率低但是数据的安全性高

未提交读：没有做任何并发处理，并发效率高，数据的安全性最低

常用：已提交读（常见oracle）、可重复读（MySQL）

结合了数据的安全性&一致性 + 并发的效率 MVCC多版本控制机制实现的可重复读

表级锁&行级锁

表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。

行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。

排他锁&共享锁

排它锁（Exclusive），X 锁，写锁。

共享锁（Shared），S 锁，读锁。

X和S锁之间有以下的关系： SS可以兼容的，XS、SX、XX之间是互斥的

        一个事务对数据对象 O 加了 S 锁，可以对 O 进行读取操作但不能进行更新操作。加锁期间其它事务能对O 加 S 锁但不能加 X 锁。

        一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

       显示加锁：select ... lock in share mode强制获取共享锁，select ... for update获取排它锁

InnoDB行级锁

行级锁（record lock）：

InnoDB存储引擎支持事务处理，表支持行级锁定，并发能力更好；

1. InnoDB的行锁是加在索引项上面的，是给索引在加锁，并不是给表的行记录加锁；只有通过索引条件检索数据，InnoDB使用的才是行级锁，否则使用的是表锁；

2、由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是 InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。

3、即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。

间隙锁（gap lock）：

InnoDB串行化隔离级别如何解决幻读？间隙锁

1. 当使用范围查询条件，并请求共享锁或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加索；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)” ，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

举例来说，假如 user 表中只有 101 条记录，其 userid 的值分别是 1,2,...,100,101，下面的 SQL：

select * from user where userid > 100 for update;

这是一个范围条件的检索，InnoDB不仅会对符合条件的userid值为101的记录加锁，也会对userid大于101的“间隙”加锁，防止其他事务在表的末尾增加数据。

InnoDB使用间隙锁的目的，为了防止幻读，以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 userid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读。

2. 当使用等值查询时，再次插入age=18时会被阻塞住，会在等值18的两边加上间隙锁

select * from user where age = 18;

（如果：

1. age是辅助索引（值可重复）会加 record lock + gap lock

2. age是主键索引或者唯一索引（值是不允许重复的），只有record lock）

（辅助索引值相等的情况下（辅助索引是可以重复的），主键按升序排列）比如说插入一个age=15的，会阻塞，因为他的主键id会升序排列24，会在已有的15和18之间，由于有间隙锁，所以无法加入。

间隙锁注意事项：

间隙锁在 SERIALIZABLE（串行化）和 REPEATABLE READ（可重复读）两种事务隔离级别中都会使用，但在 READ COMMITTED（已提交读）和 READ UNCOMMITTED（未提交读）级别中不会使用。
间隙锁可能导致锁定争用和死锁，因此在设计数据库和选择索引时需要小心。
使用显式的主键和唯一索引可以帮助减少间隙锁的复杂性和锁定争用。

意向锁

意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

（其他事务在获取一个表锁的时候，可以拿来跟意向锁进行快速的对比来判断，我能不能获取当前表的s或者x锁。）

1）意向共享锁（IS锁）：事务在给一行记录加s共享锁前，必须s先取得该表的 IS 锁。
2）意向排他锁（IX锁）：事务在给一行记录加x排他锁前，必须先x取得该表的 IX 锁。

意向锁和共享锁、排他锁的兼容关系：

1、意向锁是由InnoDB存储引擎获取行锁之前自己获取的；

2、意向锁之间都是兼容的，不会产生冲突；

3、意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）

4、意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。

意向锁如何解决表锁涉及的效率问题？

需要在大量数据中检验没有被其他事务获取过X锁，有了意向锁之后，事务在给行加锁之前，都活获取表的IS或者IX锁，所以当事务要获取表的X锁时，不需要在检查表中那些行锁被（X或者S）占用，只需要快速检查IX和IS锁即可。效率提升了

InnoDB表级锁

在绝大部分情况下应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用表级锁；

1）事务需要更新大部分或者全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能会造成其他事务长时间等待和锁冲突；

2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚

LOCK TABLE user READ；读锁锁表

LOCK TABLE user WRITE; 写锁锁表

事务执行...

COMMIT/ROLLBACK; 事务提交或者回滚

UNLOCK TABLES; 本身自带提交事务，释放线程占用的所有表锁

使用表锁的时候涉及到一个效率的问题：

要获取一张表的共享锁S或者排他锁X，最起码得确定，这张表没有被其他事务获取过X锁！比如说一张表（一千万个数据）没有被其他事务获取过行锁X锁！

举例：事务一：表S锁事务二就不可以获取表X锁、行X锁

事务一：表X锁事务二就不可以获取表X锁、行X锁

效率太低

MVCC（多版本并发控制）

MVCC是多版本并发控制（Multi-Version Concurrency Control，简称MVCC），是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别的实现，也经常称为多版本数据库。

MVCC机制会生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本（系统版本号和事务版本号）。

1. 快照读（snapshot read） ：读的是记录的可见版本，不用加锁。如select

2. 当前读（current read）：读取的是记录的最新版本，并且当前读返回的记录，insert，delete，update，select...lock in share mode/for update

已经提交读和可重复读 =》底层实现原理是 MVCC（多版本并发控制）=> 并发的读取方式：快照读

InnoDB提供了两个读取操作：

锁定读（SX）和非锁定读（MVCC提供的快照读）=》依赖底层的一个技术 =》 undo log 回滚日志

回顾：

事务日志：undo log （回滚日志） redo log（重做日志）

事务的ACD特性是由事务日志完成，I由锁+MVCC完成

undo log回滚日志的主要作用：

1. 事务发生错误时回滚rollback

2. 提供了MVCC的非锁定读（快照读）

MVCC：每一行记录实际上有多个版本，每个版本的记录除了数据本身之外，增加了其他字段

undo log 提供在表中新增两列：

DB_TRX_ID:事务ID ：记录是那个事务修改的当前事务ID

DB_ROLL_PTR:回滚指针 之前旧数据的地址 指向undo log日志上数据的指针

各个旧数据之间由链表联系起来，新增数据的旧数据是空的NULL

已提交读的MVCC：（快照读、非锁定读）

（每次执行语句并成功commit的时候都重新生成一次快照（Read View），每次select查询时。）

1. 已提交读底层如何解决脏读问题？

已提交读使用的是一种非锁定读，也就是MVCC多版本并发控制提供的一种快照读来解决的，事务每一次select都会把符合当前查询条件的数据拍个照片，相当于生成一次当前数据快照的一个数据版本，生成这个数据版本的时候有一个前提就是数据的状态必须是commit提交状态，当我们对一个数据进行修改但未提交，数据处于prepare状态，所以在生成数据快照的时候用的还是旧数据来生成的，也就是在undo log里面的旧数据，所以不会出现脏读。

2. 但为什么无法解决不可重复读？

因为每一次select都会重新产生一次数据快照，其他事务更新后而且已提交数据，符合快照读的条件，可以实时反馈到当前事务的select结果中。

3. 为什么无法解决幻读？

因为每一次select都会重新产生一次数据快照，其它事务增加了和当前事务查询条件相同的新的数据并且成功commit提交，导致当前事务再次以同样的条件查询时，数据多了。

可重复读的MVCC：

（同一个事务开始的时候生成一个当前事务全局性的快照（Read View），第一次select查询时）

1. 解决了“脏读问题”

都是生成数据快照，生成数据快照的前提就是数据状态必须是commit

2. 解决了不可重复读问题

第一次select产生数据快照，而且只产生一次！！！！！

第一次select产生数据快照，其它事务随谈修改了最新的数据，但是当前事务select时，依然查看的是最初的快照数据。

3. 部分解决了幻读

对于select操解决了幻读，原理同上，只在第一次select产生快照；但是对于updata等使用当前读方法的操作，并没有解决幻读问题。

快照内容读取原则：

1、版本未提交无法读取生成快照

2、版本已提交，但是在快照创建后提交的，无法读取

3、版本已提交，但是在快照创建前提交的，可以读取

4、当前事务内自己的更新，可以读到

MySQL乐观锁悲观锁

都是用于数据库中实现并发控制的锁机制

悲观锁：

悲观锁是一种较为保守的锁策略，它的基本思想是，假设最坏的情况，即认为在整个数据处理过程中都会有其他的事务来干扰，因此，在整个数据处理过程中，在读取数据时，先对目标记录进行加锁，其他用户无法修改该记录，直到当前用户释放锁，其他用户才能进行修改。主要通过SELECT...FOR UPDATE语句实现。

悲观锁在并发量较大的情况下容易导致阻塞现象，降低了系统的并发处理性能。

乐观锁：

乐观锁是一种较为乐观的锁策略，它的基本思想是，每次操作数据时，都认为不会发生并发冲突，因此不会加锁，而是在提交操作时检查数据是否发生冲突。如果发生了冲突，则返回错误，由应用程序决定如何处理。乐观锁通常使用版本号机制来实现，即每个数据行都会有一个版本号，每次更新时都会将版本号加一，操作数据时判断版本号是否一致，如果一致则更新数据，否则返回错误。

乐观锁在并发量较大的情况下能够提高系统的并发处理性能，但是也存在着数据冲突处理的问题。

死锁

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。

但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。

死锁问题一般都是我们自己的应用造成的，和多线程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同导致的死锁问题。因此我们应用对数据库的多个表做更新的时候，不同的代码段应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

事务被阻塞了，或者死锁了，mysql是有事务阻塞的超时时间，事务处理失败

mysqld只能能够检测到死锁问题的发生，并进行事务的回滚

锁的优化建议（持续更新）

1.尽量使用较低的隔离级别

2.设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力

3.选择合理的事务大小，小事务发生锁冲突的概率小

4.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会

5.尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

6.不要申请超过实际需要的锁级别

7.除非必须，查询时不要显示加锁