当前位置： 首页 > article >正文

mysql8.0基础-锁基础(七)

article 2024/11/30 12:30:18

一、锁概述

锁 是计算机协调多个进程或者线程并发访问某一资源的机制。锁对数据库而言非常重要，也非常复杂。
事务的隔离性就是由锁来实现的

1.锁的分类

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

二、全局锁

全局锁就是对整个数据库实例枷锁，加锁后整个实例就处于只读状态，后续的MDL,DDL语句，都会进行阻塞。
使用的场景就是要进行逻辑备份的时候就行 全局锁表

1.全局锁sql

flush tables with read lock;

2.解全局锁sql

unlock tables

三、表级锁

表级锁是发生锁冲突的概率最高，并发度最低。应用在innodb，myisam,bdb等存储引擎中。

1.加读锁

特点：

​ 当前终端可以读，写报错
​ 其它终端可以读，写堵塞

查看表内容

mysql> select * from manual;
+----+-----------------+
| id | NAME            |
+----+-----------------+
|  1 | 九阴真经         |
|  2 | 七伤拳           |
|  3 | 乾坤大挪移        |
+----+-----------------+

开始锁表，加读锁

lock tables manual read;

插入数据进行测试,发现插入数据失败，提示有读锁。

mysql> insert into manual values(NULL,"九阳神功");
ERROR 1099 (HY000): Table 'manual' was locked with a READ lock and can't be updated

在第二个终端进行插入数据。发现终端处于阻塞状态中，而不是像第一个终端那样报错

insert into manual values(NULL,"九阳神功");

2 解读锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

此时发现第二个终端不在阻塞，插入成功了。

3 加写锁

特点：

​ 当前终端可以读，也可以写。
​ 其它终端不可以读，也不可以写。都会处于阻塞状态

mysql> lock tables manual write;
Query OK, 0 rows affected (0.00 sec)

加完写锁，当前终端读写都正常。

mysql> select * from manual;
+----+-----------------+
| id | NAME            |
+----+-----------------+
|  1 | 九阴真经         |
|  2 | 七伤拳           |
|  3 | 乾坤大挪移       |
+----+-----------------+
3 rows in set (0.00 sec)

mysql> insert into manual values(NULL,"九阳神功");
Query OK, 1 row affected (0.01 sec)

mysql> select * from manual;
+----+-----------------+
| id | NAME            |
+----+-----------------+
|  1 | 九阴真经         |
|  2 | 七伤拳           |
|  3 | 乾坤大挪移        |
|  7 | 九阳神功         |
+----+-----------------+
4 rows in set (0.00 sec)

4 解写锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

解读锁之后，其它终端读写都不会在阻塞

四、查看锁表

1.查看锁表情况

show open tables where in_use > 0;

字段含义：

字段名称含义
database表所在的数据库
table表名
in_use当前有多少线程在使用该表
name_locked是否有线程锁定了该表的元数据

2.进行锁表

这里同时锁了两张表。class和persion表

lock tables class read,persion write;

查看锁表情况

mysql> show open tables where in_use > 0;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| data     | persion |      1 |           0 |
| data     | class   |      1 |           0 |
+----------+---------+--------+-------------+

这里发现这里的In_use的值都变成了1

3.进行解锁

unlock tables;

# 这里不在显示结果，和没有锁表的结果正好对应
mysql> show open tables where in_use > 0;
Empty set (0.00 sec)

五、行级锁

行级锁，每次锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最小，并发度最高。应用在innodb存储引擎中。
innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

1.分类

行锁主要分为3类

1.1 行锁

锁定单个记录的锁，防止其它事务对此行进行update和delete,在rc和rr隔离级别下都支持。

1.2 间隙锁

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

1.3 临键锁

行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙。在RR隔离级别下支持。

2.行锁

在innodb实现了以下两种类型的行锁

2.1 共享锁（S）

共享锁也叫做 “读锁”
语法：

select ..... lock in share mode;

2.2 排他锁（x）

排它锁，也叫做"写锁"，
语法：

select ..... for update;

2.3 区别

1.当事务A是共享锁时：
事务B是可以加共享锁的，但是不能加排他锁

2.当事务A是共享锁时：
事务B是不可以加共享锁，也不可以加排他锁

3.sql的对应的锁

在事务中执行的sql类型对应的行锁类型

sql行锁类型说明
执行insert、update、delete语句排他锁innodb会自动加锁
执行select 语句不会加任何锁

4.查看正在运行的事务

创建测试表

create table test1(
	id int primary key auto_increment,
	name varchar(50),
	age int
);

insert into test1 values(NULL,'张无忌',20),
(NULL,'赵敏',21),
(NULL,'周芷若',23),
(NULL,'小昭',24),
(NULL,'杨逍',26),
(NULL,'谢逊',20),
(NULL,'韦一笑',20),
(NULL,'殷天正',22);

内容如下：

mysql> select * from test1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张无忌    |   20 |
|  2 | 赵敏      |   21 |
|  3 | 周芷若    |   23 |
|  4 | 小昭      |   24 |
|  5 | 杨逍      |   26 |
|  6 | 谢逊      |   20 |
|  7 | 韦一笑    |   20 |
|  8 | 殷天正    |   22 |
+----+-----------+------+
8 rows in set (0.00 sec)

4.1.启动事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张无忌    |   20 |
|  2 | 赵敏      |   21 |
|  3 | 周芷若    |   23 |
|  4 | 小昭      |   24 |
|  5 | 杨逍      |   26 |
|  6 | 谢逊      |   20 |
|  7 | 韦一笑    |   20 |
|  8 | 殷天正    |   22 |
+----+-----------+------+
8 rows in set (0.00 sec)

4.2.启动事务2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张无忌    |   20 |
|  2 | 赵敏      |   21 |
|  3 | 周芷若    |   23 |
|  4 | 小昭      |   24 |
|  5 | 杨逍      |   26 |
|  6 | 谢逊      |   20 |
|  7 | 韦一笑    |   20 |
|  8 | 殷天正    |   22 |
+----+-----------+------+
8 rows in set (0.00 sec)

4.3.查看正在运行的事务

SELECT 
	trx_id,
	trx_state,
	trx_started,
	trx_mysql_thread_id,
	trx_query,
	trx_isolation_level
FROM 
	information_schema.innodb_trx;

结果如下

+-----------------+-----------+---------------------+---------------------+-----------+---------------------+
| trx_id          | trx_state | trx_started         | trx_mysql_thread_id | trx_query | trx_isolation_level |
+-----------------+-----------+---------------------+---------------------+-----------+---------------------+
| 421462685425664 | RUNNING   | 2024-11-28 01:08:43 |                  26 | NULL      | REPEATABLE READ     |
| 421462685424856 | RUNNING   | 2024-11-28 01:08:39 |                  23 | NULL      | REPEATABLE READ     |
+-----------------+-----------+---------------------+---------------------+-----------+---------------------+

4.4.提交两个事务

提交两个事务，发现不在显示正在运行的事务信息

mysql > commit;

5.验证共享锁

5.1 启动事务1

加上共享锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where id = 1 lock in share mode;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张无忌     |   20 |
+----+-----------+------+
1 row in set (0.00 sec)

5.2 启动事务2

在事务2中发现：
1.可以正常查询加了共享锁的数据
2.更改加了共享锁的数据，发现事务2 开始阻塞。无法进行更改数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where id = 1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张无忌     |   20 |
+----+-----------+------+
1 row in set (0.00 sec)

mysql> update test1 set age = 100 where id = 1;

查看事务2状态.这时候发现两个不同：
1.状态由running变成了lock_wait状态，这个状态是锁等待。
2.出现锁等待的的语句是，update test1 set age = 100 where id = 1。
含义就是：事务2在进行update test1 set age = 100 where id = 1语句更新的时候无法更新，因为事务1对id为1的数据加了锁，要等到锁释放后，才可以继续。

+-----------------+-----------+---------------------+---------------------+-----------------------------------------+---------------------+
| trx_id          | trx_state | trx_started         | trx_mysql_thread_id | trx_query                               | trx_isolation_level |
+-----------------+-----------+---------------------+---------------------+-----------------------------------------+---------------------+
|            7280 | LOCK WAIT | 2024-11-28 01:10:28 |                  26 | update test1 set age = 100 where id = 1 | REPEATABLE READ     |
| 421462685424856 | RUNNING   | 2024-11-28 01:10:18 |                  23 | NULL                                    | REPEATABLE READ     |
+-----------------+-----------+---------------------+---------------------+-----------------------------------------+---------------------+

5.3 提交事务1

提交了事务1之后，发现事务2不在阻塞，立即执行成功。

6.排他锁

6.1 启动事务1

使用update语句innodb存储引擎就会自动加上排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set age = 90 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6.2 启动事务2

发现事务2的update的阻塞中

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test1 set age = 90 where id = 1;

查看事务的状态,发现状态也是lock_wait状态，和上边的含义一样。只不过这次事务1是自动加的排他锁。

mysql> SELECT  trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_isolation_level FROM  information_schema.innodb_trx;
+--------+-----------+---------------------+---------------------+----------------------------------------+---------------------+
| trx_id | trx_state | trx_started         | trx_mysql_thread_id | trx_query                              | trx_isolation_level |
+--------+-----------+---------------------+---------------------+----------------------------------------+---------------------+
|   7283 | LOCK WAIT | 2024-11-28 01:21:09 |                  26 | update test1 set age = 90 where id = 1 | REPEATABLE READ     |
|   7282 | RUNNING   | 2024-11-28 01:20:53 |                  23 | NULL                                   | REPEATABLE READ     |
+--------+-----------+---------------------+---------------------+----------------------------------------+---------------------+

六、行锁的风险点

1.死锁

死当两个或更多的事务相互等待对方释放资源时,就会发生死锁。
例如,事务1锁定了行A并试图锁定行B,同时事务2锁定了行B并试图锁定行A,这就形成了死锁。MySQL会检测到死锁并终止其中一个事务,但这仍可能导致性能问题和事务失败。

2.锁升级

如果一个事务锁定的行过多，innodb可能会将锁从行级升级表级，这可能就会导致更多的锁冲突

3.锁等待

如果一个事务已经锁定了某行，其它试图访问这行的事务就必须等待，这导致性能下降，如果有大量的事务在等待锁，就导致系统出现性能瓶颈。


http://www.kler.cn/a/415607.html

相关文章：

  • 浅谈——深度学习和马尔可夫决策过程
  • 命令行使用ssh隧道连接远程mysql
  • SpringBoot助力宠物领养系统
  • 【C++进阶篇】像传承家族宝藏一样理解C++继承
  • Jenkins环境搭建及简单介绍
  • 【鸿蒙】鸿蒙开发过程中this指向问题
  • neo4j desktop版命令行中导入导出dump
  • Unity之一键创建自定义Package包
  • 题目 3209: 蓝桥杯2024年第十五届省赛真题-好数
  • 信息学奥赛一本通 1448：【例题1】电路维修 | 洛谷 P4667 [BalticOI 2011 Day1] Switch the Lamp On 电路维修
  • 《使用Python进行数据挖掘：理论、应用与案例研究》
  • spine 动画层 动态权重
  • brew安装mongodb和php-mongodb扩展新手教程
  • 智启未来 扬帆5G：江苏移动打造“5G + 智慧教育”典范，引领教育新风尚
  • 个人博客接入github issue风格的评论，utteranc，gitment
  • Nuxt.js 应用中的 render：response 事件钩子
  • 【Java面试题】消息队列中，如何保证消息的顺序性？
  • SQL进阶——子查询与视图
  • Prophet时间序列算法总结及python实现案例
  • 关于Spring @Transactional事务传播机制详解
  • 前端面试题-1(详解事件循环）
  • Cesium CZML绘制Entity
  • 深入理解 MongoDB：一款灵活高效的 NoSQL 数据库
  • 2020 NHOI小学（C++）
  • C# yield 关键字
  • github通过修改hosts访问