MySQL行锁、表锁&间隙锁

lliuhao--

已于 2023-05-10 20:05:09 修改

阅读量849

点赞数

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

于 2023-05-09 14:19:39 首次发布

本文链接：https://blog.csdn.net/qq_41897304/article/details/130578614

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章详细介绍了数据库事务的隔离级别，包括读未提交、读已提交、可重复读和串行化，以及不同级别的锁机制。重点讨论了InnoDB存储引擎的行级锁、表级锁、共享锁（S锁）、排它锁（X锁）、间隙锁和意向锁的工作原理，强调了行锁与表锁的选择策略，并提到了死锁问题及其预防措施。

摘要由CSDN通过智能技术生成

事务隔离级别的实现原理：锁

表级锁&行级锁

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

对于InnoDB引擎，绝大部分情况应该使用行锁
使用表锁中，表比较大，事务需要更新全部或大部分数据
事务涉及到多个表，比较复杂，可能引起死锁，造成大量的事务回滚

排它锁和共享锁

共享锁（Shared），又称为S锁，读锁
共享锁锁定的资源可以被其他用户读取，但不能修改

在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

排它锁（Exclusive），又称为X锁，写锁
排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改

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

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

//对某一行加上共享锁
select uid from student where uid=1 lock in share mode; 
//对某个数据行上添加排它锁
select uid from student where uid=1 for update;

InnoDB行级锁

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

行级锁

InnoDB的行锁是通过给在索引上的索引项加锁来实现的，是给索引在加锁，并不是给单纯表的行记录在加锁；索引若过滤条件没有索引的话，使用的就是表锁，而不是行锁！！！
由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但若使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行
即使SQL中使用了索引，但是经过MySQL的优化器后，若认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。

间隙锁（gap lock）（串行化隔离级别怎么解决幻读问题？）

间隙锁是专门用于解决幻读这种问题的锁，它锁的是行与行之间的间隙，能够阻塞新插入的操作

间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

注意：读读不互斥，读写/写读/写写实互斥的，但是间隙锁之间是不冲突的，间隙锁会阻塞插入操作。另外，间隙锁在可重复读级别下才是有效的。

幻读场景：
在这里插入图片描述

第一类条件：范围查询
在这里插入图片描述

注：当使用索引时，经过MySQL优化器，认为全盘扫描比使用索引效率高，则变成表级锁，当前只能插入表头之前或表尾之后。

第二类条件：等值查询
引入上图场景所用表进行解读
在这里插入图片描述
注：若age是主键索引和唯一索引(值是不允许重复的)，那就只有行锁

间隙锁和next-key lock：
行锁和间隙锁合称为next-key lock，这个锁是左开右闭的区。

意向共享锁和意向排他锁

1、意向锁是由InnoDB存储引擎获取行锁之前自己获取的
2、意向锁之间都是兼容的，不会产生冲突
3、意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）
4、意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某
行。

InnoDB表级锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用
表级锁；
1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；
2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
如：

LOCK TABLE user READ；读锁锁表
LOCK TABLE user WRITE; 写锁锁表

事务执行…

COMMIT/ROLLBACK; 事务提交或者回滚
UNLOCK TABLES; 本身自带提交事务，释放线程占用的所有表锁

死锁

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。

mysql> select * from test_dead_lock where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

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

锁的优化建议

1.尽量使用较低的隔离级别
2.设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力
3.选择合理的事务大小，小事务发生锁冲突的概率小
4.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序
存取表中的行。这样可以大大减少死锁的机会
5.尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
6.不要申请超过实际需要的锁级别
7.除非必须，查询时不要显示加锁