一文讲透MySQL锁机制

标签：语句 事务 一文 排它 UPDATE InnoDB MySQL 讲透 数据

数据库是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。基本锁类型：锁包括行级锁和表级锁。

MySQL中有几种锁：

按锁粒度划分：

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

在InnoDB存储引擎中，以下SQL语句可能触发表级锁：

1. ALTER TABLE：当对表进行结构修改操作时，如添加、删除、修改列等，InnoDB会获取一个表级锁来确保修改的原子性和一致性。
2. TRUNCATE TABLE：当使用TRUNCATE TABLE语句清空表中的所有数据时，InnoDB会获取一个表级锁来防止其他事务对表进行并发操作。
3. DROP TABLE：当使用DROP TABLE语句删除整个表时，InnoDB会获取一个表级锁来防止其他事务对表进行并发操作。

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

在InnoDB存储引擎中，以下SQL语句可能触发行级锁：

1. SELECT ... FOR UPDATE：当使用SELECT语句并且在查询中包含FOR UPDATE子句时，InnoDB会获取被查询行的行级锁。这种行级锁会阻止其他事务对同一行进行写操作，确保查询结果的一致性。
2. UPDATE：当执行UPDATE语句时，InnoDB会根据具体的条件和索引情况来决定是否使用行级锁。通常情况下，UPDATE语句会获取被更新行的行级锁，以防止其他事务同时修改同一行。
3. DELETE：类似于UPDATE语句，执行DELETE语句时也会获取被删除行的行级锁，以确保删除操作的原子性和一致性。
4. INSERT：在某些情况下，执行INSERT语句时也可能触发行级锁。例如，如果插入的数据与已有数据存在冲突，InnoDB可能会获取相关行的行级锁来防止并发冲突。
5. REPLACE：REPLACE语句是INSERT和DELETE的组合操作，因此执行REPLACE语句时也会触发行级锁，以保证替换操作的原子性。

这些SQL语句会触发行级锁是为了保证数据的一致性和避免并发操作导致的问题。

页级锁（InnoDB中没有）：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度同样界于表锁和行锁之间。

按锁的级别划分：

共享锁（Shared Lock）：又称读锁，是读取操作创建的锁。允其他用户可以并发读取数据，但任何事务都不能对数据进行修改(获取数据上的排他锁)，直到已释放所有共享锁。

以下SQL语句可能会触发共享锁：

1. SELECT：当执行普通的SELECT语句时，InnoDB通常会获取共享锁。共享锁允许其他事务同时读取同一行，但不允许其他事务对同一行进行写操作。
2. SELECT ... LOCK IN SHARE MODE：当使用SELECT语句并在查询中包含LOCK IN SHARE MODE子句时，InnoDB会获取共享锁。这种共享锁的特点是，在事务结束之前，其他事务无法对被查询行进行写操作。

在读取数据时，为了保证数据的一致性和避免并发冲突会加上共享锁

排它锁（Exclusive Lock）：又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。

以下SQL语句可能会触发排它锁：

1. UPDATE：执行UPDATE语句时，InnoDB会获取排它锁来保护被更新的数据行。排它锁会阻止其他事务同时对同一数据行进行读取或写入操作。
2. DELETE：执行DELETE语句时，InnoDB会获取排它锁来保护被删除的数据行。排它锁会阻止其他事务同时对同一数据行进行读取或写入操作。
3. INSERT：执行INSERT语句时，如果插入的数据行与已存在的数据行存在冲突（例如唯一索引冲突），InnoDB会获取排它锁来保护相关的数据行。排它锁会阻止其他事务对同一数据行进行读取或写入操作。
4. REPLACE：执行REPLACE语句时，InnoDB会获取排它锁来保护被替换的数据行。排它锁会阻止其他事务对同一数据行进行读取或写入操作。

这些SQL在修改数据时，为了避免并发冲突和保证数据的完整性会上排它锁。

意向锁（Intention Lock）：MySQL的Innodb引擎中，支持多种锁级别，包括了行级锁和表级锁。当多个事务想要访问-个共享资源的时候，如果每个事务都直接请求获取锁，那么就可能会导致互相阻塞，甚至导致死锁。

假设事务A对表Table1中的一行加上了行级锁，这时候这行记录就只能读不能写了。事务B申请对Table1增加了表级锁，如果他申请成功了，那么他就可以修改表中的任意一行记录。这就发生了冲突。

那么，想要解决这个问题，就需要让事务B在对Table1增加表级锁的时候，先判断一下是不是有事务增加过行级锁。但是，事务B总不能遍历表中数据逐条判断是否有加锁吧?

所以，为了解决这个问题，MySQL引入了意向锁机制。所以，意向锁是数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁粒度(如行锁和表锁)之间的并发性问题。(相同锁粒度(如多个行级锁)之间的并发性问题通过行级互斥锁解决。)

当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。这样其他事务请求获取表锁时，就可以先基于这个意向锁来发现是否有人加过锁，并根据该锁的类型(意向共享锁/意向排他锁)来判断自己是否可以获取锁。这样可以在不阻塞其他事务的情况下，为当前事务锁定资源。

意向锁有两种类型:意向共享锁和意向排他锁。

1.意向共享锁: 表示事务打算在资源上设置共享锁(读锁)这通常用于表示事务计划读取资源，并不希望在读取时有其他事务设置排它锁。

2.意向排它锁: 表示事务打算在资源上设置排它锁(写锁)。这表示事务计划修改资源，并不希望有其他事务同时设置共享或排它锁。

意向锁是一个表级锁，并且他会在触发意向锁的事务提交或者回滚后释放。

以下SQL语句可能会触发意向锁：

1. INSERT：当执行INSERT语句时，InnoDB会获取意向排它锁（Intention Exclusive Lock）在表级别上。这表示事务打算在表中插入数据，并且可能会获取排它锁来保护插入的数据行。
2. DELETE：当执行DELETE语句时，InnoDB会获取意向排它锁（Intention Exclusive Lock）在表级别上。这表示事务打算在表中删除数据，并且可能会获取排它锁来保护删除的数据行。
3. UPDATE：当执行UPDATE语句时，InnoDB会获取意向共享锁（Intention Shared Lock）在表级别上。这表示事务打算在表中更新数据，并且可能会获取共享锁来保护更新的数据行。

普通的SELECT语句通常不会触发意向锁。意向锁主要用于协调事务对表级锁和行级锁的获取，以避免死锁和提高并发性能。普通的SELECT语句通常会获取共享锁（Shared Lock）或不获取锁，以允许多个事务同时读取相同的数据，不会阻止其他事务的读取操作。

如果一个事务执行SELECT语句，并且没有显式地指定锁类型（如FOR UPDATE），通常情况下不会触发意向锁。在这种情况下，InnoDB会根据事务隔离级别和并发控制机制来确定是否需要获取共享锁，以确保读取的数据在事务中是一致的。

按使用方式划分：

悲观锁：当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观锁。

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会;另外，还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

以下SQL语句通常会触发悲观锁：

1. SELECT ... FOR UPDATE：当执行SELECT ... FOR UPDATE语句时，InnoDB会为选定的行获取排它锁（Exclusive Lock），确保其他事务不能读取或修改这些行，直到当前事务释放锁为止。
2. UPDATE：执行UPDATE语句时，InnoDB会根据事务隔离级别的设置，在更新数据行时获取排它锁，以确保其他事务无法同时修改相同的数据行。
3. DELETE：执行DELETE语句时，InnoDB会获取排它锁来删除数据行，防止其他事务同时访问或修改这些数据。
4. INSERT：在执行INSERT语句时，InnoDB会获取排它锁来插入新的数据行，以确保数据的完整性和一致性。

这些操作通常需要确保数据的完整性和一致性，因此会触发悲观锁机制，阻止其他事务对相同数据行进行并发修改或删除。

乐观锁：假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景：

1、乐观锁并未真正加锁，效率高。适用于读操作频繁，写操作相对较少的场景。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。

2、悲观锁依赖数据库锁，效率低。更新失败的概率比较低。适用于写操作较为频繁，且并发写入的概率较高的场景。

以下是一些在InnoDB中可能触发乐观锁机制的SQL操作：

1. UPDATE with versioning：在使用乐观锁机制时，UPDATE操作可能会涉及版本控制。通常，更新操作会检查数据的版本号或时间戳，以确保在更新之前数据没有被其他事务修改。如果检测到数据已经发生变化，事务可能会回滚或重新尝试更新操作。
2. SELECT with versioning：类似于UPDATE操作，SELECT语句在乐观锁机制下可能会涉及版本控制。事务在读取数据时会记录数据的版本信息，以便在后续操作中检查数据是否被其他事务修改。
3. INSERT with versioning：在一些情况下，INSERT操作也可能涉及乐观锁机制，尤其是在需要保证数据唯一性或一致性的情况下。事务可能会检查插入的数据是否与已有数据冲突，以确保插入操作的成功。

按锁的对象划分：

数据库的行级锁根据锁的粒度不同，可以叫做不同的名字，分为记录锁，间隙锁，临界锁。这三种锁理解起来比较晦涩，用生活中的例子来理解一下：

你和你的朋友都来图书馆借书。图书馆中有很多书架，每个书架上都有不同的书籍。现在，你和你的朋友分别想要借阅某本书，但只有一本剩下最后一本了。记录锁、间隙锁和临界锁的作用可以用以下方式来解释：

1. 记录锁（Record Lock）：记录锁就像你手中的借书卡，当你决定借阅某本书时，你会将该书的借书卡锁定，表示该书已经被你借走了。这样其他人就无法同时借阅同一本书。只有当你归还书籍并释放锁后，其他人才能借阅该书。记录锁用于保护数据库中的单个记录，确保同时只有一个事务可以修改或访问该记录（锁住的是书，即数据库中的记录）。
2. 间隙锁（Gap Lock）：间隙锁就像你在书架上放置一个标记，表示你将要借阅该书架上的书籍。当你放置标记时，其他人就无法借阅该书架上的任何书籍，即使书架上还有其他书籍也无法借阅。这样可以防止其他人在你借阅之前将其他书籍放在同一个书架上。间隙锁用于保护数据范围，防止其他事务在范围内插入新的记录。
3. 临界锁（Next-Key Lock）：临界锁结合了记录锁和间隙锁的概念。它就像你在借阅时同时使用借书卡和标记，以确保你能够借阅某本书并保护它所在的书架。临界锁既保护了当前记录，也保护了该记录之前和之后的间隙，防止其他事务在范围内插入新的记录或修改已有记录。

例如，假设你和你的朋友都想借阅图书馆中编号为101的书籍，同时你们也想保证没有其他人在你们借阅之前插入新的书籍。在这种情况下，你会获取编号为101的记录锁，而间隙锁将会锁定编号为100到102之间的间隙，以防止其他人在这个范围内插入新的书籍。这样，你和你的朋友可以安全地借阅编号为101的书籍，而不会出现冲突或数据不一致的情况。

通过上述例子，便可以引出三种锁的定义啦~

Record Lock，翻译成记录锁，是加在索引记录上的锁。例如，SELECT c1 FROM t WHEREC1=18 FOr UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1值为10的行。

以下是在InnoDB中可能会触发记录锁的SQL语句：

1. SELECT ... FOR UPDATE：执行SELECT ... FOR UPDATE语句时，会为选定的行获取排它锁（Exclusive Lock），防止其他事务同时修改这些行。
2. UPDATE：执行UPDATE语句时，InnoDB会根据事务隔离级别的设置为受影响的数据行获取排它锁，以确保其他事务无法同时修改这些数据行。
3. DELETE：执行DELETE语句时，InnoDB会获取排它锁来删除数据行，防止其他事务同时访问或修改这些数据。
4. INSERT：在执行INSERT语句时，InnoDB会为插入的新数据行获取排它锁，以确保数据的完整性和一致性。

这些操作通常需要保护单个数据行，以确保数据的一致性和避免并发修改问题。

Gap Lock，翻译成间隙锁，他指的是在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

那么，这里所谓的Gap(间隙)又怎么理解呢?

Gap指的是InnoDB的索引数据结构中可以插入新值的位置。

当你用语句SELECT..FOR UPDATE锁定一组行时。InnoDB可以创建锁，应用于索引中的实际值以及他们之间的间隙。

例如，如果选择所有大于10的值进行更新，间隙锁将阻止另一个事务插入大于10的新值。

既然是锁，那么就可能会影响到数据库的并发性，所以，间隙锁只有在Repeatable Reads这种隔离级别中才会起作用。

在Repeatable Reads这种隔离下，对于锁定的读操作(select.. for update、 lock inshare mode)、update操作、delete操作时，会进行如下的加锁:

对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙

对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gaplock或next-keylock来阻塞其他事务插入范围覆盖的间隙。

以下是在InnoDB中可能会触发间隙锁的SQL语句：

1. SELECT ... FOR UPDATE：当使用SELECT ... FOR UPDATE语句时，InnoDB会在扫描结果集时为扫描到的行和它们之间的间隙（gap）设置间隙锁，以防止其他事务在这个范围内插入新的数据。
2. UPDATE：执行UPDATE语句时，根据事务隔离级别的设置，InnoDB可能会在更新数据行时为相关的间隙设置间隙锁，以防止其他事务在这个范围内插入新的数据。
3. INSERT：在执行INSERT语句时，如果插入的数据不在已有数据的范围内，InnoDB可能会为插入数据的位置的间隙设置间隙锁，以防止其他事务在这个范围内插入新的数据。
4. DELETE：执行DELETE语句时，InnoDB可能会为删除的数据行所在的间隙设置间隙锁，以防止其他事务在这个范围内插入新的数据。

间隙锁的作用是确保范围内的数据一致性，防止其他事务在这个范围内插入新的数据，从而避免幻读（Phantom Read）等并发问题。

Next-Key Lock临界锁是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:

对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。

所以，Next-Key 的锁的范围都是左开右闭的，

Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。

标签：语句,事务,一文,排它,UPDATE,InnoDB,MySQL,讲透,数据
From： https://blog.csdn.net/weixin_43476946/article/details/139280927