【MySQL】锁

敢飞就有天空

于 2022-06-06 17:15:51 发布

阅读量220

点赞数

分类专栏： MySQL笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46778443/article/details/124871837

版权

MySQL笔记专栏收录该内容

6 篇文章 1 订阅

订阅专栏

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，数据也是一种共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

全局锁：锁定数据库中的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续包含DML语句、DDL语句的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
在这里插入图片描述

# 加全局锁
flush tables with read lock;

# 数据备份
# Windows命令行
mysqldump -h39.105.19.217 -uroot –p123456 dataBaseName > scriptName.sql

# 释放锁
unlock tables;

特点：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上停止。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

# InnoDB存储引擎，不加锁的一致性数据备份
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率高，并发度低。应用在MyISAM、InnoDB等存储引擎中。对于表级锁，主要分为以下三类：

表锁
元数据锁（meta data lock, MDL）
意向锁

表锁

读锁（共享锁）
写锁（排他锁）

# 加读锁
lock tables tableName read;
# 加写锁
lock tables tableName write;
# 释放锁
unlock tables;

结论：读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

MySQL5.5引入元数据锁，在访问一张表的时候会自动上锁。元数据可以简单理解为一张表的表结构，某一张表涉及到未提交的事务时，是不能修改这张表的表结构的。元数据锁是为了避免DML与DDL冲突，保证读写的正确性。
在这里插入图片描述
当对一张表进行增、删、改、查操作时，加元数据锁的共享锁。当对表结构进行变更操作时，加元数据锁的排他锁。

# 查看数据库中的元数据锁的情况
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB存储引擎中引入了意向锁，使得表锁不用检查每行
数据是否加锁，使用意向锁来减少表锁的检查。
在这里插入图片描述

意向共享锁（IS）：由语句select … lock in share mode添加。与表锁的共享锁兼容，与表锁的排他锁互斥。
意向排他锁（IX）：由insert、update、delete、select…for update添加。与表锁的共享锁及排他锁都互斥。
意向锁之间不会互斥，一旦事务提交，意向共享锁、意向排他锁都会自动释放。

# 查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度小，发生锁冲突的概率低，并发度高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的
锁。对于行级锁，主要分为以下三类：

行锁：锁定单行记录的锁，防止其他事务对此行进行update和delete。在Read Commit、Repeatable Read隔离级别下都支持。
间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，从而产生幻读。在Repeatable Read隔离级别下支持。
临键锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙。在Repeatable Read隔离级别下支持。

行锁

InnoDB存储引擎实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他
锁。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。InnoDB存储引擎的行锁是针对于索引加的锁，不通过索引进行检索时，会对表中的所有记录加锁，此时就会升级为表锁。

客户端一获取的是id为1这行的共享锁，客户端二是可以获取id为3这行的排它锁的，因为不是同一行数据。而如果客户端二想获取id为1这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互斥。

当客户端一，执行update语句，会为id为1的记录加排他锁。客户端二，如果也执行update语句更新id为1的数据，也要为id为1的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。直到客户端一把事务提交，才会把这一行的行锁释放，此时客户端二解除阻塞。

客户端一开启事务，并执行update语句，更新name为Lily的数据，也就是id为19的记录。然后在客户端二中更新id为3的记录，却不能直接执行，会处于阻塞状态，为什么呢？因为此时客户端一根据name字段进行更新时name字段是没有索引的，如果没有索引，此时行锁会升级为表锁（因为行锁是对索引项加的锁，而name没有索引）。
在这里插入图片描述

# 查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁与临键锁

默认情况下，InnoDB存储引擎在Repeatable Read事务隔离级别运行，使用临键锁进行搜索和索引扫描，以防止幻读。

索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
索引上的等值查询（非唯一索引），向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。

InnoDB存储引擎采用B+树索引，叶子节点是有序的双向链表。假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁。
在这里插入图片描述

索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

查询的条件为id>=19，并添加共享锁。此时我们可以根据数据库表中现有的数据，将数据分为三个部分：[19]、(19,25]、(25,+∞]。所以数据库加锁情况为，19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临
键锁（正无穷及之前的间隙）。
在这里插入图片描述
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。