MySQL的表级锁,行级锁,排它锁和共享锁

前言

如果我们和面试官聊到事务的问题，怎么回答呢？

先说下事务是什么，因为我们业务是比较复杂的，不可能一个sql就能解决的，涉及多个sql就组成一个事务。事务就是一组sql共同执行，要么完全成功，要么完全失败，不能出现部分成功或者部分失败的情况。一个事务有ACID特性（可以参考：事务的ACID特性和MySQL事务的隔离级别）：

• 原子性：要么全部成功，要么全部失败，这样才能保证事务的一致性；

• 一致性：比如银行的转账，扣除一个人的钱肯定要给另一个人加钱，不能光扣除不加，这样业务就存在问题，数据的一致性就破坏了；

• 持久性：当我们数据commit以后，数据是先写到缓存当中，缓存中的数据还是要慢慢花时间往磁盘上写，如果此时停电了、宕机或者重启了，我们有redo log重做日志来保证数据库的持久性；

• 隔离性：这块可以说下事务为什么要有隔离性，因为事务要允许并发执行，一个业务涉及了很多事务，而我们后台往往有很多业务，要能够让他们并发执行，如果所有的事务都是串行执行的话，那这样我们写多线程程序只有一个线程来做事情，这样效率很低。所以事务要并发执行，但是并发执行涉及了一些问题：事务的安全性&一致性和并发的效率问题，我们以这两个东西为参考点，才得到了MySQL不同等级的并发/隔离，如果事务并发执行时我们完全不隔离的话，就可能会出现脏读（事务B读到了事务A还未提交的数据然后，然后用事务A未提交的数据去做计算，得到了很多其他的结果，然后事务A又把那个数据rollback掉，那么事务B计算出来的都是有问题的数据，脏读一定会出现问题）、不可重复读（以同样的条件去一个数据，然后再次去查询的时候发现数据的值有所改变，当然不可重复读也不一定会有问题，有些业务场景下是允许的，这和业务上数据的安全性和一致性是否严格有关）和幻读（在事务中按照同样的条件前后两次查询的结果数据量不同）这些问题。

那么我们为了解决事务并发执行遇到的问题就给出了事务的隔离级别：

• 串行化，串行化完全用锁来实现，通过锁给所有事务排序，按顺序执行，这样做数据的安全性高但并发的效率很低，一般我们不会这样做的。

• 未提交读，对于我们写的多线程程序来说，对于临界区代码段没有做任何的并发控制，虽然并发性高但数据安全性很低，未提交读还允许脏读的存在，这是有问题的所以绝对不会使用未提交读。串行化和未提交读在实际项目中是不会用到的，一般数据库引擎默认工作在已提交读和可重复读，这两个隔离级别就结合了数据的安全性&一致性和数据的并发效率，这两个是由MVCC多版本并发控制机制实现的。

• 已提交读，oracle默认工作级别。不允许读取未commit的数据，这个级别仍然允许不可重复读和虚读产生。

• 可重复读，MySQL默认工作级别。保证事务再次读取是依然得到相同的数据，部分解决了虚读，但虚读是仍然会出现的

注意：

• 事务隔离级别越高，为避免冲突所花费的性能也就越多，即效率低。

• 在“可重复读”级别，实际上可以解决部分的虚读问题，但是不能防止update更新产生的虚读问题，要禁止虚读产生，还是需要设置串行化隔离级别。

事务隔离级别的实现原理：锁+MVCC。串行化底层实现原理是锁，锁有共享锁、排它锁、意向共享锁、意向排它锁、间隙锁和死锁，InnoDB的已提交读和可重复读的底层实现原理：MVCC（多版本并发控制），MVCC提供了一种并发读取方式，包括快照读（同一份数据会有多个版本）、当前读、undo log和redo log。MVCC是已提交读和可重复读的原理，锁是串行化的原理

ACD特性用事务日志实现，I 特性用共享锁、排它锁、MVCC 实现。事务日志分为undo log（回滚日志） 和 redo log（重做日志）

一、表级锁&行级锁

• 表级锁：对整张表加锁。开销小（因为不用去找表的某一行的记录进行加锁，要修改这张表，直接申请加这张表的锁），加锁快，不会出

• 现死锁；锁粒度大，发生锁冲突的概率高，并发度低

• 行级锁：对某行记录加锁。开销大（需要找到表中相应的记录，有搜表搜索引的过程），加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高

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

二、排它锁&共享锁

• 排它锁：又称为X锁，写锁

• 共享锁：又称为S锁，读锁

读读（SS）之间是可以兼容的，但是读写（SX、SX）之间，写写（XX）之间是互斥的

1. 测试不同事务之间排它锁和共享锁的兼容性

我们先查看表SQL及内容

查看隔离级别：

首先开启一个事务A，给id=7的数据加上排它锁：

在另一个客户端开启事务B：

给id=7不管加排它锁和共享锁都阻塞了并没有查询出来，因为A事务给id=7这一行的数据加了排它锁，就是写锁，其他人不能读也不能写。

总结：不同事务之间对于数据的锁，只有SS锁可以共存，XX、SX、XS都不能共存

2. 测试行锁加在索引项上

其实行锁是加在索引树上的。

每次做完测试都把刚做的rollback。

用表的无索引字段作为过滤条件

那现在事务2获取不同行chenwei的记录

InnoDB是支持行锁的，刚才以主键id为过滤条件时，事务1和事务2获取不同行的锁是可以成功的。然而现在我们发现获取name为chenwei的排它锁也获取不到了，这是为什么？我们解释一下：

InnoDB的行锁是通过给索引项加锁来实现的，而不是给表的行记录加锁实现的

而我们用name作为过滤条件没有用到索引，自然就不会使用行锁，而是使用表锁。这就意味着只有通过索引检索数据，InnoDB才使用行级锁，否则InnoDB都将使用表锁!!!

我们给name字段加上索引：

然后再做刚才的操作：

我们发现，给name加上索引后，两个事务可以获取到不同行的排它锁（for update），再一次证明了InnoDB的行锁是加在索引项上的。

因为现在name走的是索引， 通过zhangsan在辅助索引树上找到它所在行记录的id是7，然后到主键索引树上，获取对应行记录的排他锁（个人猜测应该是辅助索引树和主键索引树相应的记录都加了锁）

三、串行化隔离级别测试

串行化所有事务用的都是共享锁或者排它锁，不需用手动添加。select获取的是共享锁，insert、delete和update获取的都是排它锁。

设置串行化隔离级别：

两个事务可以同时获取共享锁（SS共存：

现在让事务2插入数据;

由于Insert需要加排它锁，但是由于事务1已经对整张表加了共享锁，事务2无法再对表成功加锁（sx不共存）

rollback一下，把所有获取锁的状态都回退掉:

开启两个事务:

因为我们给name加上了索引，以上的select相当于给name为zhangsan的数据加上了行共享锁

事务2update;

事务2不能update，因为此时已经被事务1的共享锁锁住了整个表

事务2在辅助索引树上找zhangsan，找到对应的主键值，然后去主键索引树找到相应的记录，但是发现这行记录已经被共享锁锁住了，事务2可以获取共享锁，但是不能获取排他锁

我们再用主键索引试试id能不能update

依然阻塞住了，虽然我们where后面的字段现在使用的id而不是name，但是name也是通过辅助索引树找到对应的主键，再到主键索引树上找相应的记录，而主键索引树上的记录加了锁

我们update id=8的数据，成功了。因为我们select的时候，只是给id=7的数据加上了行锁，我们操作id=8的数据当然可以成功

有索引，则使用行锁；没有索引，则使用表锁。

表级锁还是行级锁说的是锁的粒度，共享锁和排他锁说的是锁的性质，不管是表锁还是行锁，都有共享锁和排他锁的区分。

串行化玩的就是排它锁和共享锁，在可重复读级别下，不手动加锁的话，用的就是MVCC机制，实际上并没有用到锁，我们也可以手动加锁。InnoDB如果不创建索引的话，用的是表锁，如果查询的时候用到了索引项，它用的就是行锁了，行锁是给索引加锁，而不是单纯给一行数据加锁。

来源：https://blog.csdn.net/qq_41721746/article/details/125256039