网站建设图标图片,湖北建设网,成都旅游攻略自由行攻略地图,网络运营主要做什么工作文章目录 1. 锁概述2. 全局锁2.1 介绍2.2 数据备份2.3 使用全局锁造成的问题 3. 表级锁3.1 表锁3.1.1 语法3.1.2 读锁3.1.3 写锁3.1.4 读锁和写锁的区别 3.2 元数据锁#xff08;Meta Data Lock#xff0c;MDL#xff09;3.3 意向锁3.3.1 案例引入3.3.2 意向锁的分类 4. 行级… 文章目录 1. 锁概述2. 全局锁2.1 介绍2.2 数据备份2.3 使用全局锁造成的问题 3. 表级锁3.1 表锁3.1.1 语法3.1.2 读锁3.1.3 写锁3.1.4 读锁和写锁的区别 3.2 元数据锁Meta Data LockMDL3.3 意向锁3.3.1 案例引入3.3.2 意向锁的分类 4. 行级锁4.1 介绍4.2 行锁4.2.1 测试行锁之间的互斥性4.2.2 测试行锁升级为表锁的情况 4.3 间隙锁临键锁 1. 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中除了传统的共享计算资源CPU、RAM、IO以外数据也是需要争抢的共享资源
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素锁对数据库尤其重要 MySQL 中的锁按照锁的粒度分分为以下三类
全局锁锁定数据库中的所有表表级锁每次操作锁住整张表行级锁每次操作锁住对应的行数据
2. 全局锁
2.1 介绍
全局锁就是对整个数据库实例加锁加上全局锁后整个数据库将处于只读状态后续的 DML 语句、DDL 语句以及更新操作的事务提交语句都将被阻塞
全局锁的典型的使用场景是做全库的数据备份需要对所有的表进行锁定从而获取一致性视图保证数据的一致性和完整性
2.2 数据备份
接下来我们演示一下数据库的数据备份操作
第一步获取全局锁
flush tables with read lock;第二步使用 mysqldump 工具做数据库的备份注意是在 Linux 终端中运行
mysqldump -u root -p123456 /tmp/blog.sql运行指令后会有一个警告因为我们将密码显式地展现出来了
mysqldump: [Warning] Using a password on the command line interface can be insecure.
第三步释放全局锁
unlock tables;2.3 使用全局锁造成的问题
数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停止如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟现象的发生 在 InnoDB 引擎中我们可以在备份时加上参数 --single-transaction 参数来完成不加全局锁的一致性数据备份
mysqldump --single-transaction -u root -p123456 /tmp/blog.sql3. 表级锁
表级锁每次操作都会锁住整张表锁定粒度大发生锁冲突的概率最高并发度最低应用在 MyISAM、InnoDB、BDB 等存储引擎中
对于表级锁主要分为以下三类:
表锁元数据锁Meta Data LockMDL)意向锁
3.1 表锁
对于表锁分为两类 表共享读锁Read Lock读锁 表独占写锁Write Lock写锁
3.1.1 语法
使用表锁的语法
加锁lock table 表01 read 表02 write;释放锁unlock tables;与 MySQL 服务器断开连接也会释放锁
3.1.2 读锁
读锁的特点
阻塞其他写操作如果一个事务已经获得了某个表的读锁其他任何试图对该表进行写操作的事务将会被阻塞直到持有读锁的事务释放锁不会阻塞其他读操作表锁的读锁不会阻塞其他事务对同一表的读操作。这意味着多个事务可以同时获取表锁的读锁并且它们之间不会相互阻塞 读锁的示例图 如果在开启读锁的事务中执行 DML 或 DDL 语句会报错
lock tables tb_user read;unlock tables;3.1.3 写锁
写锁的特点
排他性写锁是排他的这意味着在同一时刻只有一个事务可以获得给定资源上的写锁。其他任何事务或会话都不能同时对该资源进行写操作阻塞其他写操作如果一个事务已经获得了某个资源上的写锁其他任何试图对该资源进行写操作的事务将会被阻塞直到持有写锁的事务释放锁阻塞读操作同样如果一个事务获得了写锁其他试图读取该资源的操作在某些事务隔离级别下也会被阻塞直到写锁被释放 写锁的示例图 3.1.4 读锁和写锁的区别
读锁不会阻塞其他客户端的读但是会阻塞其它客户端的写
写锁既会阻塞其他客户端的读也会阻塞其他客户端的写
3.2 元数据锁Meta Data LockMDL
元数据锁的加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上
元数据锁的主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作为了避免 DML 语句和 DDL 语句之间的冲突保证读写操作的正确性
更简单的理解方式就是在对表进行增删查改操作的时候不能更改表的结构
那什么是元数据呢大家可以简单地理解为表结构 MySQL 5.5 引入了元数据锁当对一张表进行增删改查的时候加元数据读锁共享锁当对表结构进行变更操作的时候加元数据写锁排它锁 如何查看元数据锁呢可以运行以下指令
select object_type, object_schema, object_name, lock_type, lock_duration, lock_status
from performance_schema.metadata_locks;如果出现以下错误说明当前用户没有访问 performance_schema 数据库的权限
SELECT command denied to user ‘wuyanzu’‘127.0.0.1’ for table ‘metadata_locks’
3.3 意向锁
3.3.1 案例引入
我们先来看以下场景
现在有一张 employee 表线程 A 想更新 id 为 3 的记录在默认的 MySQL 隔离级别下执行 update 语句而且是根据主键更新会自动对 id 为 3 的记录加上行锁
此时又来了一个线程 B线程 B 想要获取表锁大家想一下线程 B 能直接拿到表锁吗实际上线程 B 不能直接拿到表锁因为表锁与行锁之间会有冲突
线程 B 在获取表锁的时候需要先根据 employee 表中是否有行锁以及行锁的类型来判断能不能加表锁要判断是否有行锁需要一条一条数据扫描下来看看有没有行锁以及有行锁的话是什么类型的行锁性能比较低 为了避免执行 DML 语句时加的行锁与加的表锁冲突InnoDB 引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少取得表锁之前的检查
我们来看一下有了意向锁之后加锁过程是怎样的
线程 A 在执行 update 语句的时候会先针对这一行加上行锁接着再为表加上一个意向锁线程 B 要想获取表锁的话只需要检查表中意向锁的情况通过意向锁的情况来判定是否能够获取表锁如果当前表的意向锁是与线程 B 要获取的表锁是兼容的话直接获取表锁如果当前表的意向锁是与线程 B 要获取的表锁不兼容线程 B 就会一直处于阻塞状态直到线程 A 释放行锁和意向锁 大家会发现当引入了意向锁之后线程 B 想要获取表锁的话就不用逐行扫描数据可以直接根据表的意向锁的情况来判断能否获取表锁
3.3.2 意向锁的分类
意向锁分为两种
1.意向共享锁ISIntention Shared Lock由语句 select … lock in share mode 添加
2.意向排他锁IXIntention Exclusive Lock由 insert、update、delete、select … for update 添加 意向锁与表锁的互斥情况
意向共享锁与表锁的共享锁兼容与表锁的排它锁互斥意向排他锁与表锁的共享锁及表锁的排它锁都互斥
但意向锁之间不会互斥怎么理解呢
理解意向锁之间不互斥的关键在于了解不同类型的意向锁以及它们的作用
意向共享锁 (IS): 当事务想要获取一个表上的共享锁读取数据时它会先申请一个意向共享锁。这个锁表示事务有意向在表的某一行或某些行上获取共享锁意向排他锁 (IX): 当事务想要获取一个表上的排他锁写入数据时它会先申请一个意向排他锁。这个锁表示事务有意向在表的某一行或某些行上获取排他锁
意向锁的特点
非独占性多个事务可以同时持有同一表上的 IS 锁因为这些事务都只是表明了读取某些行的意图并没有实际锁定任何特定的行兼容性IS 锁和 IX 锁之间是不互斥的。这意味着如果一个事务已经持有了 IS 锁其他事务仍然可以获取 IX 锁反之亦然辅助作用意向锁本身并不直接锁定任何行而是作为辅助锁来帮助系统决定是否授予更具体的锁类型如 S 锁或 X 锁
例子说明
假设事务 T1 获取了一个表上的 IS 锁这表示 T1 有意向读取该表的某些行另一个事务 T2 也可以获取该表上的 IX 锁表示 T2 有意向更新该表的某些行在这种情况下T1 和 T2 都可以在不冲突的情况下进行操作只要他们不尝试在同一行上执行互斥的操作
总结
意向锁之间不互斥是因为它们仅仅表达了事务对于数据操作的“意向”而不是直接对数据进行锁定。意向锁的存在是为了后续更细粒度的锁请求做准备并且它们的设计目标是为了减少锁之间的等待提高并发处理能力 可以通过以下 SQL 语句查看意向锁和行锁的加锁情况
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;4. 行级锁
4.1 介绍
行级锁每次操作锁住对应的行数据锁定粒度最小发生锁冲突的概率最低并发度最高应用在 InnoDB 存储引擎中
InnoDB 的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁对于行级锁主要分为三类
第一类行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行 update 和 delete 操作在 RC、RR 隔离级别下都支持 第二类间隙锁Gap Lock锁定索引记录间隙不含该记录确保案引记录间隙不变防止其他事务在这个间隙进行 insert 操作产生幻读在 RR 隔离级别下都支持 第三类临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙 Gap在 RR 隔离级别下支持 4.2 行锁
InnoDB 实现了以下两种类型的行锁
共享锁(S)允许一个事务去读一行阻止其他事务获得相同数据集的排它锁排他锁(X)允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁 共享锁和排他锁之间的兼容情况 在进行增删查改语句的时候所加的行锁类型情况如下 默认情况下InnoDB 引擎在 REPEATABLE READ 事务隔离级别运行InnoDB 使用 Next-Key Locks 锁临键锁进行搜索和索引扫描以防止幻读
针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁lnnoDB 的行锁是针对于索引加的锁不通过索引条件检索数据那么 InnoDB 将对表中的所有记录加锁此时就会升级为表锁
4.2.1 测试行锁之间的互斥性
接下来我们来演示一下行锁用两个客户端分别连接 MySQL 服务器用 student 表来测试
student 表的数据如下其中 id 为主键name 字段和 no 字段没有建立索引 在第一个连接中开启一个新事务
begin;接着运行以下 SQL 语句
select *
from student
where id 1;当执行以上 SQL 语句后到底有没有对 id 为 1 的这一行数据加锁呢
我们在第二个连接中运行以下 SQL 语句查看表的加锁情况
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;可以看到 data_locks 表中没有任何数据说明简单的 select 语句不会加上行锁 我们在第一个连接中执行以下 SQL 语句
select *
from student
where id 1 lock in share mode;在第二个连接中查看是否加了锁 可以看到 data_locks 表中有两条数据第一条数据的 lock_type 字段为 TABLE表明加的是表锁 lock_mode 字段是 IS表明加的是意向共享锁
我们重点关注第二条数据第二条数据的 lock_type 字段为 RECORD表明加的是行锁lock_mode 字段是 S 和 REC_NOT_GAPS 代表共享锁REC_NOT_GAP代表没有间隙 那共享锁和共享锁之间能不能兼容呢可以
我们在第二个连接中开启一个新事务
begin;执行同样的 SQL 语句
select *
from student
where id 1 lock in share mode;接着在第二个连接中再次查看 student 表的加锁情况
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;可以看到此时 student 表中有四条记录说明两个共享锁之间是兼容的 我们提交第二个连接的事务后再次在第二个连接中开启一个新事务
接着在第二个连接中运行以下 SQL 语句
update student
set name 李四
where id 3;可以发现执行 SQL 语句时并没有阻塞SQL 语句也执行成功了
我们在第二个连接中运行以下 SQL 语句操作 id 1 的数据
update student
set name 王五
where id 1;可以发现光标一直在闪动说明处于阻塞状态因为第一个连接已经对 id 为 1 的数据添加了共享锁第二个连接想要对 id 为 1 的数据添加排他锁而共享锁和排他锁之间是有冲突的所以第二个连接需要等待第一个连接释放共享锁后才能获取到排他锁
由于等待获取排他锁的时间过长第二个连接出现了以下错误如果光标一直在闪烁可以通过 CTRL C 快捷键中断 SQL 语句操作 我们提交第一个连接中的事务后在第二个连接中再次查看 student 表中的加锁情况 可以发现第一个连接提交事务后第二个连接获取到了 id 为 3 这一行数据的排他锁
在第二个连接中提交事务再次查看 student 表中的加锁情况 发现锁已经释放了但 id 为 1 的这一行数据的 name 字段并没有被修改成王五 4.2.2 测试行锁升级为表锁的情况
我们分别在第一个连接和第二个连接中开启事务接着在第一个连接中执行以下 SQL 语句
update student
set name Lei
where name 李四;按理说执行 SQL 语句后会对 id 为 3 的这一行数据添加一个行锁
我们在第二个连接中执行以下 SQL 语句
update student
set name 吴彦祖
where id 4;发现 SQL 语句在执行时被阻塞了按理说第一个连接锁的是 id 为 3 的这一行数据为什么会影响到第二个连接修改 id 为 4 的数据呢
因为第一个连接更新数据的时候是根据 name 字段进行更新的而 name 字段没有建立索引所以会锁住表中的所有记录导致行锁升级为表锁
我们提交第一个连接的事务后第二个连接的更新操作就立刻完成了 接下来我们提交第二个连接的事务然后为 name 字段建立一个索引
create index index_student_name on student (name);再次重复以上测试过程发现第一个连接更新 id 为 3 的这一行数据时不会再影响到第二个连接修改 id 为 4 的数据
4.3 间隙锁临键锁
默认情况下InnoDB 在 REPEATABLE READ 事务隔离级别运行InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描以防止幻读
间隙锁Gap Locks用于防止幻读对表中的一个范围进行锁定临键锁Next-Key Locks是间隙锁和行锁的组合用于防止幻读、修改或删除范围内的行 间隙锁的唯一目的就是防止其他事务插入间隙间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁 针对索引上的等值查询、唯一索引上的范围查询以及普通索引上的范围查询的三种情况间隙锁和临键锁的具体情况有所不同
索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁索引上的等值查询普通索引向右遍历至最后一个值不满足查询需求时临键锁Next-Key Locks退化为间隙锁索引上的范围查询唯一索引遍历到不满足条件的第一个值为止
需要注意的是间隙锁和临键锁的产生还取决于事务的隔离级别在某些隔离级别下如 READ COMMITTED间隙锁和临键锁可能不会自动产生 在演示间隙锁和临键锁之前我们先准备一个新的 teacher 表表结构和数据如下
/*Navicat Premium Data TransferSource Server : Source Server Type : MySQLSource Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)Source Host : Source Schema : blogTarget Server Type : MySQLTarget Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)File Encoding : 65001Date: 30/08/2024 19:01:15
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (id int NOT NULL AUTO_INCREMENT,name varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,age int NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE,INDEX teacher_id_index(id ASC) USING BTREE,INDEX index_teacher_age(age ASC) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 26 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO teacher VALUES (1, 张三, 1);
INSERT INTO teacher VALUES (3, 李四, 3);
INSERT INTO teacher VALUES (7, Amy, 7);
INSERT INTO teacher VALUES (8, 王五, 8);
INSERT INTO teacher VALUES (11, 赵六, 11);
INSERT INTO teacher VALUES (19, 钱七, 19);
INSERT INTO teacher VALUES (25, 老八, 25);SET FOREIGN_KEY_CHECKS 1;teacher 表的数据如下 接下来我们开始测试
在第一个连接中开启事务然后在第一个连接中运行以下 SQL 语句
update teacher
set name Jane Doe
where id 5;由于 id 为 5 的这一行数据并不存在InnoDB 会怎样加锁呢是把 id 为 5 的这一行数据锁住了吗实际上并不是
InnoDB 会对 3 和 8 之间的间隙加上一个间隙锁具体的范围是(3, 8)也就是左开右开的区间不含包 3 和 8
在第二个连接中查看 teacher 表的加锁情况 可以看到teacher 表有两把锁一把是表锁一把是间隙锁间隙锁使用的索引为主键
既然锁的是 id 范围在 (3, 8) 之间的数据那我插入一条 id 为 7 的数据会怎么样呢
我们在第二个连接中开启一个事务接着在第二个连接中执行以下 SQL 语句
insert into teacher
values (7, Amy, 30);可以发现 SQL 处于阻塞状态因为第一个连接的事务给 id 范围在 (3, 8) 之间的数据加了一个间隙锁在第一个连接的事务提交之前第二个连接的插入操作都会被阻塞 当我们进行等值查询的时候如果不是唯一索引会发生什么现象呢
我们在加行锁的时候是针对索引加的锁而索引是 BTree 数据结构BTree 的叶子结点形成的是一个双向链表 以上图为例假如我们要根据二级索引的值是否等于 18 来查询并且给二级索引 等于 18 这行数据添加一个共享锁此时是仅仅把二级索引等于 18 的这条记录给锁住就完事了吗并不是的因为当前的二级索引没有唯一性约束那以后是不是就有可能在 18 的左边插入一条二级索引为 18 的记录也有可能在 18 的右边插入一条二级索引为 18 的记录
所以InnoDB 会对 (16, 18) 之间的间隙和 (18, 29) 之间的间隙加锁 接下来我们用 teacher 表来演示一下
首先为 age 字段建立一个普通的非唯一索引
create index index_teacher_age on teacher (age);我们在第一个连接中开启事务接着运行以下 SQL 语句
select *
from teacher
where age 3 lock in share mode;接着在第二个连接中查询 teacher 表的加锁情况 可以发现有四条记录第一行是表的意向锁我们重点关注后面三行
第二行的 lock_data 为 3, 3lock_mode 为 S是一个临键锁表示要把 age 为 3 的这条记录以及 age 在 (1,3) 区间的间隙锁住第一个 3 是 二级索引也就是 age 字段第二个 3 是主键索引也就是 id 字段
第三行的 lock_data 为 3且索引类型为主键表示锁住了 id 为 3 对应的记录
第四行的 lock_data 为 7, 7lock_mode 含有 GAP是一个间隙表示要把 age 在 (3, 7) 区间的间隙锁住 最后提交第一个连接中的事务 我们在第一个连接中开启事务然后在第一个连接中执行以下 SQL 语句
select *
from teacher
where id 19 lock in share mode接着在第二个连接中查询 teacher 表的加锁情况 可以看到有 4 行记录第一行是表的意向锁我们重点关注后面三行
第二行表明对 id 为 19 的这一行记录加了一个行锁
第三行表示加了一个临键锁lock_data 的 supremum pseudo-read 属性可以理解为正无穷大锁的是 (25, ∞) 之间的间隙
第四行表示加了一个临键锁锁的是 (19, 25) 之间的间隙和 id 25 对应的记录 间隙锁锁的是间隙不包含对应的数据记录而临键锁既会锁住数据记录也会锁定数据记录之前或之后的间隙
其实间隙锁和临键锁不用刻意记忆关键是搞清楚 InnoDB 引擎为什么要加上间隙锁和临键锁
