MySQL锁系列（二）之锁解读

背景

锁系列第一期的时候介绍的锁，我们要如何去解读呢？
在哪里能够看到这些锁？

锁信息解读

工欲善其事必先利其器
show engine innodb status 关于锁的信息是最详细的

案例一（有索引的情况）

前期准备


dba:lc_3>
dba:lc_3>
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>
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)

产生锁的语句

dba:lc_3> set tx_isolation = 'repeatable-read';  --事务隔离级别为repeatable-read，以后介绍
Query OK, 0 rows affected (0.00 sec)

begin;
select * from a where c=7 for update;

show engine innodb status


------------
TRANSACTIONS
------------
Trx id counter 133588132
Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle
History list length 836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588131, ACTIVE 8 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 116, OS thread handle 140001238423296, query id 891 localhost dba cleaning up
TABLE LOCK table `lc_3`.`a` trx id 133588131 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X
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 133588131 lock_mode X locks rec but not gap
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     ;;

RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X locks gap before rec
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     ;;

show engine innodb status 解读

* Trx id counter 133588132

描述的是：下一个事务的id为133588132

* Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle

Purge线程已经将trxid小于133588131的事务都purge了，目前purge线程的状态为idle
Purge线程无法控制

* History list length 836

undo中未被清除的事务数量，如果这个值非常大，说明系统来不及回收undo，需要人工介入了。

疑问：上面的purge都已经刷新完了，为什么History list length 不等于0，这是一个有意思的问题

* ---TRANSACTION 133588131, ACTIVE 8 sec

当前事务id为133588131

* 4 lock struct(s), heap size 1136, 3 row lock(s)

产生了4个锁对象结构，占用内存大小1136字节，3条记录被锁住(1个表锁，3个记录锁)

* TABLE LOCK table `lc_3`.`a` trx id 133588131 lock mode IX

在a表上面有一个表锁，这个锁的模式为IX（排他意向锁）

* RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X

在space id=281（a表的表空间），page no=5的页上，对表a上的idx_c索引加了记录锁，锁模式为：next-key 锁（这个在上一节中有告知）
该页上面的位图锁占有72bits

* 具体锁了哪些记录

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   -- heap no 3 的记录被锁住了
 0: len 4; hex 80000007; asc     ;;  --这是一个二级索引上的锁，7被锁住
 1: len 4; hex 80000003; asc     ;;  --二级索引上面还会自带一个主键，所以主键值3也会被锁住

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133588131 lock_mode X locks rec but not gap（这是一个记录锁，在主键上锁住的）
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;  --第一个字段是主键3，占用4个字节，被锁住了
 1: len 6; hex 000007f66444; asc     dD;;  --该字段为6个字节的事务id，这个id表示最近一次被更新的事务id
 2: len 7; hex fc0000271d011d; asc    '   ;; --该字段为7个字节的回滚指针，用于mvcc
 3: len 4; hex 80000005; asc     ;;  --该字段表示的是此记录的第二个字段5
 4: len 4; hex 80000007; asc     ;;  --该字段表示的是此记录的第三个字段7
 5: len 4; hex 80000009; asc     ;;  --该字段表示的是此记录的第四个字段9

RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;; --这是一个二级索引上的锁，9被锁住
 1: len 4; hex 80000005; asc     ;; --二级索引上面还会自带一个主键，所以主键值5被锁住

案例二(无索引的情况)

前期准备


dba:lc_3> show create table t;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dba:lc_3> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
|    5 |
+------+
16 rows in set (0.00 sec)

产生锁语句


dba:lc_3> set tx_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)

dba:lc_3> select * from t where i=1 for update;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

show engine innodb status

------------
TRANSACTIONS
------------
Trx id counter 133588133
Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle
History list length 836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588132, ACTIVE 6 sec
2 lock struct(s), heap size 1136, 17 row lock(s)
MySQL thread id 118, OS thread handle 140001238955776, query id 904 localhost dba cleaning up
TABLE LOCK table `lc_3`.`t` trx id 133588132 lock mode IX
RECORD LOCKS space id 278 page no 3 n bits 88 index GEN_CLUST_INDEX of table `lc_3`.`t` trx id 133588132 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 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff05; asc       ;;
 1: len 6; hex 000007f66397; asc     c ;;
 2: len 7; hex fb0000271c0110; asc    '   ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff06; asc       ;;
 1: len 6; hex 000007f663ea; asc     c ;;
 2: len 7; hex bb000027340110; asc    '4  ;;
 3: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff07; asc       ;;
 1: len 6; hex 000007f66426; asc     d&;;
 2: len 7; hex e4000040210110; asc    @!  ;;
 3: len 4; hex 80000003; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff08; asc       ;;
 1: len 6; hex 000007f66427; asc     d';;
 2: len 7; hex e5000040220110; asc    @"  ;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff09; asc       ;;
 1: len 6; hex 000007f6642c; asc     d,;;
 2: len 7; hex e8000040230110; asc    @#  ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0a; asc       ;;
 1: len 6; hex 000007f6642d; asc     d-;;
 2: len 7; hex e9000040240110; asc    @$  ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0b; asc       ;;
 1: len 6; hex 000007f66432; asc     d2;;
 2: len 7; hex ec0000273f0110; asc    '?  ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0c; asc       ;;
 1: len 6; hex 000007f66433; asc     d3;;
 2: len 7; hex ed000040020110; asc    @   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0d; asc       ;;
 1: len 6; hex 000007f66434; asc     d4;;
 2: len 7; hex ee000040030110; asc    @   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0e; asc       ;;
 1: len 6; hex 000007f66435; asc     d5;;
 2: len 7; hex ef000040040110; asc    @   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff0f; asc       ;;
 1: len 6; hex 000007f66436; asc     d6;;
 2: len 7; hex f0000040050110; asc    @   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff10; asc       ;;
 1: len 6; hex 000007f66437; asc     d7;;
 2: len 7; hex f1000040060110; asc    @   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff11; asc       ;;
 1: len 6; hex 000007f66438; asc     d8;;
 2: len 7; hex f2000027130110; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff12; asc       ;;
 1: len 6; hex 000007f66439; asc     d9;;
 2: len 7; hex f3000027140110; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 16 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff13; asc       ;;
 1: len 6; hex 000007f6643a; asc     d:;;
 2: len 7; hex f4000027150110; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 17 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dff14; asc       ;;
 1: len 6; hex 000007f6643b; asc     d;;;
 2: len 7; hex f5000027160110; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;

锁解读

1. 这里只列出跟第一个案例不同的地方解读，其他的都一样

2. RECORD LOCKS space id 278 page no 3 n bits 88 index GEN_CLUST_INDEX of table `lc_3`.`t` trx id 133588132 lock_mode X

    由于表定义没有显示的索引，而InnoDB又是索引组织表，会自动创建一个索引，这里面叫index GEN_CLUST_INDEX

3. 由于没有索引，那么会对每条记录都加上lock_mode X （next-key lock）

4. 这里有一个明显不一样的是：
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;

supremum 值得是页里面的最后一条记录(伪记录，通过select查不到的,并不是真实的记录)，heap no=1 , Infimum 表示的是页里面的第一个记录（伪记录）

可以简单的认为：
    supremum 为upper bounds，正去穷大
    Infimum 为Minimal bounds，负无穷大

那这里的加锁的意思就是：通过supremum 锁住index GEN_CLUST_INDEX的最大值到正无穷大的区间，这样就可以锁住全部记录，以及全部间隙，相当于表锁

锁开销

锁10条记录和锁1条记录的开销是成正比的吗？

1. 由于锁的内存对象针对的是页而不是记录，所以开销并不是非常大
2. 锁10条记录和锁1条记录的内存开销都是一样的，都是heap size=1136个字节

最后

这里面select * from a where c=7 for update; 明明只锁一条记录，为什么却看到4把锁呢？
看到这里是不是有点晕，没关系，这个问题，后面会慢慢揭晓答案

时间： 2024-10-02 04:38:16

MySQL锁系列（二）之锁解读的相关文章

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

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

MySQL锁系列（八）之死锁

能学到什么什么是死锁死锁有什么危害典型的死锁案例剖析如何避免死锁一.什么是死锁 1.必须满足的条件 1. 必须有两个或者两个以上的事务 2. 不同事务之间都持有对方需要的锁资源. A事务需要B的资源,B事务需要A的资源,这就是典型的AB-BA死锁 2.死锁相关的参数 * innodb_print_all_deadlocks 1. 如果这个参数打开,那么死锁相关的信息都会打印输出到error log * innodb_lock_wait_timeout 1. 当MySQL获取row lo

MySQL锁系列（一）之锁的种类和概念

背景锁是MySQL里面最难理解的知识,但是又无处不在. 一开始接触锁的时候,感觉被各种锁类型和名词弄得晕头转向,就别说其他了. 本文是通过DBA的视角(非InnoDB内核开发)来分析和窥探锁的奥秘,并解决实际工作当中遇到的问题锁的种类&概念想要啃掉这块最难的大骨头,必须先画一个框架,先了解其全貌,才能逐个击破 Shared and Exclusive Locks * Shared lock: 共享锁,官方描述:permits the transaction that holds the l

MySQL行级锁、表级锁、页级锁详细介绍_Mysql

页级:引擎 BDB.表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行行级:引擎 INNODB , 单独的一行记录加锁表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作.如果你是写锁,则其它进程则读也不允许行级,,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作.页级,表级锁速度快,但冲突多,行级冲突少,但速度慢.所以取了折衷的页级,一次锁定相邻的一组记录. MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行

MySQL中的行级锁、表级锁、页级锁_Mysql

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足. 在DBMS中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎).表级锁(MYISAM引擎)和页级锁(BDB引擎 ). 一.行级锁行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁.行级锁能大大减少数据库操作的冲突.其加锁粒度最小,但加锁的开销也最大.行级锁分为共享锁和排他锁. 特点开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也

【锁】Oracle锁系列

[锁]Oracle锁系列 1 BLOG文档结构图 2 前言部分 2.1 导读和注意事项各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 锁的概念.分类.及其模拟 ② 查询锁的视图及视图之间的关联 ③ 锁的参数(DML_LOCKS.DDL_LOCK_TIMEOUT) ④ FOR UPDATE及FOR UPDATE OF系列 ⑤ 带ONLINE和不带ONLINE创建索引的锁情况(是否阻塞DML操作) ⑥ 包或存

MySQL详解－－锁，事务

http://www.cnblogs.com/jukan/p/5670950.html http://blog.csdn.net/xifeijian/article/details/20313977 锁是计算机协调多个进程或线程并发访问某一资源的机制.在数据库中,除传统的计算资源(如CPU.RAM.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素.从这个角度来说,锁对数据

Java多线程：“JUC锁”04之公平锁(二)

概要前面一章,我们学习了"公平锁"获取锁的详细流程:这里,我们再来看看"公平锁 "释放锁的过程.内容包括: 参考代码释放公平锁(基于JDK1.7.0_40) "公平锁"的获取过程请参考"Java多线程系列--"JUC锁"03之公平锁 (一)",锁的使用示例请参考"Java多线程系列--"JUC锁"02之互斥锁 ReentrantLock". 注意: (01)

MySQL中的事务和锁简单用法测试

一直以来,对于MySQL中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是有一些收获. 首先我们初始化数据,事务的隔离级别还是MySQL默认的RR,存储引擎为InnoDB > create table test(id int,name varchar(30)); > insert into test values(1,'aa'); 开启一个会话,开启事务.会话1: [test]>start transa

MySQL锁系列（二）之 锁解读

背景

锁信息解读

案例一（有索引的情况）

案例二(无索引的情况)

锁开销

最后

MySQL锁系列（二）之 锁解读的相关文章

MySQL锁系列（二）之锁解读

MySQL锁系列（二）之锁解读的相关文章