1
innodb存储引擎锁的实现(三) - 知乎.pdf
免费下载
innodb存储引擎锁的实现(三)
2 人赞同了该文章
上期《innodb存储引擎锁的实现(二)》中，我们已经介绍了部分关于Next-Key Lock的知识，本期将接着为大
家介绍Next-Key Lock。
如果是select * from t where xid>3 for update; 又是怎么样的呢？
root@localhost : test 03:25:06> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-----
--------+----------------+-------------------+-----------------+-----------------------+-------
----+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA |
OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN |
LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-----
--------+----------------+-------------------+-----------------+-----------------------+-------
----+-----------+-------------+------------------------+
| INNODB | 2100:1059 | 2100 | 65 | 46 | test | t | NULL | NULL | NULL | 139846618246760 | TABLE
| IX | GRANTED | NULL |
| INNODB | 2100:2:5:1 | 2100 | 65 | 46 | test | t | NULL | NULL | xid | 139846618243720 |
RECORD | X | WAITING | supremum pseudo-record |
| INNODB | 2099:1059 | 2099 | 67 | 28 | test | t | NULL | NULL | NULL | 139846618240760 | TABLE
| IX | GRANTED | NULL |
| INNODB | 2099:2:5:6 | 2099 | 67 | 28 | test | t | NULL | NULL | xid | 139846618237880 |
RECORD | X,GAP | WAITING | 9, 0x000000000205 |
| INNODB | 2098:1059 | 2098 | 66 | 40 | test | t | NULL | NULL | NULL | 139846618234776 | TABLE
| IX | GRANTED | NULL |
| INNODB | 2098:2:5:5 | 2098 | 66 | 40 | test | t | NULL | NULL | xid | 139846618231848 |
RECORD | X,GAP | WAITING | 7, 0x000000000203 |
| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE
| IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX |
139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX |
139846618226128 | RECORD | X | GRANTED | 0x000000000205 |
+--------+----------------+-----------------------+-----------+----------+---------------+-----
--------+----------------+-------------------+-----------------+-----------------------+-------
----+-----------+-------------+------------------------+
12 rows in set (0.00 sec)
事务A造成的锁
| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE
| IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | supremum pseudo-record |
这里可以看到与之前的不同，上确界添加了X锁，由于使用 select * from t where xid>3 for update; 该
表的xid的上确界为9，它需要将9到正无穷也要锁住，supremum pseudo-record 上确界伪记录
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 |
RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX |
139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX |
139846618226128 | RECORD | X | GRANTED | 0x000000000205 |
下图所示：
of 8
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论