MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁

1.MySQL（一）Linux下MySQL的安装2023-02-16 2.MySQL（二）字符集、比较规则与规范2023-02-16 3.MySQL（三）数据目录2023-02-16 4.MySQL（四）用户与权限管理2023-02-16 5.MySQL（五）配置文件、系统变量与MySQL架构2023-03-17 6.MySQL（六）存储引擎2023-03-17 7.MySQL（七）索引2023-03-17 8.MySQL（八）哈希索引、AVL树、B树与B+树的比较2023-03-17 9.MySQL（九）InnoDB行格式2023-04-03 10.MySQL（九）InnoDB数据结构2023-04-03 11.MySQL（十）表空间结构：区、段与碎片区2023-04-03 12.MySQL（十一）索引的分类和创建原则2023-04-05 13.MySQL（十二）索引使用的情况分析2023-04-06 14.MySQL（十三）MySQL性能分析工具：慢查询日志与PROFILE查询成本2023-04-07 15.MySQL（十四）分析查询语句Explain 七千字总结2023-04-14 16.MySQL（十五）分析优化器的查询计划：Trace2023-07-18 17.MySQL（十六）索引优化：索引失效的情况分析2023-04-29 18.MySQL（十七）查询优化（一）2023-05-02 19.MySQL（十七）查询优化（二）与数据库主键设计2023-05-02 20.MySQL（十八）MySQL事务(一)：事务的概述与使用2023-05-04 21.MySQL（十八）MySQL事务(二)：事务的隔离级别2023-05-09 22.MySQL（十九）MySQL事务日志（一）RedoLog2023-05-09 23.MySQL（十九）MySQL事务日志（二）UndoLog2023-05-09 24.MySQL（二十）锁（一）锁的概述与S、X锁2023-05-09 25.MySQL（二十）锁（二）表锁、行锁与页级锁2023-05-09

26.MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁2023-05-09

27.MySQL（二十一）MVCC多版本并发控制2023-07-18 28.MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志2023-05-09 29.MySQL（二十二）其他数据库日志（二）bin log二进制日志与中继日志2023-05-09 30.【完结撒花】MySQL（二十三）主从复制2023-05-10

MySQL（二十）锁（三）乐观锁与悲观锁显式锁和隐式锁

1 从对待锁的态度划分：乐观锁、悲观锁

从对待锁的态度划分，可以将锁划分为乐观锁和悲观锁，可以看出这两种锁是两种对待数据并发的思维方式。
乐观锁和悲观锁并不是锁，而是锁的设计思想

1.1 乐观锁

乐观锁对数据被其他事务修改持有乐观态度
每次不会对数据上锁，而是在更新的时候判断一下其他事务有没有更新这个数据
不需要借助数据库本身的锁机制，而是通过程序实现，如版本号机制或者cas机制
乐观锁适用于多读的场景，能够提高吞吐量

乐观锁的版本号机制

在表中设计一个version字段，每次在更新的时候更新一下这个字段，如果发生变化则更新失败，重新获取字段进行尝试：

 update ... set version = version + 1 where `version` = version;

1.2 悲观锁

悲观锁是一种思想，即对数据被其他事务修改持有悲观态度
会通过数据库本身的锁机制（比如读写锁、表锁、行锁等）在每次获取数据的时候都进行加锁，其他事务想要获取数据就会堵塞直到它拿到锁。
特别对于长事务，悲观锁对于数据库性能开销影响很大

注意点：

SQL语句SELECT ... FOR UPDATE就是MySQL中的悲观锁，会将扫描过程中的所有行都加锁

这是因为MySQL加行锁是加到索引上的，所以如果使用锁没有用到索引，则会进行全表扫描，将全表索引都加上锁
当一个事务执行SELECT ... FOR UPDATE，另一个事务执行SELECT ... 并不会收到影响，这是因为InnoDB存储引擎采取的是快照读，不会收到锁的影响

2 按照加锁的方式划分：隐式锁和显式锁

🌟2.1 隐式锁

一个事务如果在执行insert操作的时候，如果插入的间隙被添加了gap锁，则这个事务会堵塞，并且当前事务会在该间隙上插入一个插入意向锁，而如果没有gap锁，则一般情况下insert操不会添加任何的锁。如果一个事务首先插入了一条记录（此时没有在内存生产与该记录关联的表结构），然后另一个事务

立即查看这条记录，即获取S锁或者X锁，则可能导致脏读问题
立即修改这条记录，即获取X锁，就可能导致脏写问题

之前讲过，Compact行格式有三个隐藏列：row_id、trx_id和回滚指针，其中的事务idtrx_id就用于解决上面的问题：

情景一：对于聚簇索引来说，会有一个trx_id记录着最后改动该记录的事务id，如果当前事务插入了一条聚簇索引记录后，该记录的trx_id就会变成该事务的id，如果此时其他事务想要对该记录添加X锁或者S锁，就会先查看一下trx_id对应的事务是否处于活跃状态（即is_waiting是否为false），如果是，则帮助活跃的事务创建一个X锁（即给活跃的事务创建一个锁结构，锁结构的is_waiting为false），然后自己进入等待状态（即给当前事务创建一个锁结构，锁结构的is_waiting为true）
情景二：对于二级索引来说，本身没有trx_id属性，但是在二级索引页面的Page Header中记录的Page_max_trx_id表示对该页面做改动的最大的事务id，如果当前正在活跃的事务id大于该Page_max_trx_id，则表明“对页面进行修改的事务都已经提交了”，否则需要在页面中定位到对应的二级索引，通过回表找到聚簇索引重新执行情景一

总结来说，一个事务对新插入的记录可以不显式地加锁，因为有trx_id的存在，相当于加了一个隐式锁，其他事务想要对该记录添加X锁 或者S锁的时候，需要查看一下trx_id对应的事务是否活跃，如果活跃则需要帮助活跃事务创建一个X锁的内存结构，并也给自己添加X锁陷入等待。

隐式锁采用如上的延迟加锁的机制，目的是为了减少锁的数量。

测试

事务一执行：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into student1 values(7, '7', '7');
Query OK, 1 row affected (0.00 sec)
 
mysql>

事务二执行，发现出现了堵塞

 Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
-- 正常查看是因为InnoDB的快照读
mysql> select * from student1 where stu_no = 7;
Empty set (0.00 sec)
 
-- 因为隐式锁的存在，加锁失败
mysql> select * from student1 where stu_no = 7 lock in share mode;

select * from performance_schema.data_lock_waits查看目前堵塞的事务，能够查看到也可以说隐式锁转化为了显示锁

 mysql> select * from performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 140068966219776:183:4:8:140068882800792
REQUESTING_ENGINE_TRANSACTION_ID: 57127
            REQUESTING_THREAD_ID: 3637
             REQUESTING_EVENT_ID: 26
REQUESTING_OBJECT_INSTANCE_BEGIN: 140068882800792
         BLOCKING_ENGINE_LOCK_ID: 140068966218968:183:4:8:140068882794272
  BLOCKING_ENGINE_TRANSACTION_ID: 57122
              BLOCKING_THREAD_ID: 3637
               BLOCKING_EVENT_ID: 25
  BLOCKING_OBJECT_INSTANCE_BEGIN: 140068882794272
1 row in set (0.00 sec)
 
ERROR: 
No query specified

2.2 显式锁

通过特定语句加的锁

 ... for share | lock in share mode;
... for update;

3 其他锁

3.1 全局锁

全局锁就是对整个数据库实例进行加锁，使得整个数据库处于只读状态
其他线程下的语句处于堵塞状态

 Flush tables with read lock;

3.2 死锁

两个事务都持有对方需要的锁，并且都不释放自己持有的锁等待对方释放导致的互相等待的现象。

举例：

事务一运行：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update account set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update account set balance = balance + 10 where id = 2;
Query OK, 1 row affected (7.63 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务二运行：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update account set balance = balance + 10 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update account set balance = balance + 10 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

产生死锁的必要条件

互斥条件
不剥夺条件
请求和保持条件
环路等待条件

如何处理死锁

等待，直到超时

当两个事务开始互相等待，当一个事务等待时间超过设置的阈值innodb_lock_wait_timeout，就将其回滚，另一个事务继续执行，这种方法简单有效。缺点是对于在线事务，这个等待时间也无法接收，但是如果等待时间太短又容易误伤到正常事务
使用死锁检测进行死锁处理

InnoDB存储引擎提供了wait for graph来主动进行死锁的检测，每当加锁请求无法立即满足需要并进入等待的时候，wait for graph算法就会触发
- 这是一种主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，基于这两部分信息，可以绘制wait-for graph等待图。
- wait-for graph等待图即以等待事务为顶点，以请求加的锁为边的有向图，如果图中存在环，则说明存在死锁。
- 一旦检测到死锁，InnoDB就会选择Undo log量最小的事务，其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑）
- 缺点：每个被堵塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作（判断有无环，如DFS、BFS算法）的时间复杂度为O(n)，则n个节点复杂度为O(n^2)
- 解决方案：控制并发访问的数量，比如在中间件中实现对于相同行的更新，在进入存储引擎前排队，这样就不会在InnoDB内部有大量的死锁检测工作
- 进一步的思路：将一行记录改造成逻辑上的多行，这一行记录是其他的加和，这样就避免了对于一行的集中并发访问造成死锁的几率。

4 锁的内存结构与监控策略

4.1 锁的内存结构

了解即可，后续有空再填坑

4.2 锁的监控策略

关于MySQL锁的监控，一般可以通过检查InnoDB_row_lock等状态变量来分析系统上的行锁的争夺情况。

 mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 349905 | -- 总时长
| Innodb_row_lock_time_avg      | 20582  | -- 平均时长
| Innodb_row_lock_time_max      | 50006  | -- 最大时长
| Innodb_row_lock_waits         | 17     | -- 总次数
+-------------------------------+--------+
5 rows in set (0.02 sec)

其他监控方法：MySQL把事务和锁的信息记录在了information_schema库中，涉及到的三张表分别是INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。

MySQL8.0删除了information_schema.INNODB_LOCKS，添加了performance_schema.data_locks用于查看事务的锁的情况，information_schema.INNODB_LOCK_WAITS也被performance_schema.data_lock_wait替代。

上一篇MySQL（十九）MySQL事务日志（二）UndoLog

下一篇MySQL（二十一）MVCC多版本并发控制

posted @ 2023-05-09 13:56 Tod4 阅读(162) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

历史上的今天：
2022-05-09 【MyBatis】分页插件

Tod4の代码零碎

MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁

MySQL（二十）锁（三）乐观锁与悲观锁显式锁和隐式锁

1 从对待锁的态度划分：乐观锁、悲观锁

1.1 乐观锁

乐观锁的版本号机制

1.2 悲观锁

2 按照加锁的方式划分：隐式锁和显式锁

🌟2.1 隐式锁

2.2 显式锁

3 其他锁

3.1 全局锁

3.2 死锁

举例：

产生死锁的必要条件

如何处理死锁

4 锁的内存结构与监控策略

4.1 锁的内存结构

4.2 锁的监控策略

公告

合集 (4)

随笔分类 (212)

随笔档案 (190)

阅读排行榜

评论排行榜

推荐排行榜

最新评论

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

	mysql> insert into student1 values(7, '7', '7');
	Query OK, 1 row affected (0.00 sec)

	mysql>

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

	-- 正常查看是因为InnoDB的快照读
	mysql> select * from student1 where stu_no = 7;
	Empty set (0.00 sec)

	-- 因为隐式锁的存在，加锁失败
	mysql> select * from student1 where stu_no = 7 lock in share mode;

	mysql> select * from performance_schema.data_lock_waits\G;
	************************* 1. row *************************
	ENGINE: INNODB
	REQUESTING_ENGINE_LOCK_ID: 140068966219776:183:4:8:140068882800792
	REQUESTING_ENGINE_TRANSACTION_ID: 57127
	REQUESTING_THREAD_ID: 3637
	REQUESTING_EVENT_ID: 26
	REQUESTING_OBJECT_INSTANCE_BEGIN: 140068882800792
	BLOCKING_ENGINE_LOCK_ID: 140068966218968:183:4:8:140068882794272
	BLOCKING_ENGINE_TRANSACTION_ID: 57122
	BLOCKING_THREAD_ID: 3637
	BLOCKING_EVENT_ID: 25
	BLOCKING_OBJECT_INSTANCE_BEGIN: 140068882794272
	1 row in set (0.00 sec)

	ERROR:
	No query specified

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

	mysql> update account set balance = balance - 10 where id = 1;
	Query OK, 1 row affected (0.01 sec)
	Rows matched: 1 Changed: 1 Warnings: 0

	mysql> update account set balance = balance + 10 where id = 2;
	Query OK, 1 row affected (7.63 sec)
	Rows matched: 1 Changed: 1 Warnings: 0

	mysql> show status like 'innodb_row_lock%';
	+-------------------------------+--------+
	\| Variable_name \| Value \|
	+-------------------------------+--------+
	\| Innodb_row_lock_current_waits \| 0 \|
	\| Innodb_row_lock_time \| 349905 \| -- 总时长
	\| Innodb_row_lock_time_avg \| 20582 \| -- 平均时长
	\| Innodb_row_lock_time_max \| 50006 \| -- 最大时长
	\| Innodb_row_lock_waits \| 17 \| -- 总次数
	+-------------------------------+--------+
	5 rows in set (0.02 sec)

Tod4の代码零碎

MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁

MySQL（二十）锁（三）乐观锁与悲观锁 显式锁和隐式锁

1 从对待锁的态度划分：乐观锁、悲观锁

1.1 乐观锁

乐观锁的版本号机制

1.2 悲观锁

2 按照加锁的方式划分：隐式锁和显式锁

🌟2.1 隐式锁

2.2 显式锁

3 其他锁

3.1 全局锁

3.2 死锁

举例：

产生死锁的必要条件

如何处理死锁

4 锁的内存结构与监控策略

4.1 锁的内存结构

4.2 锁的监控策略

公告

合集 (4)

随笔分类 (212)

随笔档案 (190)

阅读排行榜

评论排行榜

推荐排行榜

最新评论

MySQL（二十）锁（三）乐观锁与悲观锁显式锁和隐式锁