前言大家好，我是田洛。金牌、三牌、银牌和四牌即将推出。我们准备了12道关于数据库锁的连续题。相信对大家阅读会有帮助。1、为什么需要加锁？日常生活中，如果心情不好想静一静，不想被别人打扰，可以把自己锁在房间里，上锁。这就是锁定生命。同理，对于MySQL数据库，一般的对象是一个事务一个事务。那么，如果一个SQL正在更新一个事务中的某条记录，我们肯定不希望它受到其他事务的影响吧？因此，数据库设计大叔给数据行加了一把锁（行锁）。说得专业一点：如果有多个并发请求访问数据，可能会产生多个事务同时操作同一行数据。如果不对并发操作进行控制和锁定，可能会写入错误的数据，或者读取错误的数据，破坏数据的一致性。因此，需要考虑锁。2、InnoDB有哪些锁？2.1共享/排它锁InnoDB实现了两种标准的行级锁：共享锁（简称S锁）和排它锁（简称X锁）。共享锁：简称S锁。当一个事务要读取一条记录时，需要先获取该记录的S锁。独占锁：简称X锁。当事务需要修改一条记录时，需要先获取该记录的X锁。如果事务T1持有行R的S锁，当另一个事务T2请求访问这条记录时，它会做如下事情：T2立即请求允许S锁，结果T1和T2都持有S锁R行的T2请求的X锁不能立即授予，如果T1持有R行的X锁，这个操作会阻塞，那么T2请求的R的X和S锁不能立即授予，T2必须等待T1释放X锁没问题，因为X锁与任何其他锁都不兼容。S锁和X锁的兼容关系如下：X锁和S锁是针对行记录的，所以可以称为行级锁或行锁。我们认为行锁的粒度比较细。其实一个事务也可以在表级别加锁。相应的，我们称之为表锁。加在表上的锁也可以分为X锁和S锁。如果一个事务给表加了S锁，那么：其他事务可以继续获取表的S锁，也可以获取表中部分记录的S锁。其他事务无法继续获取表的X锁，也无法获取表中部分记录的X锁。如果一个事务给表加了X锁，那么其他事务就无法获得该表的S锁，也无法获得该表部分记录的S锁。其他事务无法获得该表的X锁，也无法继续获得该表部分记录的X锁。2.2意向锁什么是意向锁？意向锁是与行级锁不冲突的表级锁。在未来的某个时候，当事务可能需要添加共享锁或排他锁时，提前声明一个意图。请注意，意向锁是表级锁。为什么需要意向锁？或者换个说法，为什么我们在加共享锁或者独占锁的时候需要提前声明意向锁呢？因为InnoDB支持表锁和行锁并存，如果事务A获取了某行的排他锁但没有提交，则事务B请求获取同一张表的共享表锁。因为共享锁和排他锁是互斥的，事务B要给这张表加共享锁时，需要保证没有其他事务持有这张表的表排他锁，同时要保证：没有其他事务持有表排他锁中的任何行。那么问题来了，如果要保证没有其他事务持有表中任意一行的独占锁，每一行都要遍历？这显然是一种非常低效的方法。为了解决这个问题，InnoDb的设计大叔提出了意向锁。意向锁是如何解决这个问题的？意向锁分为两类：意向共享锁：简称IS锁。当事务要对某些记录加S锁时，需要在表级加IS锁。意向排他锁：简称IX锁。当一个事务要对某条记录加X锁时，需要在表级加IX锁。例如：select...lockinsharemode，需要给表设置IS锁；select...forupdate，需要给表设置IX锁；意向锁是如何解决这个低效率问题的：如果事务A获取了某行的排他锁，但是没有提交。这时表上有一个意向排他锁，这个行上有一个排他锁。这时事务B想要获取这张表的共享锁。此时检测到事务A持有表的意向排他锁，所以事务A必须持有某些行的排他锁，也就是说事务B对该表持有排他锁。锁请求需要阻塞等待，不再需要检测表中每一行数据是否有排他锁。意向锁只表示意向锁。意向锁不是互斥的，可以并行化。整体兼容性如下：2.3记录锁（RecordLock）记录锁是最简单的行锁，只锁一行。例如：SELECTc1FROMtWHEREc1=10FORUPDATE，如果C1字段是主键或者唯一索引，这条SQL会加一个记录锁（RecordLock）。记录锁总是加在索引上，即使一个表没有Index，InnoDB也会隐式创建一个索引，并使用这个索引来实现记录锁。它将阻止其他事务插入、更新和删除这一行记录。一般我们看死锁日志都是找关键字，比如lock_modeXlocksrecbutnotgap)，意思是X型记录锁。记录锁的关键字是rec，不是gap。以下是记录锁日志：RECORDLOCKSspaceid58pageno3nbits72index`PRIMARY`oftable`test`.`t`trxid10078lock_modeXlocksrecbutnotgapRecordlock,heapno2PHYSICALRECORD：n_fields3;紧凑格式；信息位00:len4;十六进制8000000a；升序;;1:长度6;十六进制00000000274f；升序'O;;2:长度7;十六进制b60000019d0110；)为了解决幻读问题，InnoDB引入了间隙锁（GapLock）。间隙锁是在两个索引之间添加的锁，或者在第一个索引之前或最后一个索引之后的间隙上。它锁定一个区间，而不是只锁定这个区间内的每一条数据。例如lock_modeXlocksgapbeforerec表示X型gap锁。下面是一个gaplock的日志：RECORDLOCKSspaceid177pageno4nbits80indexidx_nameoftable`test2`.`account`trxid38049lock_modeXlocksgapbeforerecRecordlock,heapno6PHYSICALRECORD:n_fields2;紧凑格式；信息位00：len3；十六进制576569；升维;;1:长度4;十六进制80000002；asc;;2.5Next-KeyLock（下一键锁）Next-keylock是recordlock和gaplock的组合，指的是加在一条记录和这条记录前面的gap上的锁。更具体的说：key锁会阻塞索引记录本身和索引记录之前的区间，也就是它的锁区间先开后闭，比如(5,10)。如果一个session占用了索引记录RShared/exclusivelock，其他session不能在紧接R之前的间隙插入新的索引记录，如果一个session在一个索引中的记录R上有共享锁或者排他锁，那么另一个session不能在紧接R之前的间隙插入新的索引记录2.6InsertintentionlockInsertintentionlock是在插入一行记录操作之前设置的间隙锁，这个锁释放一个插入方法的信号，它解决的问题：同一个索引的多个事务，在插入记录的时候相同的范围区间，如果插入的位置不冲突，则不会互相阻塞。假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获取独占访问insertedrow锁之前，4和7之间的间隙被插入意向锁锁住了，但是对方没有被阻塞，因为insertedrows没有冲突。以下是插入意向锁的日志：RECORDLOCKSspaceid31pageno3nbits72index`PRIMARY`oftable`test`.`child`trxid8731lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heap否3物理记录：n_fields3；紧凑格式；信息位00:len4;十六进制80000066；上升f;;1:长度6;十六进制000000002215；asc";;2:len7;hex9000000172011c;ascr;;...锁模式兼容矩阵（横向是持有锁，纵向是申请锁）：2.7自增锁是一种特殊的表级锁。它是为AUTO_INCREMENT类型的列设计的，对于这样的列，如果有新的数据加入到表中，它会持有自增锁。简而言之，如果一个事务正在向表中插入记录，则所有其他事务插入都必须等待，以便第一个事务插入的行是连续的主键值。官方文档是这样描述的：AUTO-INC锁是一种特殊的表级锁，由插入到具有AUTO_INCREMENT列的表中的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待自己向该表中执行插入，以便第一个事务插入的行接收连续的主键值。假设有一张表：mysql>createtablet0(idintNOTNULLAUTO_INCREMENT,namevarchar(16),primarykey(id));mysql>showvariableslike'%innodb_autoinc_lock_mode%';+-------------------------+--------+|变量名|值|+------------------------+------+|innodb_autoinc_lock_mode|1|+-------------------------+--------+1行在集合中，1个警告（0.01秒）集合事务A和B交替执行过程如下：从上图可以看出，当我们在事务A中插入自增列时，会话事务B也进行了插入操作。这种情况下会出现两个奇怪的现象：事务A的会话中的事务自增列好像直接增加了2个值。（如上图第7步和第8步）事务B的session中的自增列直接从2增加。（如上图第5步和第6步）既然自增锁是表级锁，为什么事务B在会话A的事务结束之前就可以执行插入成功呢？桌子不应该上锁吗？这是因为在参数innodb_autoinc_lock_mode上，当这个参数设置为1时，相当于将这个auto_inclock弱化为更轻的互斥自增长机制来实现，官方称之为mutex。innodb_autoinc_lock_mode也可以设置为0或2，0：表示传统锁模式，使用表级AUTO_INC锁。事务的INSERT-LIKE语句在语句执行后释放AUTO_INC表级锁，而不是在事务结束后。1：连续锁模式，连续锁模式不使用表级锁进行Simpleinsert，而是使用轻量级锁产生自增值，因为InnoDB可以提前到插入多少行数据。自增生成阶段使用轻量级互斥量生成所有值，而不是持有锁直到插入完成。使用AUTO_INC表级锁进行批量插入，直到语句完成。2：交错锁模式，所有INSERT-LIKE语句都不使用表级锁，而是使用轻量级互斥锁。INSERT-LIKE：指所有插入语句，包括：INSERT、REPLACE、INSERT...SELECT、REPLACE...SELECT、LOADDATA等。简单插入：指在插入前可以确定插入行数的语句，包括：INSERT、REPLACE，不包括INSERT...ONDUPLICATEKEYUPDATE等语句。批量插入：指插入时无法确定行数的语句，包括：INSERT...SELECT/REPLACE...SELECT/LOADDATA。3.什么是死锁？如何防止死锁？什么是死锁？死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方的资源，从而形成恶性循环的现象。如何防止死锁？尽量约定固定的顺序访问表，因为交叉访问更容易造成事务等待循环。尽量避免大额交易，建议拆分成多个小额交易。因为大事务占用的锁资源越多，越容易出现死锁。降低数据库隔离级别，比如RR到RC，因为RR隔离级别有GAP锁，死锁的概率会高很多。死锁和索引密不可分。适当优化索引将减少死锁的可能性。如果业务处理不好，可以使用分布式事务锁或者乐观锁。4、数据库中的乐观锁和悲观锁。他们都担心自己心爱的数据可能会被其他事务修改，所以一个事务拥有（获得）悲观锁后，其他事务都不能修改数据，只能在锁释放后执行。select...forupdate是mysql悲观锁的应用。乐观锁：乐观锁的“乐观”体现在它认为数据变化不会太频繁。因此，它允许多个事务同时对数据进行更改。实现方式：乐观锁一般是通过版本号/时间戳来判断一条记录是否被更改，一般是用CAS算法来实现的。可以看看这篇文章：用丰富的图文让你彻底理解悲观锁和乐观锁。5.selectforupdate是加表锁还是行锁的面试题一般需要分为两个数据库隔离级别（RR和RC）），如果查询条件是唯一索引也需要单独讨论、主键、普通索引、无索引等。在RC隔离级别下，如果查询条件是唯一索引，会加一个IX意向排他锁（表级锁不影响Insert），两个X排他锁（行锁，分别对应唯一索引和主键索引），如果查询条件是主键，IX意向排他锁（表级锁，不影响插入），主对应一个X排他锁keyLock（行锁，会锁住主键索引的行）。如果查询条件是普通索引，如果查询命中一条记录，一个IX意向排他锁（表锁）和两个X排它锁（行锁，分别对应普通索引的X锁和primary的X锁键分别）将被添加；如果没有命中数据库表中的记录，则只加一个IX意向排他锁（表锁，不影响插入）。如果查询条件是无索引，会加两把锁，一把IX意向排他锁（表锁），一把X排它锁（行锁，主键对应的X锁）。查询条件是没有索引，为什么不锁表呢？MySQL会使用聚簇（主键）索引进行全表扫描过滤。每条记录都会加X锁。不过为了效率，MySQL在这方面做了改进。扫描过程中，不满足过滤条件的记录将被解锁。同时优化违反了2PL原则。在RR隔离级别，如果查询条件是唯一索引，命中数据库表记录时，一共加三把锁：一把IX意向排他锁（表锁，不影响插入），一把X主键对应排他锁（行锁），唯一索引对应一个X排他锁（行锁）。如果查询条件是主键，一个IX意向排他锁（表级锁，不影响插入），一个X主键对应的排他锁（行锁，会锁住主键的行索引）将被添加。如果查询条件是普通索引，如果命中查询记录，除了X锁（行锁），IX锁（表锁，不影响插入），Gap锁（间隙锁，会影响插入）添加。如果查询条件是无索引，会加一个IX锁（表锁，不影响插入），每行实际记录行加一个X锁，supremum伪记录对应的虚拟全表行锁添加。这种场景，通俗地说，其实就是表锁。可以看我之前写的这篇文章：说说selectforupdate加了什么样的锁。它的优点是：简单、易用，无需引入Redis、zookeeper等中间件。缺点是：不适合高并发场景，db操作性能较差。在我之前的文章中，详细介绍了数据库分布式锁的实现。可以看看：面试要求：谈谈分布式锁的多种实现！7、表级锁、行级锁、页锁有什么区别？存储引擎不同：MyISAM存储引擎，只支持表锁，并发写入时性能较差。InnoDB存储引擎同时支持表锁和行锁，默认是行级锁。BDB存储引擎，支持表锁和页锁。表级锁和行级锁的区别：表锁：开销小，加锁速度快；强锁，锁冲突概率高，并发度最低；没有僵局。行锁：开销大，加锁慢；可能会出现死锁；加锁粒度小，锁冲突概率低，并发性高，行级锁可以大大减少数据库操作中的冲突页锁：开销和加锁时间介于表锁和行锁之间；会有死锁；锁粒度介于表锁和行锁之间，并发度一般。8、Mysql如何锁定一条SQL语句？一个SQL锁可以分为9种情况：组合1：id列为主键，RC隔离级别组合2：id列为二级唯一索引，RC隔离级别组合3：id列为二级非唯一索引，RC隔离级别组合四：id列上没有索引，RC隔离级别组合五：id列为主键，RR隔离级别组合六：id列是辅助唯一索引，RR隔离级别组合七：idcolumn是secondarynon-uniqueindex，RR隔离级别组合八：id列上没有索引，RR隔离级别组合九：Serializable隔离级别，可以看我的文章，第三节有详细解释：20000字详细！InnoDB锁题！9、并发情况下，如何安全修改同一行数据要安全修改同一行数据，需要保证一个线程在修改的时候其他线程不能更新这一行记录。其实一般有悲观锁和乐观锁两种思想。悲观锁的思想是当当前线程要修改数据的时候，其他线程就得关掉~比如可以用select...forupdate~,select*fromUserwherename='jay'forupdate上面的SQL语句会锁定User表中所有满足搜索条件（name='jay'）的记录。在提交此事务之前，没有其他线程可以修改这些记录。也可以使用乐观锁的思想：所谓乐观锁的思想就是当一个线程过来的时候，先放手修改。如果看到其他线程没有修改，就可以修改成功了。如果有其他线程修改过，则修改失败或重试。.实现方法：乐观锁一般使用版本号机制或者CAS算法来实现。可以看看我的文章，主要思想是~CAS乐观锁是解决并发问题的一种实践当然，回答这个问题的时候，可以提到分布式锁。分布式锁的实现方式有三种：数据库分布式锁、Redis分布式锁、Zookeeper分布式锁，可以看我的文章：面试要求：谈谈分布式锁的多种实现！10、RR隔离级别下的加锁规则是什么？其实在极客时间丁奇的MySQL45讲座中就提到了RR隔离级别是如何锁定的。有兴趣的可以下单看看。这是一个很好的课程。首先MySQL的版本是5.x系列<=5.7.24，8.0系列<=8.0.13。加锁规则包括：两个原则，两个优化，一个bug。原则1：加锁的基本单元是下一键锁。下一键锁（next-keylock）是之前开启，之后关闭的区间。原则二：只有在搜索过程中访问到的对象才会被锁定。优化一：对于索引的等价查询，在对唯一索引加锁时，next-key锁退化为记录锁。优化2：对于索引的等价查询，当向右遍历最后一个值不满足等价条件时，next-key锁退化为间隙锁（Gaplock）。一个错误：唯一索引的范围查询将访问直到第一个不满足条件的值。可以看我的文章，第四节有详解：20000字的详解！InnoDB锁题！11.InnoDB中行级锁是如何实现的？InnoDB的三种行锁算法MySQL上的行级锁是通过在索引上加锁索引项来实现的。只有在通过索引条件检索数据时，InnoDB才使用行级锁。否则，InnoDB使用表锁。同时，当不同行的两条记录使用同一个索引键时，也会发生锁冲突。例如，这个SQL：select*fromtwhereid=666forupdate;update的复制代码可以根据条件完成行锁，id是有索引键的列。如果id不是索引键，InnoDB将实现表锁。InnoDB行锁的3种算法：RecordLock：对单个索引记录的锁，GapLock：间隙锁，锁定一个范围，但不包括记录本身Next-KeyLock：等于GapLock+RecordLock，锁定一个范围，并锁定记录本身。如果查询条件是唯一索引或主键，Next-KeyLock将简化为RecordLock。如果是普通索引，会在下一个键值上加一个间隙锁，其实就是对下一个键值的范围加锁。间隙锁就是为了解决幻读问题而设计的。间隙锁处于RR隔离级别。如果想关闭间隙锁，可以修改隔离级别。也可以修改数据库参数innodb_locks_unsafe_for_binlog为1.12。MySQL是否遇到过死锁问题，你是如何解决的？我解决死锁的一般步骤很简单：检查死锁日志showengineinnodbstatus；找出死锁sql分析sql锁情况模拟死锁事件分析死锁日志分析死锁结果
