mysql锁详解脑图思维导图

U444884967

2023-09-13

浏览量: 7

数据库

数据开发

程序开发

mysql数据库锁理论与详解脑图

树图思维导图提供《mysql锁详解脑图》在线思维导图免费制作，点击“编辑”按钮，可对《mysql锁详解脑图》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：bbdfada8e4eeea8723ba2b769c03c60a

思维导图大纲

模式分类

乐观锁（业务实现）

适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，会大大降低系统性能。

一般使用数据版本（version）记录机制实现，在数据库表中增加以后个数据类型的"version"字段来实现。当读取数据时，将version字段的值一同读出，数据没更新一次，version值加一。当我们提交更新的时候，判断数据库表对应的当前版本信息与第一次取出来的version值对比，如果如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

悲观锁（select...for update）

适用于并发量不大、写入操作比较频繁、数据库一致性比较高的场景

例如 lock table t1 read,t2 write;命令，其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能在unlock tables之前访问其他表

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

按粒度

全局锁（全库逻辑备份 Flush tables with read lock )

使整个库处于只读状态，其他线程的数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句都会被阻塞

风险点：如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

解决办法：mysqldump使用参数--single-transaction，启动一个事务，确保拿到一致性试图。而由于MVCC的支持，这个过程中数据是可以正常更新的

表级锁（当前操作的整张表加锁，并发低，MyISAM:不支持事务，不会死锁；InnoDB:当不使用索引时全局扫描使用表锁）

表锁（lock tables ...read/write

元数据锁（meta data lock）

MDL 不需要显式使用，在访问一个表的时候会被自动加上，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

风险点：所有对表的增删改查操作都需要先申请MDL 读锁，而这时读锁没有释放，对表alter ，产生了mdl写锁，把表t锁住了，这时候就对表t完全不可读写了。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

解决办法：首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。这也是为什么需要在低峰期做ddl 变更。

页级锁（BDB引擎支持）

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁

行级锁（InnoDB支持）是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。

实现方法：行级锁不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking

按属性

共享锁（select ...lock in share mode）

读锁，简称S锁，当事务A对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当事务A上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免"不可重读"的问题的出现，适用于两张表存在关系时的写操作。

排它锁（select ...for update）

写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免"脏读"问题的产生。

按状态

（表锁，为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁并存）

当事务A有行锁是，MySQL会自动为改表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能

意向共享锁

意向排它锁

按算法

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。 select* from tables where id between 1 and 10 for update;

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

记录锁

记录锁是封锁记录，记录锁也叫行锁，例如： select *from tables where **`id`=**1 for update; 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

思维导图模板大纲

地热勘查技术思维脑图思维导图