MySQL锁

ccc_0
244 阅读23分钟

目录结构如下：

  • 锁分类
  • 表锁
  • 行锁
  • 一级二级三级封锁协议
  • 隔离级别
  • MVCC
  • 乐观锁和悲观锁
  • 间隙锁(Gap Locks)
  • 临键锁（Next-Key Locks）
  • 二段锁

锁分类

image.png

数据库会隐式帮我们加上：

  • 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁。
  • MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁。
  • MyISAM在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。

表锁

MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率越高，并发度最低，MyISAM和InnoDB引擎都支持表级锁。

触发条件：
InnoDB不通过索引条件检索数据将使用表锁。(否则，使用行级锁)

表读锁和写锁：

  • 表读锁：读读不阻塞，读写阻塞
    如果某线程对表加了读锁，无论本线程还是其他线程写操作都会被阻塞，直到锁释放会自动执行。(但都可以读)
  • 表写锁：写写阻塞，写读阻塞 本线程加了写锁，其他线程的读操作和写操作都会被堵塞。(但是本线程可读可写)。

读锁和写锁是互斥的，读写操作是串行。 如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！

缺点：
不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

行锁

Mysql中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。区别于MyISAM存储引擎，InnoDB的特性为支持行锁和事务。

虽然使用行级锁具有粒度小、并发高等特点，但是表级锁在某些场景下也是必需的。

  • 事务更新大表中的大部分数据时直接使用表级锁效率更高，避免频繁加行级锁。
  • 事务比较复杂，使用行级锁很可能会导致死锁导致回滚。

触发条件：
InnoDB只有通过索引条件检索数据才使用行级锁，也就是说，InnoDB的行锁是基于索引的！ eg：select * from table where id=1;

共享锁和排他锁： InnoDB实现了这两种类型行锁

  • 共享锁(S锁)(读锁)：
    对数据对象加了共享锁，就可进行读取操作，但是不能进行更新操作。
    加锁期间其他事务也能读取(加共享锁)，但是不能更新(加排他锁)。

在正常的select语句中，是不会有加锁的，例如下边这条sql： select * from t_user_message;
这条sql在innodb中，默认是不会锁表，也不会锁行记录。如果你希望加上一把共享锁，那么可以尝试以下的这种写法：使用 lock in share mode 关键字。 select * from t_user_message lock in share mode;

  • 排他锁(X锁)(写锁)：
    对数据对象加了排他锁，就可进行读取和更新操作。
    加锁期间其他事务不能读也不能写。(不能加任何锁)

在Innodb存储引擎中，常见的update，insert，delete这些sql都会默认加入上排他锁，而我们的select语句如果没有加入特殊关键字（下边会讲是什么样的特殊关键字）  ，是不会加入排他锁的。
如果select语句希望加入排它锁，那么可以尝试以下方式：使用 for update 关键字。
eg: select * from t_user_message for update;

image.png

1694786914329.png

image.png

间隙锁（Gap Locks）——行级锁 ——解决幻读问题

基于非唯一索引列，它锁定一段范围内的索引记录 使用间隙锁锁住的是一个区间，双开区间，而不仅仅是这个区间中的每一条数据。 执行以下代码时会该事务会获得间隙锁：

    SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

临键锁（Next-Key Locks）——行级锁——解决幻读问题

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。 所以，next-key lock 即能保护该记录，又能阻止其他事务将新记录插入到被保护记录前面的间隙中。

InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。

-- 或根据非唯一索引列 锁住某条记录 SELECT * FROM table WHERE age = 24 FOR UPDATE;

1690813542(1).png

插入意向锁

1694787278924.png

乐观锁和悲观锁

乐观锁和悲观锁是两种思想，用于解决并发场景下的数据竞争问题。它们的使用是非常广泛的，不局限于某种编程语言或数据库。乐观锁对应于生活中乐观的人总是想着事情往好的方向发展，悲观锁对应于生活中悲观的人总是想着事情往坏的方向发展。这两种人各有优缺点，不能不以场景而定说一种人好于另外一种人。

  • 悲观锁：先成功获取锁，再进行业务操作
    总是会假设当前情况是最坏的情况，在每次去拿数据的时候，都会认为数据会被别人改变，因此在每次进行拿数据操作的时候都会加锁。因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
    在数据库中也经常用到这种锁机制，如行锁，表锁，读写锁等，都是在操作之前先上锁，保证共享资源只能给一个操作（一个线程）使用。
    缺点：

      1. 由于悲观锁的频繁加锁，因此导致了一些问题的出现：比如在多线程竞争下，频繁加锁、释放锁导致频繁的上下文切换和调度延时，一个线程持有锁会导致其他线程进入阻塞状态，从而引起性能问题。
      1. MySQL还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。

  • 乐观锁”：先业务操作，进行完业务操作需要实际更新数据的最后一步再去拿一下锁
    总是假设最好的情况，每次去读取数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，那么当前正在提交的事务会进行回滚。可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

  • 使用场景：
    当竞争不激烈 (出现并发冲突的概率小)时，乐观锁更有优势，因为悲观锁会锁住代码块或数据，其他线程无法同时访问，影响并发，而且加锁和释放锁都需要消耗额外的资源。

    当竞争激烈(出现并发冲突的概率大)时，悲观锁更有优势，因为乐观锁在执行更新时频繁失败，需要不断重试，浪费CPU资源。

记录锁（Record Locks）——行级锁

记录锁就是为某行记录加锁，它封锁该行的索引记录。执行以下代码时会加上记录锁。

-- id 列为主键列或唯一索引列 
SELECT * FROM table WHERE id = 1 FOR UPDATE;

注意点：

  1. id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
  2. 查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

一级二级三级封锁协议

数据库并发的几大类问题：

  • 丢失修改：两个事务同时读入同一数据并修改，有一个事务的提交结果会被另一个提交结果破环，导致丢失
  • 不可重复读：一个读一个更新
  • 幻读：一个读一个插入删除
  • 读脏数据

解决并发问题的技术：封锁 在运用X（排他锁、写锁）锁和S（共享锁、读锁）锁对数据对象加锁时，还需要约定一些规则 ，例如何时申请X锁或S锁、持锁时间、何时释放等。称这些规则为封锁协议（Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。不同的封锁协议，在不同的程度上为并发操作的正确调度提供一定的保证。

  • 一级封锁协议 约束写行为：事务T在修改数据R之前必须先对其加X锁，直到事务结束释放X锁。

    缺点：不能保证可重复读和不读“脏”数据和幻读。（优点：防止丢失修改） 实际场景：

  • 二级封锁协议 约束写行为：事务T在修改数据R之前必须先对其加X锁，直到事务结束释放X锁。
    约束读行为：事务T在读取数据R之前必须先对其加S锁，读完后方可释放S锁。

    缺点：不能保证可重复读。（优点：可防止丢失修改和读脏数据）

  • 三级封锁协议 约束写行为：事务T在修改数据R之前必须先对其加X锁，直到事务结束释放X锁。
    约束读行为：事务T在读取数据R之前必须先对其加S锁，直到事务结束释放S锁。

    缺点：（优点：防止不读脏数据外，防不可重复读，）

封锁可能会引起活锁和死锁：

  • 活锁：
    • 活锁的解决方法：避免活锁的方法就是先来先服务的策略。当多个事务请求对同一数据对象封锁时，封锁子系统按照请求的先后对事务排队。数据对象上的锁一旦释放就批准申请队列中的第一个事务获得锁。
  • 死锁：见下面

隔离级别

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别
事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节

事务的隔离级别有4种： image.png Mysql有四种事务隔离级别，这四种隔离级别代表当存在多个事务并发冲突时，可能出现的脏读、不可重复读、幻读的问题。

  1. 隔离级别越高（读未提交->读已提交->可重复读->串行化），越能保证数据的完整性和一致性，对并发性能的影响也会越大。
  2. Mysql默认的级别是可重复读，优先考虑把数据库系统的隔离级别设为读已提交。

事务有三个操作： 开启事务、回滚、提交 MySQL数据库默认是自动提交的，一条DML（增删改语句）会自动提交一次事务； MySQL默认的事务隔离级别为repeatable-read

事务并发时会导致的问题

1698842408954.png 无论是脏读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。

  • 脏读：一个事务读取到另外一个事务未提交的数据
    在事务A两次查询的间隙，事务B修改了该行数据但未提交。导致了A两次读取不一致。 之后事务B回滚，导致A第二次查询结果是数据库中不存在的数据，是错误的。

    • 出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。 避免脏读做法：
    1. 升级隔离级别为读已提交， 2. 行锁(把互斥锁的释放的位置调整到事务提交之后）

  • 不可重复读： 一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改
    在事务A两次查询的间隙，事务B修改了该行数据并提交了。导致A两次读取不一致。
    事务A读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况叫不可重复读。

    避免不可重复读办法：
    1.升级隔离级别为可重复读 2. MVCC实现读一致性 3. 事务A获取行锁

  • 幻读：
    在事务A两次范围查询间隙，事务B插入了一行数据并提交了。导致A两次读取不一致。
    在事务A再去查询的时候，它发现多了一行数据。

    避免幻读办法：1.升级隔离级别为串行 2. MVCC实现读一致性 3. 事务A获取GAP间隙锁。

MVCC(多并发版本控制)

image.png 在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)

  • MVCC实现的读写不阻塞正如其名：多版本并发控制--->通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot) ，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

快照有两个级别：

  • 语句级
    • 针对于Read committed隔离级别
  • 事务级别
    • 针对于Repeatable read隔离级别

Read committed是语句级别的快照！每次读取的都是当前最新的版本！ Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。

实现：

  1. 版本号或时间戳：每个数据行都会关联一个版本号或时间戳，用于标识该数据行的不同版本。通常，读取事务的开始时间或提交时间被用来作为版本标识。
  2. 快照事务：当一个事务开始时，它会创建一个快照，这个快照包含了事务开始时的数据库状态。这个快照中包含了事务开始时的版本号或时间戳。
  3. 读取一致性：当事务执行读取操作时，它只会看到在其快照创建之前提交的事务所做的更改。这确保了读取操作的一致性，不会受到正在进行的其他事务的影响。
  4. 写入冲突：如果一个事务要修改某个数据行，但该数据行的版本号与事务的快照不匹配（即已经被其他事务修改），则会发生写入冲突。此时，数据库系统需要根据事务的隔离级别（如Read Committed、Serializable等）来处理冲突，可以选择阻塞、回滚或使用其他策略来解决。
  5. 回滚段或日志：MVCC还需要一种机制来记录事务的操作，以便在需要回滚事务或撤销更改时使用。通常使用回滚段或事务日志来记录事务的操作。

死锁

其实只要我们的select类型的sql中进行显示加锁，就有可能会有死锁情况发生，所以建议大家使用的时候谨慎。 概念： 死锁是指两个或两个以上的线程（或进程）在运行过程中，因为资源竞争而造成相互等待的现象，若无外力作用则不会解除等待状态，它们之间的执行都将无法继续下去。 A线程持有B锁，然后想要访问A锁，此时B线程持有A锁，想要访问B锁，这种情况下就容易出现死锁。

四大条件：

  • 互斥条件；至少有一个资源互斥使用
  • 持有并等待条件；进程必须持有资源再去申请
  • 不可剥夺条件；资源只能进程自愿放弃
  • 环路等待条件；在资源分配图中有一个环路

死锁产生原因： 场景1：线程A和线程B对同样的一行通过黑色select语句持有了共享锁，然后他们都想 image.png

解决：1.不用锁，提升事务隔离级别为读已提交
2.去掉使用lock in share mode，使用乐观锁
3. .引入分布式锁

死锁处理： 如果一个事务的等待时间超过了默认的时间，就认为是产生了死锁。一旦检测到系统中存在死锁就要设法解除。通常的解决方法是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，恢复其所执行的数据修改操作，使得其他事务得以运行下去。 对待死锁常见的两种策略：

  • 通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时；
  • 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

死锁检测： 设置 innodbdeadlockdetect 设置为 on 可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。

  • 使用命令 show engine innodb status 查看最近的一次死锁。
  • InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

死锁预防：

  • 一次封锁法 一次封锁法要求事务必须一次将所有要使用的数据全部加锁，否则不能继续执行。例如上图中的事务T1将数据R1和R2一次加锁，T1就能执行下去，而T2等待。T1执行完成之后释放R1，R2上的锁，T2继续执行。这样就不会产生死锁。
    一次封锁法虽然能防止死锁的发生，但是缺点却很明显。一次性将以后要用到的数据加锁，势必扩大了封锁的范围，从而降低了系统的并发度。

常见的死锁案例：

  • 含唯一索引的表中高并发插入导致的死锁：insert into tab(xx,xx) on duplicate key update xx=‘xx’。

    insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作， 然后对该记录加上X(排他锁)，最后进行update写入。如果有两个事务并发的执行同样的语句，
    image.png 1694786481682.png

  • primarykey并发插入冲突，事务回滚导致的死锁问题：insert into

1694787612202.png 1694787497883.png
> insert的加锁过程：插入一个插入意向锁(表锁)表明插入的意向，对插入成功的行设置排他锁，即插入到同一索引间隙中的多个事务如果不在间隙中的同一位置插入，则无需彼此等待。(假设存在值为4和7的索引记录。尝试插入值为5和6的单独事务在获得插入行的排他锁之前，都会使用插入意图锁锁定4和7之间的间隙，但不会彼此阻止，因为这些行不冲突。)

分析流程：insert会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁（简称I锁吧），并发的事务可以对同一个gap加I锁。如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作是会发生死锁的。

image.png

见：[MySQL]-死锁案例-唯一索引上的并发插入_mysql插入导致死锁_森格的博的博客-CSDN博客 mysql insert锁机制 - 奕锋博客 - 博客园 (cnblogs.com)

死锁避免解决：

防止死锁发生避免方式:
1.从业务角度预防，例如通过select for update对新增订单做幂等性校验操作在业务量很大的时候会出现死锁，可以通过设置将订单号设置唯一索引列避免幂等校验。例如，在选择使用insert into on duplicate key update解决唯一索引冲突时的策略时，该语句在高并发下会有可能导致死锁。结合业务考虑，可以在并发插入数据库之前提前在分布式环境中进行数据去重。

  1. 从程序逻辑角度预防，事物之间尽量按照相同的顺序处理多个资源，避免出现循环等待。

    比如操作表A和表B时，总是按照先A后B的顺序处理，避免了出现用户1正在访问A表想要访问B表，用户2正在访问B表想要访问A表的死锁情形。

  2. 从事务角度预防，将大事务拆小，尽量不要在一个事务中实现过于复杂和耗时的操作，事务越大，占用数据库资源时间越长，引发死锁可能性越大。实际业务场景中可以不需要事务，所以直接取消事务包装即可，采用insert ON DUPLICATE KEY UPDATE的方式。

  3. 一次封锁法预防，在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

  4. 从索引角度预防，合理简历和使用索引，避免出现不走索引或索引失效导致全表扫描的情形，此时每一行记录都会添加上锁，死锁的概率大大增加。

  5. 从并发角度来说，死锁是由于并发访问数据库资源造成的，可以通过限制并发数，以及将大规模的数据库操作放在并发度较小的时候进行。比如凌晨用户量少的时候。

  6. 数据分区预防，将大量数据的表拆分为若干分区，分别保存在不同的物理存储介质中，可以提高查询效率，减少查询时需要锁定的数据量，减少锁竞争和提高查询性能。

  7. 调整事务隔离级别为read commit，在业务允许的情况下，RC级别不会产生gap lock，降低锁竞争概率

当有死锁发生时：可以通过破坏死锁的必要条件来避免死锁。
在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：
1699777285874.png

死锁排查过程：

  • 查看死锁日志，注意这里并不会包含整个事务的相关sql，仅仅会把等待锁的SQL打印出来，死锁日志内容含义参考 ：blog.itpub.net/22664653/vi…
  • 根据服务异常log定位到具体事务执行代码，找出该事务相关的sql
  • 根据积累的经验知识分析加锁、锁等待情况，找出死锁原因

死循环排查过程

  1. 审查代码，重点关注循环结构，检查是否有条件永远满足
  2. 日志：打印循环入口和出口，以及每次迭代值，看看程序在哪里陷入了重复执行的状态
  3. 断点调试
  4. 资源监控：监控程序的资源使用情况，例如CPU和内存。如果程序陷入死循环，CPU使用率可能会非常高。使用系统监控工具（例如top或Task Manager）观察程序的资源占用情况。

非数据库死锁检测

参考

数据库两大神器【索引和锁】 - 掘金 (juejin.cn)

「MySQL高级篇」MySQL全局锁、表级锁、行级锁 - 掘金 (juejin.cn)

数据库锁机制 - 掘金 (juejin.cn)

MySQL数据库——事务的操作（开启、回滚、提交）、特征、隔离级别基础总结 - 腾讯云开发者社区-腾讯云 (tencent.com)

juejin.cn/post/720023…

Mysql-详解脏读、不可重复读、幻读 - 掘金 (juejin.cn)

面试官：可以谈谈乐观锁和悲观锁吗 - 掘金 (juejin.cn)

(十四)深入并发之线程、进程、纤程、协程、管程与死锁、活锁、锁饥饿详解 - 掘金 (juejin.cn)

(十)全解MySQL之死锁问题分析、事务隔离与锁机制的底层原理剖析 - 掘金 (juejin.cn)

MySQL 死锁了，怎么办？ | 小林coding (xiaolincoding.com)

高并发场景下的MySQL几类死锁事故案例分析_bigint_事务_sql (sohu.com)

(30条消息) MySQL 中锁的面试题总结_读数据对数据加s锁,直到事务结束才能释放锁。_欢乐大象的博客-CSDN博客

一次并发插入死锁带来的“教训”，我才清楚这些MySQL锁知识 - 知乎 (zhihu.com)

avatar
文章
阅读
粉丝