InnoDB undo log解析（二）

最新推荐文章于 2022-11-19 15:07:36 发布

湉湉老爸

最新推荐文章于 2022-11-19 15:07:36 发布

阅读量435

点赞数

分类专栏：数据库

数据库专栏收录该内容

57 篇文章 0 订阅

订阅专栏

http://insidemysql.blog.163.com/blog/static/202834042201341011347494/

在上一篇中已经介绍了InnoDB undo log的组织结构，并通过一个示例并结合InnoSQL来分析insert undo log记录格式。本篇中介绍update undo log的记录格式。update undo log有以下三种类型：

类型	十六进制值	说明
TRX_UNDO_UPD_EXIST_REC	0x0c	更新一个not delete mark的记录
TRX_UNDO_UPD_DEL_REC	0x0d	更新一个delete mark记录
TRX_UNDO_DEL_MARK_REC	0x0e	将记录标记为delete mark

接着来看一个具体的例子，首先根据如下清单创建测试表t并导入测试数据：


<p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"><span class="pln" style="color: rgb(0, 0, 0);">CREATE TABLE t </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="pln" style="color: rgb(0, 0, 0);"> a INT</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> b VARCHAR</span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">10</span><span class="pun" style="color: rgb(102, 102, 0);">),</span><span class="pln" style="color: rgb(0, 0, 0);"> c INT</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> PRIMARY KEY</span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="pln" style="color: rgb(0, 0, 0);">a</span><span class="pun" style="color: rgb(102, 102, 0);">),</span><span class="pln" style="color: rgb(0, 0, 0);"> KEY</span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="pln" style="color: rgb(0, 0, 0);">b</span><span class="pun" style="color: rgb(102, 102, 0);">));</span></p><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"><span class="pln" style="color: rgb(0, 0, 0);">INSERT INTO t SELECT </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="str" style="color: rgb(0, 136, 0);">'1'</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></p><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

接着运行下面的事务，注意不要提交事务：


<p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div><pre style="white-space: pre-wrap; word-wrap: break-word;"><div><span class="kwd" style="color: rgb(0, 0, 136);">BEGIN</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">DELETE FROM t WHERE a</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);">  SELECT </span><span class="pun" style="color: rgb(102, 102, 0);">*</span><span class="pln" style="color: rgb(0, 0, 0);"> FROM information_schema</span><span class="pun" style="color: rgb(102, 102, 0);">.</span><span class="pln" style="color: rgb(0, 0, 0);">INNODB_TRX_UNDO\G</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="pun" style="color: rgb(102, 102, 0);">***************************</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1.</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="pun" style="color: rgb(102, 102, 0);">***************************</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       trx_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">303</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      rseg_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">4</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">  undo_rec_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">undo</span><span class="pln" style="color: rgb(0, 0, 0);">_rec_type</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> TRX_UNDO_DEL_MARK_REC</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">         size</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">37</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">        space</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      page_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">308</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       offset</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">272</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="kwd" style="color: rgb(0, 0, 136);">in</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">set</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div></div><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

打开共享表空间ibdata1并定位到page_no:308 offset：272的位置，得到如下的内容：


  
  
   
   <p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div><span class="lit" style="color: rgb(0, 102, 102);">004d4110</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><strong><span class="lit" style="color: rgb(0, 102, 102);">01</span></strong><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">35</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0e</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0d</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">03</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">02</span><span class="pln" style="color: rgb(0, 0, 0);"> e0 </span><span class="lit" style="color: rgb(0, 102, 102);">83</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="pun" style="color: rgb(102, 102, 0);">|.</span><span class="lit" style="color: rgb(0, 102, 102);">5.</span><span class="pun" style="color: rgb(102, 102, 0);">.............|</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">004d4120</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="lit" style="color: rgb(0, 102, 102);">33</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">10</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">04</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">80</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0b</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">04</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">80</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="pun" style="color: rgb(102, 102, 0);">|</span><span class="lit" style="color: rgb(0, 102, 102);">3.</span><span class="pun" style="color: rgb(102, 102, 0);">..............|</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">004d4130</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="lit" style="color: rgb(0, 102, 102);">03</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">31</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">01</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><strong><span class="lit" style="color: rgb(0, 102, 102);">10</span></strong><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">00</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="pun" style="color: rgb(102, 102, 0);">|..</span><span class="lit" style="color: rgb(0, 102, 102);">1.</span><span class="pun" style="color: rgb(102, 102, 0);">............|</span></div><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

整理后可得：

十六进值	说明
01 35	undo log结束位置
0e	undo log类型，TRX_UNDO_DEL_MARK_REC
00	记录的info bit信息
0d	表的ID
00 00 00 03 02 e0	记录的隐藏事务ID列
83 00 00 01 33 01 10	记录的隐藏回滚指针列
04	主键长度
80 00 00 01	主键值（a=1）
00 0b	之后部分的字节数
00	列的ID（列a）
04	列占用的字节数
80 00 00 01	列的值（a=1）
03	列的ID（列b）
01	列占用的字节数
31	列的值（b=‘1’）
01 10	undo log开始位置（0x0135-0x0110=37）

回滚事务，接着运行下面的例子：


   
   
    
    <p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">BEGIN</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span><span class="pln" style="color: rgb(0, 0, 0);"> rows affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><div>
</div><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> UPDATE t SET c</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="lit" style="color: rgb(0, 102, 102);">2</span><span class="pln" style="color: rgb(0, 0, 0);"> WHERE a</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Rows</span><span class="pln" style="color: rgb(0, 0, 0);"> matched</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Changed</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Warnings</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div>
</div><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> SELECT </span><span class="pun" style="color: rgb(102, 102, 0);">*</span><span class="pln" style="color: rgb(0, 0, 0);"> FROM information_schema</span><span class="pun" style="color: rgb(102, 102, 0);">.</span><span class="pln" style="color: rgb(0, 0, 0);">INNODB_TRX_UNDO\G</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="pun" style="color: rgb(102, 102, 0);">***************************</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1.</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="pun" style="color: rgb(102, 102, 0);">***************************</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       trx_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">308</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      rseg_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">7</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">  undo_rec_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">undo</span><span class="pln" style="color: rgb(0, 0, 0);">_rec_type</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> TRX_UNDO_UPD_EXIST_REC</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">         size</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">33</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">        space</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      page_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">310</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       offset</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">272</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="kwd" style="color: rgb(0, 0, 136);">in</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">set</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

按上述同样的方法整理undo log后可得：

十六进值	说明
01 31	undo log结束位置
1c	undo log类型，TRX_UNDO_UPD_EXIST_REC
0d	表的ID
00	记录的info bit信息
00 00 00 03 02 e0	记录的隐藏事务ID列
83 00 00 01 33 01 10	记录的隐藏回滚指针列
04	主键长度
80 00 00 01	主键值（a=1）
01	update vector 的数量
04	update vector保存的列ID（列c）
04	列占用的字节数
80 00 00 01	列的值（a=1）
01 10	undo log开始位置（0x0135-0x0110=33）

这里undo log的类型为1c而不是0c这是因为这部分还保存了其他信息，这里1表示更新操作没有更新其他索引列。若更新辅助索引列b，如：


  
  
   
   <p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">BEGIN</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span><span class="pln" style="color: rgb(0, 0, 0);"> rows affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><div>
</div><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> UPDATE t SET b</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="str" style="color: rgb(0, 136, 0);">'222'</span><span class="pln" style="color: rgb(0, 0, 0);"> WHERE a</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><div><span class="typ" style="color: rgb(102, 0, 102);">Rows</span><span class="pln" style="color: rgb(0, 0, 0);"> matched</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Changed</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Warnings</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div>
</div><div><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> SELECT </span><span class="pun" style="color: rgb(102, 102, 0);">*</span><span class="pln" style="color: rgb(0, 0, 0);"> FROM information_schema</span><span class="pun" style="color: rgb(102, 102, 0);">.</span><span class="pln" style="color: rgb(0, 0, 0);">INNODB_TRX_UNDO\G</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></div><div><span class="pun" style="color: rgb(102, 102, 0);">***************************</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1.</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="pun" style="color: rgb(102, 102, 0);">***************************</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       trx_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">30A</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      rseg_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">8</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">  undo_rec_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">undo</span><span class="pln" style="color: rgb(0, 0, 0);">_rec_type</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> TRX_UNDO_UPD_EXIST_REC</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">         size</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">41</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">        space</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">      page_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">311</span></div><div><span class="pln" style="color: rgb(0, 0, 0);">       offset</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">272</span></div><div><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="kwd" style="color: rgb(0, 0, 136);">in</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">set</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></div><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

可以看到这时undo log的类型还是 TRX_UNDO_UPD_EXIST_REC，但可以发现这时undo type的值为0c而非1c。此外，由于更新了列b，update vector会保存更新时记录列b的值，因此两次操作产生的undo log的量也不同。

何时会产生 TRX_UNDO_UPD_DEL_REC的undo log呢？这个类型表明是在delete mark的记录上进行更新，但是若事务已经提交则delete mark的记录是不允许更改的，其会等待purge线程进行删除。因此产生该类型的undo log发生应发生在同一事务中，如下面的情况：


   
   
    
    <p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">BEGIN</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span><span class="pln" style="color: rgb(0, 0, 0);"> rows affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;">
</span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> DELETE FROM t </span><span class="kwd" style="color: rgb(0, 0, 136);">where</span><span class="pln" style="color: rgb(0, 0, 0);"> a</span><span class="pun" style="color: rgb(102, 102, 0);">=</span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;">
</span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);"> INSERT INTO t SELECT </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="str" style="color: rgb(0, 136, 0);">'2'</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="lit" style="color: rgb(0, 102, 102);">2</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="typ" style="color: rgb(102, 0, 102);">Query</span><span class="pln" style="color: rgb(0, 0, 0);"> OK</span><span class="pun" style="color: rgb(102, 102, 0);">,</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);"> row affected </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="typ" style="color: rgb(102, 0, 102);">Records</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Duplicates</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span><span class="pln" style="color: rgb(0, 0, 0);">  </span><span class="typ" style="color: rgb(102, 0, 102);">Warnings</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;">
</span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">mysql</span><span class="pun" style="color: rgb(102, 102, 0);">></span><span class="pln" style="color: rgb(0, 0, 0);">  SELECT </span><span class="pun" style="color: rgb(102, 102, 0);">*</span><span class="pln" style="color: rgb(0, 0, 0);"> FROM information_schema</span><span class="pun" style="color: rgb(102, 102, 0);">.</span><span class="pln" style="color: rgb(0, 0, 0);">INNODB_TRX_UNDO ORDER BY undo_rec_no\G</span><span class="pun" style="color: rgb(102, 102, 0);">;</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pun" style="color: rgb(102, 102, 0);">***************************</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1.</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="pun" style="color: rgb(102, 102, 0);">***************************</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">       trx_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">30E</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">      rseg_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">10</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">  undo_rec_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="lit" style="color: rgb(0, 102, 102);">undo</span><span class="pln" style="color: rgb(0, 0, 0);">_rec_type</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> TRX_UNDO_DEL_MARK_REC</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">         size</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">37</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">        space</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">      page_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">314</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">       offset</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">272</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pun" style="color: rgb(102, 102, 0);">***************************</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">2.</span><span class="pln" style="color: rgb(0, 0, 0);"> row </span><span class="pun" style="color: rgb(102, 102, 0);">***************************</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">       trx_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">30E</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">      rseg_id</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">10</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">  undo_rec_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">1</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="lit" style="color: rgb(0, 102, 102);">undo</span><span class="pln" style="color: rgb(0, 0, 0);">_rec_type</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> TRX_UNDO_UPD_DEL_REC</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">         size</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">46</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">        space</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">0</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">      page_no</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">314</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="pln" style="color: rgb(0, 0, 0);">       offset</span><span class="pun" style="color: rgb(102, 102, 0);">:</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="lit" style="color: rgb(0, 102, 102);">309</span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53;"><span class="lit" style="color: rgb(0, 102, 102);">2</span><span class="pln" style="color: rgb(0, 0, 0);"> rows </span><span class="kwd" style="color: rgb(0, 0, 136);">in</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="kwd" style="color: rgb(0, 0, 136);">set</span><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="pun" style="color: rgb(102, 102, 0);">(</span><span class="lit" style="color: rgb(0, 102, 102);">0.00</span><span class="pln" style="color: rgb(0, 0, 0);"> sec</span><span class="pun" style="color: rgb(102, 102, 0);">)</span></span></div><p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p>

可以看到事务的第二条SQL语句再次插入了主键值为1的记录，并且记录的大小没有发生变化，这意味着可以重用之前已经删除的部分，仅需对其他列的部分进行更新即可，而这就会产生 TRX_UNDO_UPD_DEL_REC的undo log。整理该undo log，最后可得：

十六进值	说明
01 63	undo log结束位置
0d	undo log类型，TRX_UNDO_UPD_EXIST_REC
01	记录的info bit信息(1表示记录已经被delete mark）
0d	表的ID
00 00 00 03 0e ca	记录的隐藏事务ID列
00 00 01 33 01 10	记录的隐藏回滚指针列
04	主键长度
80 00 00 01	主键值（a=1）
02	update vector 的数量
03	update vector保存的列ID（列b）
01	列占用的字节数
31	列的值（b='1'）
04	update vector保存的列ID（列c）
04	列占用的字节数
80 00 00 01	列的值（c=1）
00 0b	之后部分的字节数
00	列的ID
04	列占用的字节数
80 00 00 01	列的值（a=1）
03	列的ID
01	列占用的字节数
31	列的值（c=‘1’）
01 35	开始位置

可以看到在某些情况下即便用过数据结构update vector记录发生变化的列，但还是需要记录发生更改的索引列的信息。源码中给出了明确的答案：


<p style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px;"></p><div data-find="_2"><pre data-find="_1" style="white-space: pre-wrap; word-wrap: break-word;"><div><span class="pln" style="color: rgb(0, 0, 0);"> </span><span class="com" style="color: rgb(136, 0, 0);">/* In the case of a delete marking, and also in the case of an update</span></div><div><span class="com" style="color: rgb(136, 0, 0);"> where any ordering field of any index changes, store the values of all</span></div><div><span class="com" style="color: rgb(136, 0, 0);"> columns which occur as ordering fields in any index. This info is used</span></div><div><span class="com" style="color: rgb(136, 0, 0);"> in the purge of old versions where we use it to build and search the</span></div><div><span class="com" style="color: rgb(136, 0, 0);"> delete marked index records, to look if we can remove them from the</span></div><div><span class="com" style="color: rgb(136, 0, 0);"> index tree. */</span></div>

呼~~~ update undo log分析完了。可以尝试从源码文件trx0rec.c中的函数trx_undo_page_report_modify得到更为直接的答案。若希望自己分析undo log的内容，可以下载InnoSQL并进行尝试。下载地址：https://david-mysql-tools.googlecode.com/files/mysql-5.5.30-v1a-linux-x86_64.tar.gz