- 浏览: 144233 次
- 性别:
- 来自: 北京
文章分类
最新评论
阅读原文请点击: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/
摘要: 能学到什么 隔离级别和锁的关系 重点讲解在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/
相关推荐
在MySQL 5.5版本中,出现了一系列重要的新特性,这些新特性主要关注性能提升、安全性增强和系统配置参数的动态调整。以下是对这些新特性及参数优化的知识点的详细介绍: 1. 性能上的显著改变: a. 默认存储引擎的...
### MySQL数据库性能监控与诊断详解 在IT领域,特别是对于依赖于数据库的现代应用程序而言,数据库性能监控与诊断是确保系统稳定性和响应速度的关键环节。本文将深入探讨MySQL数据库性能监控与诊断的相关知识点,...
### MySQL Key Cache 优化详解 在MySQL数据库管理系统中,针对不同的存储引擎,有着不同的缓存机制来提升查询性能。本文将重点围绕MyISAM表类型的key cache进行详细讲解,并涉及InnoDB存储引擎的相关特性,以及...
### MySQL常见面试题及答案详解 #### 一、MySQL基础知识 **1. MySQL 是什么?** MySQL 是一种开源的关系型数据库管理系统(RDBMS),它最初由瑞典的 MySQL AB 公司开发,随后被 Sun Microsystems 收购,并最终...
然而,InnoDB引擎是支持ACID(原子性、一致性、隔离性、持久性)事务的,提供了行级锁和并发控制,这使得它成为处理复杂业务逻辑和高并发场景的理想选择。Berkeley DB也是一个支持事务的存储引擎,但其使用情况相对...
MySQL是世界上最受欢迎的关系型数据库管理系统之一,尤其在Web应用程序中被广泛应用。对于后端开发者来说,熟练掌握MySQL是必备技能,而面试时,面试官往往会针对MySQL提出一系列问题来评估候选人的技术水平。以下是...
【MySQL8.0新特性详解】 MySQL 8.0是一个重要的里程碑版本,它带来了一系列显著的改进和增强,旨在提供更高的性能、更好的用户体验以及更强的安全性。以下是对这些新特性的详细解读: 1. **更简便的NoSQL支持**: ...
内置模块详解之time与datetime模块 内置模块详解之Range模块 内置模块详解之OS模块 内置模块详解之Sys模块 内置模块详解之Shelve模块 内置模块详解之Xml模块 内置模块详解之Configparser模块 内置模块详解之Hashlib...
【压缩包内容】:这个压缩包可能包含了一系列与BAT面试相关的文档,涵盖了算法、数据结构、操作系统、计算机网络、数据库、编程语言、项目经验、团队协作、产品理解、商业分析等多个方面的知识。 **一、算法与数据...
数据结构与算法之美 基础篇 go k8s实战 go MySQL系列 React 系列 深入浅出redis go—interview 最近在读编程珠玑 最近在读编程珠玑,打算每天做一些题目放上来,持续更新 go-高级编程 go-设计模式 深入理解计算机...
一、面试题涉及的知识点详解: 1. **Zookeeper在项目中的使用及原理**: Zookeeper是一个分布式协调服务,常用于管理分布式应用中的配置信息、命名服务、分布式同步、组服务等。它通过一致性算法保证数据的一致性...
- MySQL中的悲观锁通常通过行级锁实现,如`SELECT ... FOR UPDATE`语句。 6. **MySQL的乐观锁机制** - MySQL中的乐观锁通常通过版本号或时间戳来实现。在更新记录时检查版本号是否发生变化来判断数据是否被其他...
MySQL中实现悲观锁与乐观锁** - **悲观锁**:通过行级锁或表级锁实现。 - **乐观锁**:一般通过版本号字段实现。 **20. 事务隔离级别与Spring中的事务传播行为** MySQL支持四种隔离级别:READ UNCOMMITTED、READ...
- **策略模式**: 定义一系列的算法,把它们一个个封装起来,并且使它们可相互替换。 #### 五、Spring框架核心原理 - **依赖注入(DI)**: 一种编程实现,通过构造函数、setter方法或字段注入等方式将一个对象所依赖...
2. **计网七层协议详解** - **知识点**:理解OSI七层模型的基本概念及每一层的主要功能。 - **解释**:OSI模型定义了网络通信的七个层次,从物理层到应用层,每个层次都有特定的责任和功能。 3. **线程与进程的...
【AAAAA AAA 机试试题】相关知识点详解 在信息技术领域,"机试试题"通常指的是计算机编程或技术相关的在线测试题目,旨在评估应聘者或学生的技术能力、问题解决能力和编程技能。这些试题可能涵盖各种编程语言(如...
《C++仓库管理系统课程设计详解》 在信息技术领域,C++是一种广泛应用的编程语言,尤其在系统软件、应用软件和游戏开发中占有重要地位。本篇文章将深入探讨一个基于C++实现的仓库管理系统的设计与实现,旨在帮助...