mysql锁详解-黑马程序员课程笔记

已于 2023-08-10 15:44:30 修改
阅读量231 收藏 2
点赞数 3
文章标签： mysql oracle 数据库 笔记
于 2023-08-10 15:43:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_62357662/article/details/132210805
版权

文章目录

1. 概述

黑马程序员MySQL锁观看地址
​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有 效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个 角度来说，锁对数据库而言显得尤其重要，也更加复杂。

​ MySQL中的锁，按照锁的粒度分，分为以下三类：

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

2. 全局锁

2.1 概述

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语 句，已经更新操作的事务提交语句都将被阻塞。

2.2 使用场景

做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

为什么全库逻辑备份，就需要加全就锁呢？

A. 我们一起先来分析一下不加全局锁，可能存在的问题。 假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日 志表。

在这里插入图片描述

  • 在进行数据备份时，先备份了tb_stock库存表。
  • 然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入 tb_order表）。
  • 然后再执行备份 tb_order表的逻辑。
  • 业务中执行插入订单日志操作。
  • 最后，又备份了tb_orderlog表。

此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一 致(有最新操作的订单信息,但是库存数没减)。 那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。

B. 再来分析一下加了全局锁后的情况

在这里插入图片描述

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、 DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。 那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性 和完整性。

2.3 语法

​ 加全局锁

 flush tables with read lock ;

​ 数据备份

mysqldump -uroot –p密码 itcast > 备份的位置/取名.sql -- 不是在mysql内部执行，是在linux或者windows的命令行执行

释放锁

unlock tables ;

2.4 特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导 致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致 性数据备份。

mysqldump --single-transaction  -uroot –p密码 itcast > 备份的位置/取名.sql -- 不是在mysql内部执行，是在linux或者windows的命令行执行

3. 表级锁

3.1 概述

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、 InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

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

3.2 表锁

对于表锁，分为两类：

  • 表共享读锁（read lock）
  • 表独占写锁（write lock）

3.2.1 语法：

​ 加锁

lock tables 表名... read/write

释放锁

unlock tables

3.2.2 特点

​ 读锁

在这里插入图片描述

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。

测试:

​ 在这里插入图片描述

​ 写锁

在这里插入图片描述

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。

测试

在这里插入图片描述

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

3.3 元数据锁(DML)

3.3.1 概述

meta data lock , 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维 护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务 时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变 更操作的时候，加MDL写锁(排他)

3.3.2 SQL操作时的元数据锁

对应SQL锁类型说明
lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share modeSHARED_READ与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
insert 、update、 delete、select … for updateSHARED_WRITE与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
alter table …EXCLUSIVE与其他的MDL都互斥

3.3.3 测试

​ 当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。

在这里插入图片描述

​ 当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁 （EXCLUSIVE），之间是互斥的。

在这里插入图片描述

我们可以通过下面的SQL，来查看数据库中的元数据锁的情况

select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks ;

在这里插入图片描述

3.4 意向锁

3.4.1 概述

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行 数据是否加锁，使用意向锁来减少表锁的检查。

客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一 下：

没有意向锁

​ 首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

在这里插入图片描述

​ 当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就 会从第一行数据，检查到最后一行数据，效率较低。

在这里插入图片描述

有了意向锁

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

在这里插入图片描述

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而 不用逐行判断行锁情况了。

在这里插入图片描述

3.4.2 分类

  • 意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁 (read)兼容，与表锁排他锁(write)互斥。
  • 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共 享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

3.4.3 测试

意向共享锁与表读锁是兼容的

在这里插入图片描述

意向排他锁与表读锁、写锁都是互斥的

在这里插入图片描述

4. 行级锁

4.1 概述

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

​ 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。

​ 在这里插入图片描述

​ 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事 务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

​ 在这里插入图片描述

​ 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在RR隔离级别下支持。

在这里插入图片描述

4.2 行锁 （Record Lock）

4.2.1 概述

InnoDB实现了以下两种类型的行锁：

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

两种行锁的兼容情况如下:

共享锁（S）请求锁类型排他锁（X）请求锁类型
共享锁（S）当前锁类型兼容冲突
排他锁（X）当前锁类型冲突冲突

4.2.2 SQL执行所加的行锁

SQL行锁类型说明
INSERT …排他锁自动加锁
UPDATE …排他锁自动加锁
DELETE …排他锁自动加锁
SELECT（正常）不加任何锁
SELECT … LOCK IN SHARE MODE共享锁需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE排他锁需要手动在SELECT之后加FOR UPDATE

4.2.3 测试

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜 索和索引扫描，以防止幻读。

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

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

数据准备:

CREATE TABLE `stu` (
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);

演示行锁的时候，我们就通过上面这张表来演示一下。

  • 普通的select语句，执行时，不会加锁。

    在这里插入图片描述

  • select…lock in share mode，加共享锁，共享锁与共享锁之间兼容。

    在这里插入图片描述

共享锁与排他锁之间互斥。

在这里插入图片描述

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

  • 排它锁与排他锁之间互斥

    在这里插入图片描述

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

  • 无索引行锁升级为表锁

stu表中数据如下:

在这里插入图片描述

我们在两个客户端中执行如下操作:

在这里插入图片描述

在客户端一中，开启事务，并执行update语句，更新name为Lily的数据，也就是id为19的记录 。 然后在客户端二中更新id为3的记录，却不能直接执行，会处于阻塞状态，为什么呢？

原因就是因为此时，客户端一，根据name字段进行更新时，name字段是没有索引的，如果没有索引， 此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。

接下来，我们再针对name字段建立索引，索引建立之后，再次做一个测试：

在这里插入图片描述

此时我们可以看到，客户端一，开启事务，然后依然是根据name进行更新。而客户端二，在更新id为3的数据时，更新成功，并未进入阻塞状态。 这样就说明，我们根据索引字段进行更新操作，就可以避 免行锁升级为表锁的情况。

4.3 间隙锁（Gap Lock）& 临键锁（Next-Key Lock）

4.3.1 概述

​ 默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜 索和索引扫描，以防止幻读。

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

  • 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时， 临键锁（next-key lock） 退化为间隙锁。

  • 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

    注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁。

4.3.2 测试

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。

    在这里插入图片描述

  2. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时， 临键锁（next-key lock） 退化为间隙锁。

    介绍分析一下：

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

    在这里插入图片描述

    在这里插入图片描述

  3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

在这里插入图片描述

查询的条件为id>=19，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部分：

[19]

(19,25]

(25,+∞]

所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临 键锁(正无穷及之前的间隙)。

Knock-King
关注
  • 3
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
黑马MySQL数据库笔记.doc
12-25
黑马程序员数据库笔记。数据库语句的介绍，等等优秀 的操作，可以帮助更好的理解数据库基础，是不错的材料，值得学习与借鉴！
《MySQL数据库入门》-黑马程序员 配套书籍资源 .zip
07-18
配套教学ppt，教学大纲，教学设计，课后题及答案，源代码等资源。
黑马程序员Mysql系列笔记
weixin_45905371的博客
04-10 3302
基础篇 通用语法及分类 DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段） DML: 数据操作语言，用来对数据库表中的数据进行增删改 DQL: 数据查询语言，用来查询数据库中表的记录 DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限 DDL（数据定义语言） 数据定义语言 数据库操作 查询所有数据库： SHOW DATABASES; 查询当前数据库： SELECT DATABASE(); 创建数据库： CREATE DATABASE [ IF NOT EXISTS ] 数据库名
【笔记】黑马程序员 MySQL数据库入门到精通 —— 基础篇_理论_黑马mysql 基础笔记
最新发布
2401_84006805的博客
05-01 1020
数据库：DataBase(DB)，是存储数据的仓库数据库管理系统：DataBase Management System ，是操纵和管理数据库的大型软件SQL：Structed Query Language，结构化查询语言，是操作关系型数据库的编程语言-h 指定连接的主机地址；-P 指定连接端口号；-u 指定用户名 -p指定用户名密码关系型数据库(RDBMS)：基于表存储数据，Oracle，MYSQL，SQL Server，PostgreSQL，SQLite，
【黑马程序员】mysql基础篇笔记
好好学习日记
10-26 250
select * from a left (outer) join b on a.id=b.id （innor 可以省略）显示内连接select * from a (inner) join b on a.id=b.id （innor 可以省略）A查id=1，查不到，（因为已经解决了不可重复读的问题了，所以和之前读的一样，还是没有）隐式内连接select * from a, b where a.id=b.id。自连接可以是内连接也可以是外连接，起别名。A插入1，不行，因为主键已插入。
黑马程序员MySQL-存储过程SQL笔记
糖果墙的博客
01-06 9619
对应课程地址 -- 存储过程 -- 准备数据 create database if not exists mydb7_procedure; use mydb7_procedure; create table dept( deptno int primary key, dname varchar(20), loc varchar(20) ); insert into dept values(10, '教研部','北京'), (20, '学工部','上海'), (30, '销售部','广州'), (40
MySQL（黑马程序员学习笔记）
weixin_59368946的博客
04-12 1267
黑马程序员MySQL学习笔记
MYSQL数据库入门实战课程-重点笔记
04-03
Mysql ⾯试和经典查询案例演示 mysql数据库实战训练课程简介 MySQL函数⼤全，MySQL常⽤函数汇总 MySQL约束、函数和运算符 MySQL数据类型和存储引擎数据库设计 SQL是什么？它能做什么？ 电商平台(B2B2C)项⽬实战...
mysql锁详解
07-23
资源名称：mysql锁详解资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
---mysql---相关的笔记
10-13
---mysql---相关的笔记
MySQL基础-01-授课笔记.md
10-16
MySQL基础-01-授课笔记.md
mysql学习笔记和案例(全部知识点版)
01-17
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
程序员资料（课程讲义）
11-03
windows server 2005 介绍，ppt版
mySQL 学习笔记(黑马程序员)
nishuaiqidezhege的博客
07-04 2559
本文仅作为个人笔记，刚开始学习数据库，欢迎评论交流
黑马程序员MYSQL进阶笔记（超详细）
weixin_50523986的博客
06-14 2025
数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以实现高级查找算法执行的SQL语句为 ：无索引情况：全表扫描有索引：改进B+treeMySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构不同的存储引擎对于索引结构的支持情况视图（View）是一种虚拟存在的表视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表视图只保存了查询的SQL逻辑，不保存查询结果。
【笔记】黑马程序员 MySQL数据库入门到精通 —— 基础篇_理论
weixin_43254100的博客
07-03 1421
黑马程序员 MySQL数据库入门到精通 —— 基础篇_理论总结
黑马程序员，SQL基础学习笔记
xuyang1152的专栏
09-03 355
---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------  有些事情就是这样，不动手永远都不会了解，自己能做什么。因为在之前学校学过SQL的入门，并不把他放在眼里，之后一次次练习的无从下笔，傻眼，终于承认，必须一步步一个脚印。是啊 学习就是这样。 跟着杨老师学习，一边学习一边坐着笔记：SQL
《黑马程序员 MySQL数据库入门到精通，从MySQL安装到MySQL高级、MySQL优化全囊括》——学习笔记基础篇
u011044503的博客
09-25 2413
《黑马程序员 MySQL数据库入门到精通，从MySQL安装到MySQL高级、MySQL优化全囊括》学习笔记
【MySQL存储索引】-黑马程序员视频观看笔记
kybabcde的博客
02-12 114
存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。# 1、在创建表时，指定存储引擎CREATE TABLE 表名{字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释]}ENGINE = INNODB [COMMENT 表注释]# 2、查看当前数据库支持的鵆引擎1、体系结构连接层、服务层、引擎层、存储层2、存储引擎简介3、存储引擎特点INNODB与MyISAM：事务、外键、行级锁4、存储引擎的应用。
黑马程序员java笔记mysql
10-19
黑马程序员Java笔记MySQL是一本介绍MySQL数据库的书籍，主要内容包括MySQL的安装、基本语法、高级应用、优化等方面的知识点。其中，MySQL的基本语法包括增删改查等操作，例如执行SQL语句"insert into goods_myisam values(null,'笔记本')"可以向goods_myisam表中插入一条数据。在Java中连接MySQL数据库需要使用JDBC，其中JDBC的连接信息包括URL、用户名和密码等，例如"jdbc.url=jdbc:mysql://localhost:3306/day06 jdbc.黑马程序员Java笔记MySQL是一本介绍MySQL数据库的书籍，主要内容包括MySQL的安装、基本语法、高级应用、优化等方面的知识点。其中，MySQL的基本语法包括增删改查等操作，例如执行SQL语句"insert into goods_myisam values(null,'笔记本')"可以向goods_myisam表中插入一条数据。在Java中连接MySQL数据库需要使用JDBC，其中JDBC的连接信息包括URL、用户名和密码等，例如"jdbc.url=jdbc:mysql://localhost:3306/day06 jdbc.user=root jdbc.password=root"。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值