`
骑猪逛街666
  • 浏览: 141502 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

MySQL锁系列（七）之 锁算法详解

算法mysql360cLotus 
阅读更多
阅读原文请点击：http://click.aliyun.com/m/23854/
摘要： 能学到什么 隔离级别和锁的关系 重点讲解在RR隔离级别下的加锁算法逻辑 重点罗列了比较典型的几种加锁逻辑案例 对insert的加锁逻辑进行了深度剖析 实战中剖析加锁的全过程 InnoDB为什么要这样加锁 隔离级别和算法 repeatable-read 1.

能学到什么
隔离级别和锁的关系
重点讲解在RR隔离级别下的加锁算法逻辑
重点罗列了比较典型的几种加锁逻辑案例
对insert的加锁逻辑进行了深度剖析
实战中剖析加锁的全过程
InnoDB为什么要这样加锁
隔离级别和算法
repeatable-read
1. 使用的是next-key locking
2. next-key lock  =  record lock + Gap lock

read-committed
1. 使用的是 record lock
2. 当然特殊情况下( purge + unique key )，也会有Gap lock
我们接下来就以RR隔离级别来阐述，因为RC更加简单

锁的通用算法
RR隔离级别
1. 锁是在索引上实现的
2. 假设有一个key，有5条记录， 1，3，5，7，9.  如果where id<5 ， 那么锁住的区间不是（-∞，5），而是(-∞,1],(1,3],(3,5] 多个区间组合而成
3. RR隔离级别使用的是：next-key lock算法，即：锁住 记录本身+区间
4. next-key lock 降级为 record lock的情况
    如果是唯一索引，且查询条件得到的结果集是1条记录（等值，而不是范围），那么会降级为记录锁
    典型的案例：where primary_key = 1 (会降级), 而不是 where primary_key < 10 （由于返回的结果集不仅仅一条，那么不会降级）
5. 上锁，不仅仅对主键索引加锁，还需要对辅助索引加锁，这一点非常重要
锁算法的案例剖析
RR隔离级别
表结构
dba:lc_3> show create table a;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
| Table | Create Table
             |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
| a     | CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
1 row in set (0.00 sec)


dba:lc_3> select * from a;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
4 rows in set (0.00 sec)

* 设置RR隔离级别
set tx_isolation = 'repeatable-read';
等值查询，非唯一索引的加锁逻辑
dba:lc_3> begin;
Query OK, 0 rows affected (0.00 sec)

dba:lc_3> select * from a where c=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601815 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc     ;;
1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601815 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d012a; asc    '  *;;
3: len 4; hex 80000007; asc     ;;
4: len 4; hex 80000009; asc     ;;
5: len 4; hex 8000000b; asc     ;;

RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc     ;;
1: len 4; hex 80000007; asc     ;;


锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock，((7,3),(9,5)] , ((9,5),(11,7)]，解读一下：((7,3),(9,5)] 表示：7是二级索引key，3是对应的主键
    2.这样写不太好懂，所以以后就暂时忽略掉主键这样写： next-key lock = (7,9],(9,11]

对主键索引primary： 加record lock，[5]


等值查询，唯一键的加锁逻辑
dba:lc_3> select * from a where b=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601816 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc     ;;
1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0137; asc    '  7;;
3: len 4; hex 80000009; asc     ;;
4: len 4; hex 8000000b; asc     ;;
5: len 4; hex 8000000d; asc     ;;


锁的结构如下：

对二级索引idx_b：
    1. 加record lock，[9]

对主键索引primary：
    1. 加record lock，[7]


>= ，非唯一索引的加锁逻辑
dba:lc_3> select * from a where c>=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601817 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601817 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc     ;;
1: len 4; hex 80000005; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc     ;;
1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601817 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d012a; asc    '  *;;
3: len 4; hex 80000007; asc     ;;
4: len 4; hex 80000009; asc     ;;
5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0137; asc    '  7;;
3: len 4; hex 80000009; asc     ;;
4: len 4; hex 8000000b; asc     ;;
5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (7,9],(9,11],(11,∞]

对主键索引primary：
    1. 加record lock，[5],[7]

>= ，唯一索引的加锁逻辑
dba:lc_3> select * from a where b>=7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
2 rows in set (0.00 sec)


TABLE LOCK table `lc_3`.`a` trx id 133601820 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601820 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 4; hex 80000005; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc     ;;
1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601820 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d012a; asc    '  *;;
3: len 4; hex 80000007; asc     ;;
4: len 4; hex 80000009; asc     ;;
5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0137; asc    '  7;;
3: len 4; hex 80000009; asc     ;;
4: len 4; hex 8000000b; asc     ;;
5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_b：
    1. 加next-key lock， (5,7],(7,9],(9,∞]

对主键索引primary：
    1. 加record lock，[5],[7]


<= , 非唯一索引的加锁逻辑

dba:lc_3> select * from a where c<=7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601822 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601822 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc     ;;
1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601822 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0110; asc    '   ;;
3: len 4; hex 80000003; asc     ;;
4: len 4; hex 80000005; asc     ;;
5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d011d; asc    '   ;;
3: len 4; hex 80000005; asc     ;;
4: len 4; hex 80000007; asc     ;;
5: len 4; hex 80000009; asc     ;;


锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (-∞,5],(5,7],(7,9]

对主键索引primary：
    1. 加record lock，[1],[3]


<= , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b<=5 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
+---+------+------+------+
2 rows in set (0.00 sec)



TABLE LOCK table `lc_3`.`a` trx id 133601823 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601823 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601823 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0110; asc    '   ;;
3: len 4; hex 80000003; asc     ;;
4: len 4; hex 80000005; asc     ;;
5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d011d; asc    '   ;;
3: len 4; hex 80000005; asc     ;;
4: len 4; hex 80000007; asc     ;;
5: len 4; hex 80000009; asc     ;;


锁的结构如下：

对二级索引idx_b：
    1. 加next-key lock， (-∞,3],(3,5],(5,7]

对主键索引primary：
    1. 加record lock，[1],[3]

> , 非唯一索引的加锁逻辑
dba:lc_3> select * from a where c>9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)



RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601825 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc     ;;
1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601825 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 000007f66444; asc     dD;;
2: len 7; hex fc0000271d0137; asc    '  7;;
3: len 4; hex 80000009; asc     ;;
4: len 4; hex 8000000b; asc     ;;
5: len 4; hex 8000000d; asc     ;;






锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (9,11],(11,∞]

对主键索引primary：
    1. 加record lock，[7]


> , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b>7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)
阅读原文请点击：http://click.aliyun.com/m/23854/
分享到：
| Spark与深度学习框架——H2O、deeplearni ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL-5.5新特性详解及参数优化

    在MySQL 5.5版本中，出现了一系列重要的新特性，这些新特性主要关注性能提升、安全性增强和系统配置参数的动态调整。以下是对这些新特性及参数优化的知识点的详细介绍： 1. 性能上的显著改变： a. 默认存储引擎的...

    MySQL数据库性能监控与诊断

    ### MySQL数据库性能监控与诊断详解 在IT领域，特别是对于依赖于数据库的现代应用程序而言，数据库性能监控与诊断是确保系统稳定性和响应速度的关键环节。本文将深入探讨MySQL数据库性能监控与诊断的相关知识点，...

    mysql优化.txt

    ### MySQL Key Cache 优化详解 在MySQL数据库管理系统中，针对不同的存储引擎，有着不同的缓存机制来提升查询性能。本文将重点围绕MyISAM表类型的key cache进行详细讲解，并涉及InnoDB存储引擎的相关特性，以及...

    mysql常见面试题及答案.pdf

    ### MySQL常见面试题及答案详解 #### 一、MySQL基础知识 **1. MySQL 是什么？** MySQL 是一种开源的关系型数据库管理系统（RDBMS），它最初由瑞典的 MySQL AB 公司开发，随后被 Sun Microsystems 收购，并最终...

    mysql事务处理用法与实例代码详解

    然而，InnoDB引擎是支持ACID（原子性、一致性、隔离性、持久性）事务的，提供了行级锁和并发控制，这使得它成为处理复杂业务逻辑和高并发场景的理想选择。Berkeley DB也是一个支持事务的存储引擎，但其使用情况相对...

    MySQL后端面试题汇总.zip

    MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用程序中被广泛应用。对于后端开发者来说，熟练掌握MySQL是必备技能，而面试时，面试官往往会针对MySQL提出一系列问题来评估候选人的技术水平。以下是...

    第18章_MySQL8其它新特性1

    【MySQL8.0新特性详解】 MySQL 8.0是一个重要的里程碑版本，它带来了一系列显著的改进和增强，旨在提供更高的性能、更好的用户体验以及更强的安全性。以下是对这些新特性的详细解读： 1. **更简便的NoSQL支持**： ...

    最新Python3.5零基础+高级+完整项目（28周全）培训视频学习资料

    内置模块详解之time与datetime模块 内置模块详解之Range模块 内置模块详解之OS模块 内置模块详解之Sys模块 内置模块详解之Shelve模块 内置模块详解之Xml模块 内置模块详解之Configparser模块 内置模块详解之Hashlib...

    BAT面试题汇总及详解(进大厂必看)

    【压缩包内容】：这个压缩包可能包含了一系列与BAT面试相关的文档，涵盖了算法、数据结构、操作系统、计算机网络、数据库、编程语言、项目经验、团队协作、产品理解、商业分析等多个方面的知识。 **一、算法与数据...

    leetcode题库-go_learning:集合go的编程技巧分布式数据库MySQLLinux操作系统讲解设计模式主流的框架设计，结构设计，

    数据结构与算法之美 基础篇 go k8s实战 go MySQL系列 React 系列 深入浅出redis go—interview 最近在读编程珠玑 最近在读编程珠玑，打算每天做一些题目放上来，持续更新 go-高级编程 go-设计模式 深入理解计算机...

    20191118_京东内推.docx

    一、面试题涉及的知识点详解： 1. **Zookeeper在项目中的使用及原理**： Zookeeper是一个分布式协调服务，常用于管理分布式应用中的配置信息、命名服务、分布式同步、组服务等。它通过一致性算法保证数据的一致性...

    搜狐&&美团旅行面试题.docx

    - MySQL中的悲观锁通常通过行级锁实现，如`SELECT ... FOR UPDATE`语句。 6. **MySQL的乐观锁机制** - MySQL中的乐观锁通常通过版本号或时间戳来实现。在更新记录时检查版本号是否发生变化来判断数据是否被其他...

    郑州数字马力面试（后台java）经验

    MySQL中实现悲观锁与乐观锁** - **悲观锁**：通过行级锁或表级锁实现。 - **乐观锁**：一般通过版本号字段实现。 **20. 事务隔离级别与Spring中的事务传播行为** MySQL支持四种隔离级别：READ UNCOMMITTED、READ...

    Java 面试资料 进阶版

    - **策略模式**: 定义一系列的算法，把它们一个个封装起来，并且使它们可相互替换。 #### 五、Spring框架核心原理 - **依赖注入(DI)**: 一种编程实现，通过构造函数、setter方法或字段注入等方式将一个对象所依赖...

    大厂 Go 工程师面试题集锦.docx

    2. **计网七层协议详解** - **知识点**：理解OSI七层模型的基本概念及每一层的主要功能。 - **解释**：OSI模型定义了网络通信的七个层次，从物理层到应用层，每个层次都有特定的责任和功能。 3. **线程与进程的...

    AAAAA AAA 机试试题

    【AAAAA AAA 机试试题】相关知识点详解 在信息技术领域，"机试试题"通常指的是计算机编程或技术相关的在线测试题目，旨在评估应聘者或学生的技术能力、问题解决能力和编程技能。这些试题可能涵盖各种编程语言（如...

    C++课程设计-仓库管理系统源码.rar

    《C++仓库管理系统课程设计详解》 在信息技术领域，C++是一种广泛应用的编程语言，尤其在系统软件、应用软件和游戏开发中占有重要地位。本篇文章将深入探讨一个基于C++实现的仓库管理系统的设计与实现，旨在帮助...

Magicbox
Global site tag (gtag.js) - Google Analytics