大家好，我是小林。最近加入《MySQL 有哪些锁》文章内容：添加记录锁，间隙锁，网键锁，添加插入意向锁，添加自增锁为innodb_autoinc_lock_mode=2模式，解释为什么主从环境有不安全的问题所以，现在的内容比较全面，基本把MySQL用到的锁都说了，大家可以复习一下。这次我们来聊聊MySQL锁，主要是以问答的形式，看起来比较容易。不用BB了，走吧！在MySQL中，根据加锁的范围，可以分为全局锁、表级锁和行锁三种。全局锁全局锁如何使用？要使用全局锁，需要执行这条命令：flushtableswithreadlock执行后，整个数据库处于只读状态。此时其他线程在执行以下操作时会被阻塞：增删改数据，如insert、delete、update等语句；表结构的改变，如altertable、droptable等语句。如果要释放全局锁，需要执行这条命令：unlocktables当然，当会话断开时，全局锁会自动释放。全局锁的应用场景是什么？全局锁主要用于整个数据库的逻辑备份，使得在数据库备份过程中，备份文件中的数据不会因为数据或表结构的更新而与预期的不同。举个例子大家就知道了。全库逻辑备份时，假设不加全局锁，看看会出现什么意外情况。如果用户在全库逻辑备份时购买了商品，一般购买商品的业务逻辑会涉及更新多张数据库表，比如在user表更新用户的余额，然后在product表更新购买的商品货物库存。那么，可能会有这样的顺序：先备份user表的数据；然后用户发起购买商品的操作；然后备份product表的数据。即在备份用户表和商品表之间，用户购买了商品。这种情况下，备份的结果是user表中的用户余额没有被扣除，但是product表中商品的库存减少了。如果备份文件用于以后恢复数据库数据，用户不缺钱，但库存少了，就说明用户白买了一个产品。所以在全库逻辑备份时，如果加了全局锁就不会出现上述情况。添加全局锁有什么缺点？添加全局锁意味着整个数据库是只读的。那么如果数据库中的数据很多的话，备份就会花费很多时间。关键是在备份期间，业务只能读取数据，不能更新数据，会导致业务停滞。既然在备份数据库数据时使用全局锁会影响业务，请问有没有其他办法避免呢？是的，如果数据库引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前启动事务，先创建一个ReadView，然后在整个事务执行过程中使用这个ReadView，并且由于支持MVCC，业务在备份期间仍然可以更新数据。因为在可重复读的隔离级别下，即使其他事务更新了表中的数据，也不会影响备份数据库时的ReadView。交易开始时的数据。备份数据库的工具是mysqldump。使用带有--single-transaction参数的mysqldump时，事务将在备份数据库之前启动。该方法只适用于支持“可重复读隔离级别事务”的存储引擎。InnoDB存储引擎默认的事务隔离级别是可重复读，所以可以使用这种方式来备份数据库。但是对于不支持事务的引擎，比如MyISAM，在备份数据库的时候就必须使用全局锁的方式。表级锁什么是MySQL表级锁？具体怎么用。MySQL中有几种表级锁：表锁；元数据锁（MDL）；意向锁；AUTO-INC锁；表锁先说表锁。如果我们想给student表（t_student）加表锁，可以使用如下命令：//表级共享锁，即读锁；locktablest_studentread;//表级独占锁，即写锁；锁定表t_stuent写；需要注意的是，表锁除了会限制其他线程的读写外，还会限制本线程后续的读写操作。也就是说，如果这个线程给student表加了“共享表锁”，那么如果这个线程要对student表进行写操作，就会被阻塞。当然其他线程在写student表的时候也会被阻塞。将被阻塞，直到锁被释放。要释放表锁，可以使用下面的命令，它会释放当前会话中所有表的锁：unlocktables另外，当会话退出时，所有的表锁也会被释放。但是尽量避免对使用InnoDB引擎的表使用表锁，因为表锁的粒度过大，会影响并发性能。InnoDB很棒，因为它实现了更细粒度的行级锁。元数据锁让我们谈谈元数据锁（MDL）。我们不需要显式使用MDL，因为当我们对数据库表进行操作时，MDL会自动添加到表中：当对表进行CRUD操作时，添加MDL读锁；当对表进行结构更改操作时，添加MDL写锁；MDL是为了保证当用户对表进行CRUD操作时，防止其他线程对表结构进行修改。当一个线程正在执行select语句（有MDL读锁）时，如果其他线程想要改变表的结构（申请MDL写锁），就会被阻塞，直到select语句执行完毕（释放MDL读锁）)。反之，当一个线程改变表结构（加MDL写锁）时，如果其他线程进行CRUD操作（申请MDL读锁），它们将被阻塞，直到表结构改变完成（释放MDL写锁）。MDL不需要显示和调用，那么什么时候发布呢？MDL在事务提交后释放，也就是说在事务执行期间一直持有MDL。那么如果数据库有一个长事务（所谓长事务就是事务已经开启但是还没有提交），那么当表结构改变的时候，可能会发生意想不到的事情，比如下面的序列场景：首先，线程A首先开启事务（但还没有提交），然后执行一条select语句，此时，对表加了MDL读锁；然后，线程B也执行同样的select语句，此时它不会阻塞，因为“读”不冲突；然后，线程C修改表字段。此时因为线程A的事务还没有提交，也就是MDL读锁还在被占用，此时线程C无法申请到。对MDL的写锁会被阻塞，那么线程C阻塞后，后续对该表的select语句也会被阻塞，如果此时有大量请求对该表的select语句，就会有大量的线程阻塞了，数据库线程很快就满了。为什么线程C申请不到MDL写锁，那么后续申请读锁的查询操作也会被阻塞？这是因为申请MDL锁的操作会形成一个队列，在队列中获取写锁的优先级高于读锁。一旦发生MDL写锁等待，所有后续对该表的CRUD操作都会被阻塞。因此，为了安全地更改表结构，在更改表结构之前，首先检查数据库中的长事务，看是否有事务对表加了MDL读锁。如果可以考虑把这个长事务干掉，然后对表结构进行修改。意向锁接下来说一下意向锁。在使用InnoDB引擎对表中的某些记录加“共享锁”之前，需要在表级别加“意向共享锁”；在使用InnoDB引擎对表中的某些记录加“排它锁”之前，需要在表级别加“意向排它锁”；即在进行insert、update、delete操作时，需要先对表加“意向排他锁”，再对记录加排它锁。普通select不加行级锁。普通的select语句使用MVCC实现一致读，并且是无锁的。不过select也可以给记录加上共享锁和排它锁。具体方法如下：//先给表加意向共享锁，再给读记录加共享锁select...lockinsharemode;//先给表加意向排他锁，然后对读记录select...forupdate加排它锁；意向共享锁和意向排它锁都是表级锁，不会和行级共享锁和排它锁发生冲突，意向锁之间不会有冲突，只会和共享表锁（locktables...读）和独占表锁（锁表...写）。表锁和行锁满足读写共享，读写互斥，写写互斥。如果没有“意向锁”，那么在加“独占表锁”时，需要遍历表中的所有记录，检查记录是否有独占锁，会很慢。然后就是“意向锁”，因为在给记录加排他锁之前，会先加一个表级的意向排他锁，所以在加“排他表锁”的时候，直接检查表是否有意向排它锁。意思是表中已经有记录被独占锁了，这样就不用遍历表中的记录了。因此，意向锁的目的是快速判断表中是否有记录被锁定。AUTO-INC锁表中的主键通常设置为自增，这是通过在主键字段上声明AUTO_INCREMENT属性来实现的。之后插入数据时，可以不指定主键的值，数据库会自动给主键赋一个递增的值，主要是通过AUTO-INC锁实现的。AUTO-INC锁是一种特殊的表锁机制。锁不会在另一个事务提交后释放，而是在插入语句执行后立即释放。插入数据时，会加一个表级的AUTO-INC锁，然后给AUTO_INCREMENT修改的字段赋增量值。插入语句执行完成后，AUTO-INC锁就会被释放。那么，当一个事务持有AUTO-INC锁时，其他事务如果要向表中插入语句，就会被阻塞，从而保证AUTO_INCREMENT修改的字段的值在插入数据时不断递增。但是，当插入大量数据时，AUTO-INC锁会影响插入性能，因为在另一个事务中插入会被阻塞。因此，从MySQL5.1.22开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。同样在插入数据时，会在AUTO_INCREMENT修饰的字段上加一个轻量级锁，然后给该字段赋一个自增值来释放轻量级锁，而不用等到整个insert语句执行完才释放锁。InnoDB存储引擎提供了一个innodb_autoinc_lock_mode系统变量，用于控制是使用AUTO-INC锁还是轻量级锁。当innodb_autoinc_lock_mode=0时，使用AUTO-INC锁，语句执行完毕后释放锁；当innodb_autoinc_lock_mode=2时，使用轻量级锁，申请自增主键后释放锁，语句执行后不需要释放。innodb_autoinc_lock_mode=1时：普通insert语句，自增锁申请后立即释放；对于insert...select这种批量插入数据的语句，自增锁直到语句结束才会释放；当innodb_autoinc_lock_mode=2是性能最高的方法，但是当binlog的日志格式为statement并一起使用时，在“主从复制场景”下会出现数据不一致的情况。例如，考虑以下场景：会话A向表t中插入4行数据，然后创建一个具有相同结构的表t2，然后两个会话同时向表t2执行数据插入。如果innodb_autoinc_lock_mode=2，表示“申请自增主键后释放锁，不等待insert语句执行完成”。那么可能会出现这样一种情况：sessionB先插入两条记录，(1,1,1),(2,2,2)；然后，sessionA申请自增id得到id=3，插入(3,5,5);之后sessionB继续执行，插入两条记录（4,3,3），（5,4,4）。可以看出sessionB的insert语句产生了不连续的id。当“主库”发生这种情况时，binlog在面对t2表的更新时，只会记录这两个session的insert语句。如果binlog_format=statement，则记录的语句为原始语句。记录的顺序要么是会话A的插入语句在前，要么是会话B的插入语句在前。但是不管是哪一个，这个binlog都是拿到“从库”去执行的。这时，从库按“顺序”执行语句。只有执行完一条SQL语句后，才会执行下一条SQL语句。因此，像主库那样由两个session“同时”往t2表中插入数据的场景，在从库上是不会发生的。因此，sessionB的insert语句是在备库上执行的，生成结果中的id都是连续的。此时主从库出现数据不一致。要解决这个问题，应该将binlog的日志格式设置为row，这样binlog中就会记录主库分配的自增值。备库执行时，主库的自增值是多少，从库的自增值是多少。因此，当innodb_autoinc_lock_mode=2，binlog_format=row时，可以提高并发性，不会出现数据一致性问题。行级锁InnoDB引擎支持行级锁，而MyISAM引擎不支持行级锁。前面说了，普通的select语句是不会锁record的，因为它属于snapshotread。如果想在查询的时候给记录加行锁，可以使用下面两种方法。查询将锁定的语句称为锁定读取。//给读记录加共享锁select...lockinsharemode;//给读记录加排它锁select...forupdate;以上两条语句必须在一个事务中，因为当事务一旦提交，锁就会被释放，所以在使用这两条语句时，加上begin、starttransaction或者setautocommit=0。共享锁（S锁）满足read-读共享，读写互斥。排它锁（X锁）满足写与写互斥，读与写互斥。行级锁主要有三种：RecordLock，记录锁，即只锁定一条记录；GapLock，间隙锁，锁定一个范围，但不包括记录本身；Next-KeyLock：RecordLock+GapLock组合，锁定范围，并锁定记录本身。RecordLockRecordLock称为记录锁，锁定一条记录。而且，记录锁分为S锁和X锁：当一个事务给一条记录加S型记录锁时，其他事务也可以继续给该记录加S型记录锁（S型和S-锁是兼容的），但是记录不能加X型记录锁（S型和X型锁不兼容）；当一个事务给一条记录加X型记录锁时，其他事务不能给该记录加S型记录锁（S型锁）。类型与X锁不兼容），也不能将X类型记录锁添加到记录中（X类型与X锁不兼容）。例如，当一个事务执行如下语句时：mysql>begin;mysql>select*fromt_testwhereid=1forupdate;就是给t_test表锁中主键id为1的记录增加一条X类型的记录，让其他事务无法修改这条记录。当事务提交时，事务期间产生的所有锁都会被释放。GapLockGapLock全称为间隙锁，只存在于可重复读隔离级别。目的是解决可重复读隔离级别下的幻读现象。假设范围id为(3,5)的表存在间隙锁，则其他事务无法插入id=4的记录，有效防止幻读的发生。缝隙锁虽然有X型缝隙锁和S型缝隙锁之分，但是没有区别。间隙锁是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，不存在互斥关系，之所以提出，是因为间隙锁的目的是防止插入幻象记录。Next-KeyLockNext-KeyLock全称为Pro-KeyLock，是RecordLock+GapLock的组合，锁定一个范围，同时锁定记录本身。假设表中有一个范围id为(3,5]的next-key锁，那么其他事务既不能插入id=4的记录，也不能修改id=5的记录。因此，next-key锁不仅可以保护记录，还可以防止其他事务将新记录插入到被保护记录前面的空隙中。next-key锁包括间隙锁+记录锁。如果一个事务获取了X型next-key锁，则另一个事务在获取相同范围的X型next-key锁时会被阻塞。例如，如果一个事务持有范围为(1,10)的X类型的next-key锁，则另一个事务在获取相同范围的X类型的next-key锁时将被阻塞。虽然相同范围间隙锁在多个事务之间是相互兼容的，但是对于记录锁，我们需要考虑X型和S型的关系，X型记录锁和X型记录锁有冲突。插入意向锁一个事务是插入一条记录，需要判断插入位置是否已经被其他事务锁定（next-keylock也包括间隙锁），如果是，插入操作将被阻塞，直到拥有间隙锁的事务提交（release）Gaplockmoment），这期间会产生一个insertintentlock，表示一个事务要在某个范围内插入一条新记录，但是现在处于等待状态。比如假设事务A添加了一个范围id作为(3,5)Gap到表中锁。当事务A还没有提交时，事务B向表中插入一条id=4的新记录。此时会判断插入的位置已经被事务A加了间隙锁，所以事务B会生成一个插入意向锁，然后将锁状态设置为等待状态（PS：当MySQL加了一个lock，先生成锁结构，然后设置锁状态，如果锁状态是waiting状态，不代表事务成功获取锁，只有当锁状态正常时，才算成功表示事务已经成功获取到锁），此时事务B会被阻塞，直到事务A提交事务。insertedintentionallock的名字虽然有意向锁，但它并不是意向锁，它是一种特殊的间隙锁，属于行级锁。如果说间隙锁锁的是一个区间，那么“插入意向锁”锁的是一个点。所以，从这个角度来说，插入意向锁确实是一种特殊的间隙锁。插入意向锁和间隙锁还有一个很重要的区别就是，虽然“插入意向锁”也属于间隙锁，但是两个事务不能同时时间。gaplock，对方在gap区间有插入意向锁（当然如果插入意向锁不在gap锁区间也是可以的）。参考资料：《MySQL技术内幕：innodb》《MySQL实战45讲》《从根儿上理解MySQL》
