AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-06-21 10521
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介：

能学到什么

  1. 隔离级别和锁的关系
  2. 重点讲解在RR隔离级别下的加锁算法逻辑
  3. 重点罗列了比较典型的几种加锁逻辑案例
  4. 对insert的加锁逻辑进行了深度剖析
  5. 实战中剖析加锁的全过程
  6. 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)



TABLE LOCK table `lc_3`.`a` trx id 133601826 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601826 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 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 133601826 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. 加next-key lock， (7,9],(9,∞]

对主键索引primary：
    1. 加record lock，[7]
  • < , 非唯一索引的加锁逻辑
dba:lc_3> select * from a where c<7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
+---+------+------+------+
1 row in set (0.00 sec)


TABLE LOCK table `lc_3`.`a` trx id 133601827 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601827 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 LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601827 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     ;;





锁的结构如下：

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

对主键索引primary：
    1. 加record lock，[1]
  • < , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b<5 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
+---+------+------+------+
1 row in set (0.00 sec)


TABLE LOCK table `lc_3`.`a` trx id 133601828 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601828 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 LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601828 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     ;;



锁的结构如下：

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

对主键索引primary：
    1. 加record lock，[1]
  • 总结之前的加锁逻辑
* 如果
1. select * from xx where col <比较运算符> M for update
2. M->next-rec: 表示M的下一条记录
3. M->pre-rec: 表示M的前一条记录


########第一轮总结########


* 等值查询M，非唯一索引的加锁逻辑
     (M->pre-rec,M],(M,M->next-rec]

* 等值查询M，唯一键的加锁逻辑
     [M], next-lock 降级为 record locks

* >= ，非唯一索引的加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* >= ，唯一索引的加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* <= , 非唯一索引的加锁逻辑
    (-∞] ... (M,M->next-rec]

* <= , 唯一索引的加锁逻辑
    (-∞] ... (M,M->next-rec]

* > , 非唯一索引的加锁逻辑
     (M,M->next-rec] ... (∞]

* > , 唯一索引的加锁逻辑
     (M,M->next-rec] ... (∞]

* < , 非唯一索引的加锁逻辑
     (-∞] ... (M->rec,M]

* < , 唯一索引的加锁逻辑
     (-∞] ... (M->rec,M]


########第二轮总结合并########

* 等值查询M，非唯一索引的加锁逻辑
    (M->pre-rec,M],(M,M->next-rec]

* 等值查询M，唯一键的加锁逻辑
    [M], next-lock 降级为 record locks
    这里大家还记得之前讲过的通用算法吗：
            next-key lock 降级为 record lock的情况：
                如果是唯一索引，且查询条件得到的结果集是1条记录（等值，而不是范围），那么会降级为记录锁

* >= ，加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* > ,  加锁逻辑
     (M,M->next-rec] ... (∞]

* <= , 加锁逻辑
    (-∞] ... (M,M->next-rec]

* < , 加锁逻辑
     (-∞] ... (M->rec,M]


########最后的疑问和总结########

1. 疑问： 为什么要对M->next-rec 或者  M->pre-rec ？

1. 回答： 因为为了防止幻读。

lock_update_0

insert 操作的加锁逻辑

RR 隔离级别

  • 表结构
dba:lc_3> show create table tb_non_uk;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                           |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_non_uk | CREATE TABLE `tb_non_uk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id2` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dba:lc_3> show create table tb_uk;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_uk | CREATE TABLE `tb_uk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


dba:lc_3> select * from tb_non_uk;
+----+------+
| id | id_2 |
+----+------+
|  1 |  100 |
|  2 |  200 |
+----+------+
2 rows in set (0.00 sec)

dba:lc_3> select * from tb_uk;
+----+------+
| id | id_2 |
+----+------+
|  1 |   10 |
|  2 |   20 |
| 33 |   30 |
+----+------+
3 rows in set (0.00 sec)
  • 普通的insert,insert之前,其他事务没有对next-record加任何锁


dba:lc_3> insert into tb_uk select 100,200;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0



锁的结构：


MySQL thread id 11888, OS thread handle 140000862643968, query id 24975 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601936 lock mode IX

没有加任何的锁，除了在表上面加了意向锁之外，这个锁基本上只要访问到表都会加的

难道insert不会加锁吗？显然不是，那是因为加的是隐式类型的锁
  • 有唯一键约束，insert之前，其他事务且对其next-record加了Gap-lock
* session 1:

select * from tb_uk where id_2 >= 30 for update;

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601951 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601951 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 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601951 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

锁住： (20,30](30,∞) ， 对30有Gap锁


* session 2:

dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (6.30 sec)
Records: 1  Duplicates: 0  Warnings: 0


* session 1:

rollback;


TABLE LOCK table `lc_3`.`tb_uk` trx id 133601952 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601952 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

当session2 插入25的时候，这时候session2 会被卡住。 然后session 2 释放gap lock后，session 1 就持有插入意向锁 lock_mode X locks gap before rec insert intention
  • 有唯一键约束，insert之前，其他事务且对其next-record加了record lock
* session 1:

dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 |   30 |
+----+------+
1 row in set (0.00 sec)


TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;


* session 2:

dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


锁结构：

说明有唯一键约束，insert之前，其他事务且对其next-record加了record lock，不会阻塞insert。

此时的insert，也不会产生insert intension lock
  • 有唯一键约束，insert 记录之后，发现原来的表有重复值的情况,

* session 1:

dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 |   30 |
+----+------+
1 row in set (0.00 sec)

dba:lc_3> delete from tb_uk where id_2 = 20;
Query OK, 1 row affected (0.00 sec)

这时候的锁结构如下：

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a97; asc       ;;
 2: len 7; hex 460000403f090b; asc F  @?  ;;
 3: len 4; hex 80000014; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

对二级索引uniq_idx ：
    1. 加record lock ， [20]，[30]

对主键索引：
    1. 加record lock，[2],[33]



* session 2:

dba:lc_3> insert into tb_uk select 3,20;
...............waiting.................


这时候，我们再来看看锁结构：

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601949 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601949 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

---TRANSACTION 133601943, ACTIVE 490 sec
3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 11889, OS thread handle 140000878618368, query id 25018 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a97; asc       ;;
 2: len 7; hex 460000403f090b; asc F  @?  ;;
 3: len 4; hex 80000014; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;


info bits 32 表示这条记录已经标记为删除状态

这里面的session 2 ： insert into tb_uk select 3,20; 被阻塞了
因为，这条insert 语句需要对 uniq_idx中的20加lock mode S ， 但是发现session 1 已经对其加了lock_mode X locks rec but not gap，而这条记录被标记为删除状态
所以发生锁等待，因为S lock 和 X lock 冲突
  • 没有唯一键约束,insert之前，其他事务对其next-record加了Gap-lock
* session 1:

dba:lc_3> select * from tb_non_uk where id_2>=100 for update;
+----+------+
| id | id_2 |
+----+------+
|  1 |  100 |
|  2 |  200 |
+----+------+
2 rows in set (0.00 sec)

锁结构：

TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a6b; asc      k;;
 2: len 7; hex a500000d360110; asc     6  ;;
 3: len 4; hex 800000c8; asc     ;;

对idx_id2二级索引： (100,200],(200,∞]
对主键索引： [2]

* session 2:

dba:lc_3> insert into tb_non_uk select 3,150;
......waiting.....

---TRANSACTION 133601940, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 11888, OS thread handle 140000862643968, query id 24996 localhost dba executing
insert into tb_non_uk select 3,150
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

------------------
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601940 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

---TRANSACTION 133601939, ACTIVE 311 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11889, OS thread handle 140000878618368, query id 24994 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a6b; asc      k;;
 2: len 7; hex a500000d360110; asc     6  ;;
 3: len 4; hex 800000c8; asc     ;;


锁结构：
    多了一个插入意向锁 lock_mode X locks gap before rec insert intention
  • 总结Insert 操作的加锁流程
* insert 的流程(没有唯一索引的情况)： insert N

1. 找到大于N的第一条记录M
2. 如果M上面没有gap ， next-key locking的话，可以插入  ， 否则等待  (对其next-rec加insert intension lock，由于有gap锁，所以等待)

* insert 的流程(有唯一索引的情况)： insert N

1. 找到大于N的第一条记录M，以及前一条记录P
2. 如果M上面没有gap ， next-key locking的话，进入第三步骤  ， 否则等待(对其next-rec加insert intension lock，由于有gap锁，所以等待)
3. 检查p：
    判断p是否等于n：
         如果不等: 则完成插入（结束）
         如果相等：
                再判断P 是否有锁，
                    如果没有锁:
                        报1062错误（duplicate key） --说明该记录已经存在，报重复值错误
                        加S-lock  --说明该记录被标记为删除, 事务已经提交，还没来得及purge
                    如果有锁: 则加S-lock  --说明该记录被标记为删除，事务还未提交.


* insert intension lock 有什么用呢？锁的兼容矩阵是啥？

1. insert intension lock 是一种特殊的Gap lock，记住非常特殊哦
2. insert intension lock 和 insert intension lock 是兼容的，其次都是不兼容的
3. Gap lock 是为了防止insert， insert intension lock 是为了insert并发更快，两者是有区别的
4. 什么情况下会出发insert intension lock ？
    当insert的记录M的 next-record 加了Gap lock才会发生，record lock并不会触发

实战案例

RR 隔离级别
最后来一个比较复杂的案例作为结束
通过这几个案例，可以复习下之前讲过的理论，锁不仅对主键加，还要考虑二级索引哦

  • 环境
set tx_isolation = 'repeatable-read';

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

 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)
  • 加锁语句
select * from a where c<9 for update;

锁结构：

TABLE LOCK table `lc_3`.`a` trx id 133601957 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601957 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 133601957 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 加锁 next-key lock： (-∞,5],(5,7],(7,9]
primary key 加锁 record lock： [1]和[3]

lock_update_1

  • 案例一 insert into a select 4,40,9,90

大家觉得能够插入成功吗？

dba:lc_3> insert into a select 4,40,9,90;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
...................waiting.................

显然是被锁住了

TABLE LOCK table `lc_3`.`a` trx id 133601961 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601961 lock_mode X locks gap before rec insert intention waiting
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     ;;

lock_update_2

  • 案例二 insert into a select 6,40,9,90;

大家觉得能够插入成功吗？


dba:lc_3> insert into a select 6,40,9,90;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

显然是插入成功了

lock_update_3

文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
数据库管理
算法
MySQL
关键词：
云数据库 RDS MySQL 版锁
mysql算法
云数据库 RDS MySQL 版算法
算法mysql
算法锁
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
兰春
目录
相关文章
daniel_2000
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
daniel_2000
52 3
mao29955
|
26天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素，根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进，可以提高数据库的并发性能，提升系统的整体效率。
mao29955
47 1
猿java
|
1月前
|
关系型数据库 MySQL Java
MySQL数据锁：Record Lock，Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎，深入解析三种行锁机制：记录锁（Record Lock）、间隙锁（Gap Lock）和临键锁（Next-key Lock）。记录锁锁定索引记录，确保事务唯一修改；间隙锁锁定索引间的间隙，防止新记录插入；临键锁结合两者，锁定范围并记录自身，有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
猿java
106 2
猿java
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库锁：共享锁和独占锁
本文详细介绍了`InnoDB`存储引擎中的两种行级别锁：共享锁（S锁）与排他锁（X锁）。通过具体示例展示了这两种锁的工作机制及其在`InnoDB`与`MyISAM`引擎中的表现差异。文章还提供了锁的兼容性矩阵，帮助读者更好地理解锁之间的互斥关系。最后总结了两种锁的特点及适用场景。适合希望深入了解`MySQL`并发控制机制的读者阅读。
猿java
40 1
mao29955
|
26天前
|
存储 关系型数据库 MySQL
MySQL锁，锁的到底是什么？
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象，其目的是为了保证数据的一致性、避免冲突，并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
mao29955
54 0
糜终
|
2月前
|
监控 关系型数据库 MySQL
MySQL锁机制与解决死锁问题
MySQL锁机制与解决死锁问题
糜终
267 5
蓝易云
|
28天前
|
关系型数据库 MySQL 数据库
mysql锁详解
通过理解并合理运用MySQL中的锁机制，开发者可以有效管理数据库并发访问，平衡性能与数据一致性需求。更多关于MySQL锁的深入探讨和最佳实践，请参考专业的数据库管理资源[[深入MySQL锁机制详解
蓝易云
32 0
dlwlrma-晴子
|
2月前
|
关系型数据库 MySQL 数据库
Mysql的锁
本文介绍了MySQL中表级锁和行级锁的区别，其中MyISAM仅支持表级锁，而InnoDB支持表级锁和行级锁，默认为行级锁。表级锁锁定整个表，实现简单，资源消耗少，但并发度低；行级锁仅锁定相关记录，减少冲突，提高并发度，但加锁开销大。此外，还介绍了共享锁和排他锁的概念及意向锁的作用。
dlwlrma-晴子
52 1
古明地盆
|
2月前
|
存储 SQL 关系型数据库
MySQL 的锁机制，那么多的锁，该怎么区分？
MySQL 的锁机制，那么多的锁，该怎么区分？
古明地盆
38 0
游客mldfis24krfue
|
3月前
|
监控 关系型数据库 MySQL
在Linux中，mysql的innodb如何定位锁问题？
在Linux中，mysql的innodb如何定位锁问题？
游客mldfis24krfue
114 2

热门文章

最新文章

  • 1
    线性排序算法（1）
  • 2
    【推荐算法】商品推荐_3041
  • 3
    [干货]基础机器学习算法
  • 4
    泛型KMP算法
  • 5
    《操作系统》—— 处理机调度算法
  • 6
    算法学习之顺序结构
  • 7
    【算法】算法之美—Fire Net
  • 8
    OneMob（一体传媒）：大数据算法 让营销更精准
  • 9
    A* + 弗洛伊德 算法 打造 360 向寻路 - [ActionScript3.0]
  • 10
    【数据结构与算法】一篇文章彻底搞懂二分查找（思路图解+代码优化）两种实现方式，递归与非递归
  • 1
    中间件MySQL性能瓶颈
    57
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    292
  • 3
    MySQL上亿数据查询优化：实践与技巧
    291
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    50
  • 5
    MySQL：union all与union区别详解
    89
  • 6
    学习mysql中使用inner join，left join 等
    50
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    92
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    140
  • 10
    【实操记录】MySQL二进制安装包部署
    192

    • 相关课程

    更多
  • Go语言核心编程 - 数据结构和算法
  • MySQL高级应用 - 索引和锁
  • Linux MySQL服务器搭建与应用
  • MySQL数据库入门学习
  • MySQL完全自学手册
  • 神经网络概览及算法详解

    • 相关电子书

    更多
  • 数据+算法定义新世界
  • 袋鼠云基于实时计算的反黄牛算法
  • Alink：基于Apache Flink的算法平台

    • 相关实验场景

    更多
  • 推荐系统入门之使用ALS算法实现打分预测
  • MyBase MySQL实例资源灵活弹配
  • 如何开通MySQL的专属集群
  • 欧拉图的构造性证明与算法实现
  • 云端MySQL的安装及应用
  • RSA密码算法设计与实现

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云OSS设置跨域访问