21 | 为什么我只改一行的语句，锁这么多？

林晓斌 2018-12-31
                          
                             在上一篇文章中，我和你介绍了间隙锁和 next-key lock 的概念，但是并没有说明加锁规则。间隙锁的概念理解起来确实有点儿难，尤其在配合上行锁以后，很容易在判断是否会出现锁等待的问题上犯错。
                            
                             所以今天，我们就先从这个加锁规则开始吧。
                            
                             首先说明一下，这些加锁规则我没在别的地方看到过有类似的总结，以前我自己判断的时候都是想着代码里面的实现来脑补的。这次为了总结成不看代码的同学也能理解的规则，是我又重新刷了代码临时总结出来的。所以，
                            
                              这个规则有以下两条前提说明：
                             
                                MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。
                               
                                 如果大家在验证中有发现 bad case 的话，请提出来，我会再补充进这篇文章，使得一起学习本专栏的所有同学都能受益。
                                
                               因为间隙锁在可重复读隔离级别下才有效，所以本篇文章接下来的描述，若没有特殊说明，默认是可重复读隔离级别。
                              
                                我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
                               
                                  原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
                                 
                                   原则 2：查找过程中访问到的对象才会加锁。
                                  
                                    优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
                                   
                                     优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
                                    
                                      一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
                                     
                                  <div class="ps__rail-x" style="left: 0px; bottom: 0px;"><div class="ps__thumb-x" tabindex="0" style="left: 0px; width: 0px;"/></div><div class="ps__rail-y" style="top: 0px; right: 0px;"><div class="ps__thumb-y" tabindex="0" style="top: 0px; ;"/></div></div> <div class="_2XxzQqjL_0" style="display: none;"><div class="_1KDQg3Bq_0">确认放弃笔记？</div> <div class="mJWK5em0_0">放弃后所记笔记将不保留。</div></div> <div class="_3tbfXQau_0" style="display: none;"/></div> <div class="_1Ax_ZpPk_0"><div class="_2OgrMv3n_0">取消</div> <div class="FKyaWb-t_0">完成</div> <!----> <!----> <!----></div> <div class="_3MpaRWpt_0"><span>0/1000字</span></div></div> <div class="zbKHG1ec_0"><div class="tXxk8mk7_0">划线</div> <div class="_2YBWosuL_0"/> <div class="_25lruFgJ_0"/> <div class="_3wLabARi_0"/> <div class="tXxk8mk7_0">笔记</div> <div class="_2YBWosuL_0"/> <div class="tXxk8mk7_0">复制</div></div></div> <!----> <div class="_2sg1Tei__0"><span>©</span> 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
          </div> <div class="_3qftfzyH_0"><div class="_2rnRKKqV_0">该试读文章来自付费专栏《MySQL实战45讲》，如需阅读全部文章，<br/>请订阅文章所属专栏。</div> <div class="CJJSHwZ8_0">立即订阅</div></div></div> <div class="_2Vlfl3UO_0"><div class="_3SZaEKao_0"><img src="data:image/jpeg;base64,/9j/4QAYRXhpZgAASUkqAAgAAAAAAAAAAAAAAP/sABFEdWNreQABAAQAAABkAAD/4QN5aHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wLwA8P3hwYWNrZXQgYmVnaW49Iu+7vyIgaWQ9Ilc1TTBNcENlaGlIenJlU3pOVGN6a2M5ZCI/PiA8eDp4bXBtZXRhIHhtbG5zOng9ImFkb2JlOm5zOm1ldGEvIiB4OnhtcHRrPSJBZG9iZSBYTVAgQ29yZSA1LjYtYzE0MCA3OS4xNjA0NTEsIDIwMTcvMDUvMDYtMDE6MDg6MjEgICAgICAgICI+IDxyZGY6UkRGIHhtbG5zOnJkZj0iaHR0cDovL3d3dy53My5vcmcvMTk5OS8wMi8yMi1yZGYtc3ludGF4LW5zIyI+IDxyZGY6RGVzY3JpcHRpb24gcmRmOmFib3V0PSIiIHhtbG5zOnhtcE1NPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvbW0vIiB4bWxuczpzdFJlZj0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL3NUeXBlL1Jlc291cmNlUmVmIyIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvIiB4bXBNTTpPcmlnaW5hbERvY3VtZW50SUQ9InhtcC5kaWQ6YWE3YmZhMDItMzBhMC00MDg3LTg3MmYtOGMwMjMxNjNhZWRjIiB4bXBNTTpEb2N1bWVudElEPSJ4bXAuZGlkOjI2MTlEODM3NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXBNTTpJbnN0YW5jZUlEPSJ4bXAuaWlkOjI2MTlEODM2NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXA6Q3JlYXRvclRvb2w9IkFkb2JlIFBob3Rvc2hvcCBDQyAyMDE1IChNYWNpbnRvc2gpIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9InhtcC5paWQ6OTYyRTNCMDNBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiIHN0UmVmOmRvY3VtZW50SUQ9InhtcC5kaWQ6OTYyRTNCMDRBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiLz4gPC9yZGY6RGVzY3JpcHRpb24+IDwvcmRmOlJERj4gPC94OnhtcG1ldGE+IDw/eHBhY2tldCBlbmQ9InIiPz7/7gAOQWRvYmUAZMAAAAAB/9sAhAABAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAgICAgICAgICAgIDAwMDAwMDAwMDAQEBAQEBAQIBAQICAgECAgMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwP/wAARCADuAO4DAREAAhEBAxEB/8QAfAABAAICAwEBAAAAAAAAAAAAAAYHBAgBAwUCCgEBAAAAAAAAAAAAAAAAAAAAABAAAgIBAgIECwQJBQAAAAAAAAECAwQRBSEGMWESF0FRgVITk+MUVJTUIkJiB5EyhBVFhbXFNnFygqJTEQEAAAAAAAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwD9vAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGHmbhg7fD0mbl4+LF69l32wrctPBCMmpTfUk2BG7ue+Wqm4rNsua6XTi5DWvVKyutPyaoDmnnrlq19l506W9NPTYuSk2/xQqnGPlaQElxM7Dzq/S4WVj5VfhlRbC1Rfil2G3GXU9GBlAAAAAAAAAAAAAAAAAAAAAAAAAAAA4bUU5SajGKblJtJJJattvgkkBVHMnP8AJSswtilHSLcLdxcVLV9DWHCWsdF/6ST1+6uiQFW35F+VbK/Jutvum9Z23WSssk/xTm3JgdIADvx8nIxLY34t9uPdD9W2myVc1412otPR6cV0MC1uWufvTTrwd8cITlpCrcYpQhKT4KOXBaQrbf346R8aXFgWmnrxXFPimvCAAAAAAAAAAAAAAAAAAAAAAAAAAFUfmBzHKLexYVjjrGMtxsg+LU12oYia6E4tSn400vOQFTAAAAAAAuDkDmSWRFbHm2OVtUHLb7ZvWU6oLWeK2+LdMV2ofgTX3UBaAAAAAAAAAAAAAAAAAAAAAAAABi52XDAwsvNs4wxce6+S10cvRQlNQX4ptaLrYGr+RfblX3ZN8nO7Itsutk/vWWSc5Pq4sDpAAAAAABlYWXbgZeNmUPS3Guruhx0TcJJ9mWnTGa4NeFMDaDGvrysejJqeteRTVfW/HC2EbI/9ZAdwAAAAAAAAAAAAAAAAAAAAAACJc8WurlncOzwdrxateqeVT2v0wTXlA18AAAAAAAAAbFcnXSu5a2mcnq402U/8cfJuoivJGtASYAAAAAAAAAAAAAAAAAAAAAABFOdqXdyzuSjxlWse7yVZVMp/or1YGvQAAAAAAAADY3lGiWPy3tNclo5Yzv8AF9nJusyYvyxtQEjAAAAAAAAAAAAAAAAAAAAAAAdGVj15eNkYty1qyaLaLF4exbCVctOvSXADWDNxLsDLycLIj2bsa6dM/E3B6KUfHCa0afhTAxQAAAAAAZ224Nu55+LgUp+kyboV6pa9iDetljXm1VpyfUgNnqaoUU1UVLs101wqrj4oVxUILyRQHYAAAAAAAAAAAAAAAAAAAAAAAAVrz5yzPNh++cGtzyaK1HNpgtZX0QX2bopcZW0R4NdLhp5ujCmQAAAAAAXbyLyzPbaXumdW4ZuVX2aKprSWNjS0bck+Mbr9FqumMeHS2gLDAAAAAAAAAAAAAAAAAAAAAAAAAACuOZOQ6c+dmbtDrxcubc7cWX2cbIm+LlW0n7vbLw8OxJ+bxbCpM7bM/bLXVn4l+NPVpekg1CenhrsWtdseuLaAwQAHo7ftO47raqsDEuyZapSlCOlVevhtul2aql/uaAt3lrkWjbJ15u5yry86Gk6qYrXFxpripfaSd90X0NpRi+hNpSAsIAAAAAAAAAAAAAAAAAAAAAAAAAAAAD4sqrug67a4W1y/WhZCM4P/AFjJNMDw7eVuXrm5T2jCTfT6Kr0C49VLrQHNPK/L1ElKvaMJtcU7alfo/Gle7FqB7cK4VQVdcIVwitIwhFQhFeJRikkgPsAAAAAAAAAAAAAAAAAAAAAAAAAY2XmYuBRPKzL68aiv9ay2XZjq+iKXTKcvBFJt+BARGf5g8uRk4q3LsSeinDFkoy60pyhPR9aQHz3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAMjG575cybY1PKtxnJpRnk0Trq1fglZHtxrXXLRLxgTCMozjGUZKUZJSjKLTjKLWqlFrVNNPgwOQAAAAAAAAAAAAAAAAAAAAUZ+YW43ZG9ywHOSx9vqpUa9fsu7IphkTta8MnCyMepLrYECAAAAAAAAAAAF0/lxuN2Tt+Zg2zlOO320uhyerhTlK1qpPzYWUSa8Xa06NALHAAAAAAAAAAAAAAAAAAAABr3zx/lO6fsX9OxAImAAAAAAAAAAALY/K/+Ofyz+4AWwAAAAAAAAAAAAAAAAAAAADXvnj/ACndP2L+nYgETAAAAAAAAAAAFsflf/HP5Z/cALYAAAAAAAAAAAAAAAAAAAABVvMfJG7bxvOZuONkbdCjI937Eb7cmNq9DiUUS7Ua8S2C1nU2tJPgB4fdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAJvyby1ncvfvH323Et989z9F7rZdPs+7+9dvt+loo019OtNNfD0ATcAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA//Z" class="_1162B7i7_0"/> <div class="_20XltAts_0"><span class="ieiqepdd_0">登录</span> <span class="_2xgUAzrs_0">后留言</span></div></div> <div class="_1tbWAlsU_0 _23Q5Ko9I_0"><textarea placeholder="由作者筛选后的优质留言将会公开显示，欢迎踊跃留言。" rows="16" readonly="readonly" class="_2KHEOPSr_0"/></div> <!----></div> <div class="_3-W_zrq4_0"><h2>精选留言(178)</h2> <ul><li><div class="_2sjJGcOH_0"><img src="data:image/jpeg;base64,/9j/4QAYRXhpZgAASUkqAAgAAAAAAAAAAAAAAP/sABFEdWNreQABAAQAAABkAAD/4QN5aHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wLwA8P3hwYWNrZXQgYmVnaW49Iu+7vyIgaWQ9Ilc1TTBNcENlaGlIenJlU3pOVGN6a2M5ZCI/PiA8eDp4bXBtZXRhIHhtbG5zOng9ImFkb2JlOm5zOm1ldGEvIiB4OnhtcHRrPSJBZG9iZSBYTVAgQ29yZSA1LjYtYzE0MCA3OS4xNjA0NTEsIDIwMTcvMDUvMDYtMDE6MDg6MjEgICAgICAgICI+IDxyZGY6UkRGIHhtbG5zOnJkZj0iaHR0cDovL3d3dy53My5vcmcvMTk5OS8wMi8yMi1yZGYtc3ludGF4LW5zIyI+IDxyZGY6RGVzY3JpcHRpb24gcmRmOmFib3V0PSIiIHhtbG5zOnhtcE1NPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvbW0vIiB4bWxuczpzdFJlZj0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL3NUeXBlL1Jlc291cmNlUmVmIyIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvIiB4bXBNTTpPcmlnaW5hbERvY3VtZW50SUQ9InhtcC5kaWQ6YWE3YmZhMDItMzBhMC00MDg3LTg3MmYtOGMwMjMxNjNhZWRjIiB4bXBNTTpEb2N1bWVudElEPSJ4bXAuZGlkOjI2MTlEODM3NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXBNTTpJbnN0YW5jZUlEPSJ4bXAuaWlkOjI2MTlEODM2NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXA6Q3JlYXRvclRvb2w9IkFkb2JlIFBob3Rvc2hvcCBDQyAyMDE1IChNYWNpbnRvc2gpIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9InhtcC5paWQ6OTYyRTNCMDNBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiIHN0UmVmOmRvY3VtZW50SUQ9InhtcC5kaWQ6OTYyRTNCMDRBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiLz4gPC9yZGY6RGVzY3JpcHRpb24+IDwvcmRmOlJERj4gPC94OnhtcG1ldGE+IDw/eHBhY2tldCBlbmQ9InIiPz7/7gAOQWRvYmUAZMAAAAAB/9sAhAABAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAgICAgICAgICAgIDAwMDAwMDAwMDAQEBAQEBAQIBAQICAgECAgMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwP/wAARCADuAO4DAREAAhEBAxEB/8QAfAABAAICAwEBAAAAAAAAAAAAAAYHBAgBAwUCCgEBAAAAAAAAAAAAAAAAAAAAABAAAgIBAgIECwQJBQAAAAAAAAECAwQRBSEGMWESF0FRgVITk+MUVJTUIkJiB5EyhBVFhbXFNnFygqJTEQEAAAAAAAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwD9vAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGHmbhg7fD0mbl4+LF69l32wrctPBCMmpTfUk2BG7ue+Wqm4rNsua6XTi5DWvVKyutPyaoDmnnrlq19l506W9NPTYuSk2/xQqnGPlaQElxM7Dzq/S4WVj5VfhlRbC1Rfil2G3GXU9GBlAAAAAAAAAAAAAAAAAAAAAAAAAAAA4bUU5SajGKblJtJJJattvgkkBVHMnP8AJSswtilHSLcLdxcVLV9DWHCWsdF/6ST1+6uiQFW35F+VbK/Jutvum9Z23WSssk/xTm3JgdIADvx8nIxLY34t9uPdD9W2myVc1412otPR6cV0MC1uWufvTTrwd8cITlpCrcYpQhKT4KOXBaQrbf346R8aXFgWmnrxXFPimvCAAAAAAAAAAAAAAAAAAAAAAAAAAFUfmBzHKLexYVjjrGMtxsg+LU12oYia6E4tSn400vOQFTAAAAAAAuDkDmSWRFbHm2OVtUHLb7ZvWU6oLWeK2+LdMV2ofgTX3UBaAAAAAAAAAAAAAAAAAAAAAAAABi52XDAwsvNs4wxce6+S10cvRQlNQX4ptaLrYGr+RfblX3ZN8nO7Itsutk/vWWSc5Pq4sDpAAAAAABlYWXbgZeNmUPS3Guruhx0TcJJ9mWnTGa4NeFMDaDGvrysejJqeteRTVfW/HC2EbI/9ZAdwAAAAAAAAAAAAAAAAAAAAAACJc8WurlncOzwdrxateqeVT2v0wTXlA18AAAAAAAAAbFcnXSu5a2mcnq402U/8cfJuoivJGtASYAAAAAAAAAAAAAAAAAAAAAABFOdqXdyzuSjxlWse7yVZVMp/or1YGvQAAAAAAAADY3lGiWPy3tNclo5Yzv8AF9nJusyYvyxtQEjAAAAAAAAAAAAAAAAAAAAAAAdGVj15eNkYty1qyaLaLF4exbCVctOvSXADWDNxLsDLycLIj2bsa6dM/E3B6KUfHCa0afhTAxQAAAAAAZ224Nu55+LgUp+kyboV6pa9iDetljXm1VpyfUgNnqaoUU1UVLs101wqrj4oVxUILyRQHYAAAAAAAAAAAAAAAAAAAAAAAAVrz5yzPNh++cGtzyaK1HNpgtZX0QX2bopcZW0R4NdLhp5ujCmQAAAAAAXbyLyzPbaXumdW4ZuVX2aKprSWNjS0bck+Mbr9FqumMeHS2gLDAAAAAAAAAAAAAAAAAAAAAAAAAACuOZOQ6c+dmbtDrxcubc7cWX2cbIm+LlW0n7vbLw8OxJ+bxbCpM7bM/bLXVn4l+NPVpekg1CenhrsWtdseuLaAwQAHo7ftO47raqsDEuyZapSlCOlVevhtul2aql/uaAt3lrkWjbJ15u5yry86Gk6qYrXFxpripfaSd90X0NpRi+hNpSAsIAAAAAAAAAAAAAAAAAAAAAAAAAAAAD4sqrug67a4W1y/WhZCM4P/AFjJNMDw7eVuXrm5T2jCTfT6Kr0C49VLrQHNPK/L1ElKvaMJtcU7alfo/Gle7FqB7cK4VQVdcIVwitIwhFQhFeJRikkgPsAAAAAAAAAAAAAAAAAAAAAAAAAY2XmYuBRPKzL68aiv9ay2XZjq+iKXTKcvBFJt+BARGf5g8uRk4q3LsSeinDFkoy60pyhPR9aQHz3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAMjG575cybY1PKtxnJpRnk0Trq1fglZHtxrXXLRLxgTCMozjGUZKUZJSjKLTjKLWqlFrVNNPgwOQAAAAAAAAAAAAAAAAAAAAUZ+YW43ZG9ywHOSx9vqpUa9fsu7IphkTta8MnCyMepLrYECAAAAAAAAAAAF0/lxuN2Tt+Zg2zlOO320uhyerhTlK1qpPzYWUSa8Xa06NALHAAAAAAAAAAAAAAAAAAAABr3zx/lO6fsX9OxAImAAAAAAAAAAALY/K/+Ofyz+4AWwAAAAAAAAAAAAAAAAAAAADXvnj/ACndP2L+nYgETAAAAAAAAAAAFsflf/HP5Z/cALYAAAAAAAAAAAAAAAAAAAABVvMfJG7bxvOZuONkbdCjI937Eb7cmNq9DiUUS7Ua8S2C1nU2tJPgB4fdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAJvyby1ncvfvH323Et989z9F7rZdPs+7+9dvt+loo019OtNNfD0ATcAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA//Z" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>堕落天使</span> <span class="_3ZqVltck_0">置顶</span></div> <div class="_2_QraFYR_0">老师，您好。假期的没跟上，今天补到了这节课，看了之后有几点不是太明白。望能解答一下。<br/>1. 索引c上的锁算不算是行锁。假如索引c上的next-key lock为(0,5] (5,10]，那么5算不算是c上的行锁？<br/>2. 在案例六中，执行 “delete from t where c=10;” 语句，索引c上的next-key lock是(5,10],(10,10],(10,15)。那么主键索引上的锁是什么呢？是只有行锁，锁住的是 (10,10,10) 和 (30,10,30) 两行吗？<br/>3. 也是在案例六中，session A不变，在session B中执行 “update t_20 set d=50 where c=5;”、“update t_20 set d=50 where c=15;”、“insert into t_20 values(40,15,40);”均执行成功，但执行“insert into t_20 values(50,5,50);” 时，却被阻塞。为什么呢？具体执行语句如下<br/>session A<br/>mysql> begin;<br/>mysql> explain delete from t_20 where c=10;<br/>id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra<br/>1	DELETE	t_20		range	c	c	5	const	2	100	Using where<br/>mysql> delete from t_20 where c=10;<br/><br/>session B<br/>mysql> update t_20 set d=50 where c=5;<br/>Query OK, 1 row affected (0.01 sec)<br/>Rows matched: 1  Changed: 1  Warnings: 0<br/><br/>mysql> update t_20 set d=50 where c=15;<br/>Query OK, 1 row affected (0.00 sec)<br/>Rows matched: 1  Changed: 1  Warnings: 0<br/><br/>mysql> insert into t_20 values(40,15,40);<br/>Query OK, 1 row affected (0.00 sec)<br/><br/>mysql> explain insert into t_20 values(50,5,50);<br/>+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+<br/>| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |<br/>+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+<br/>|  1 | INSERT      | t_20  | NULL       | ALL  | c             | NULL | NULL    | NULL | NULL |     NULL | NULL  |<br/>+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+<br/>1 row in set (0.00 sec)<br/><br/>mysql> insert into t_20 values(50,5,50);<br/>（block）<br/><br/>我使用的mysql版本是：5.7.23-0ubuntu0.16.04.1<br/>show variables的结果太多，我截取了一部分，或许对您分析有帮助：<br/>innodb_version	5.7.23<br/>protocol_version	10<br/>slave_type_conversions	<br/>tls_version	TLSv1,TLSv1.1<br/>version	5.7.23-0ubuntu0.16.04.1<br/>version_comment	(Ubuntu)<br/>version_compile_machine	x86_64<br/>version_compile_os	Linux</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 1. Next-key lock 就是间隙锁➕行锁，所以包含=5这一行<br/>2. 对<br/>3. (c=5,id=50)是在这个gap里哦，你试试插入(1,5,50)对比一下。好问题</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-03</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>2</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">17</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/0f/52/3c/d6fcb93a.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>张三</span> <!----></div> <div class="_2_QraFYR_0">Happy New Year !这个专栏绝对是极客时间最好我买过最值的专栏。</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2018-12-31</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>1</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">75</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/4f/78/c3d8ecb0.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>undifined</span> <!----></div> <div class="_2_QraFYR_0">遇到一个有趣的问题，在老师的解答下终于弄明白了：<br/><br/>CREATE TABLE z (<br/>  id INT PRIMARY KEY AUTO_INCREMENT,<br/>  b  INT,<br/>  KEY b(b)<br/>)<br/>  ENGINE = InnoDB<br/>  DEFAULT CHARSET = utf8;<br/><br/>INSERT INTO z (id, b)<br/>VALUES (1, 2),<br/>  (3, 4),<br/>  (5, 6),<br/>  (7, 8),<br/>  (9, 10);<br/><br/>session A<br/><br/>BEGIN;<br/>SELECT *<br/>FROM z<br/>WHERE b = 6 FOR UPDATE;<br/><br/>session B <br/>INSERT INTO z VALUES (0, 4);<br/>这里为什么会被锁住<br/><br/>答案比较长，写在我自己的笔记里了，地址是 https://helloworlde.github.io/blog/blog/MySQL/MySQL-%E4%B8%AD%E5%85%B3%E4%BA%8Egap-lock-next-key-lock-%E7%9A%84%E4%B8%80%E4%B8%AA%E9%97%AE%E9%A2%98.html<br/><br/>大家可以看看</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 好问题，质量很高的笔记</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-07</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>6</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">26</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/0f/f8/ba/14e05601.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>约书亚</span> <!----></div> <div class="_2_QraFYR_0">早晨睡不着打开极客时间一看，竟然更新了。今天是周日而且在假期中哎...</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 风雨无阻 节假日不休，包括元旦和春节😄</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2018-12-31</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>1</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">20</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/11/11/18/8cee35f9.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>HuaMax</span> <!----></div> <div class="_2_QraFYR_0">首先老师新年快乐，学习专栏受益良多！<br/>上期问过老师的问题已了解答案，锁是加在索引上的。再尝试回答问题。c上是普通索引，根据原则2，访问到的都要加锁，在查询c>=15这个条件时，在查找到15后加锁（10，15］，继续往右查找，按理说不会锁住6这个索引值，但查询语句中加了order by c desc，我猜想会优化为使用c<=20这条语句，查找到20后往左查找，这样会访问到15左边的值10，从而加锁（5，10］，不知我理解对否？</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 新年好<br/><br/>对的👍🏿</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-01</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">17</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/12/9b/a8/6a391c66.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>Leon📷</span> <!----></div> <div class="_2_QraFYR_0">老师，案例八session B的操作语句update t set d = d + 1 where c =10; 由于c是非唯一键索引，锁（5，10」可以理解<br/>，为什么不锁(10,15} 呢，不是应该继续向后扫描直到第一个不满足条件的值为止吗</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 好问题，新年快乐<br/><br/>会锁的，只是因为在(5,10]就被锁住了，所以后面的锁加不上去了😆</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-29</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">13</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/f7/5b/d2e7c2c4.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>时隐时现</span> <!----></div> <div class="_2_QraFYR_0">不好意思，这次又来晚了，看这种连载技术文章，和看小说一样，养肥了集中看~~<br/>这次的问题如下，希望丁老师有空解答一下。<br/>版本：mysql 5.6.39<br/>CREATE TABLE `t` (<br/>  `a` int(11) NOT NULL,<br/>  `b` int(11) DEFAULT NULL<br/>) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br/>insert into t values(1,1),(2,2),(3,3),(4,4),(5,5);<br/>采用READ-COMMITTED隔离级别<br/>案例1、<br/>session A：<br/>begin;<br/>update t set a=6 where b=1;<br/>session B：<br/>begin;<br/>update t set a=7 where b=2;<br/>A和B均能执行成功<br/>问题1：官档上说对于RC且全表扫描的update，先逐行添加行锁然后释放掉不符合where条件的，那么session A成功对(1,1)加锁，理论上session B在扫描(1,1)并尝试加锁时会被阻塞，为何还能执行成功？官档链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html<br/><br/>案例2：<br/>session A：<br/>begin;<br/>update t set a=6 where b=1;<br/>session B：<br/>begin;<br/>delete from t where b=2; -- 被阻塞<br/>问题2：为何案例1 中的session B不会被阻塞，而案例2的却被session A的行数阻塞，update和delete都是全部扫描，难道加锁机制不一样？</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: <br/>好问题，在read-commited隔离级别下，update语句<br/>有一个“semi-consistent” read优化，<br/><br/>意思是，如果update语句碰到一个已经被锁了的行，会读入最新的版本，然后判断一下是不是满足查询条件，<br/>a)如果不满足，就直接跳过；<br/>b) 如果满足，才进入锁等待<br/><br/>你的第二个问题：这个策略，只对update有效，delete无效<br/><br/>新春快乐~</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-30</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>2</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">12</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="data:image/jpeg;base64,/9j/4QAYRXhpZgAASUkqAAgAAAAAAAAAAAAAAP/sABFEdWNreQABAAQAAABkAAD/4QN5aHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wLwA8P3hwYWNrZXQgYmVnaW49Iu+7vyIgaWQ9Ilc1TTBNcENlaGlIenJlU3pOVGN6a2M5ZCI/PiA8eDp4bXBtZXRhIHhtbG5zOng9ImFkb2JlOm5zOm1ldGEvIiB4OnhtcHRrPSJBZG9iZSBYTVAgQ29yZSA1LjYtYzE0MCA3OS4xNjA0NTEsIDIwMTcvMDUvMDYtMDE6MDg6MjEgICAgICAgICI+IDxyZGY6UkRGIHhtbG5zOnJkZj0iaHR0cDovL3d3dy53My5vcmcvMTk5OS8wMi8yMi1yZGYtc3ludGF4LW5zIyI+IDxyZGY6RGVzY3JpcHRpb24gcmRmOmFib3V0PSIiIHhtbG5zOnhtcE1NPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvbW0vIiB4bWxuczpzdFJlZj0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL3NUeXBlL1Jlc291cmNlUmVmIyIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvIiB4bXBNTTpPcmlnaW5hbERvY3VtZW50SUQ9InhtcC5kaWQ6YWE3YmZhMDItMzBhMC00MDg3LTg3MmYtOGMwMjMxNjNhZWRjIiB4bXBNTTpEb2N1bWVudElEPSJ4bXAuZGlkOjI2MTlEODM3NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXBNTTpJbnN0YW5jZUlEPSJ4bXAuaWlkOjI2MTlEODM2NTgzMTExRTk5NDY4Qjk3QUFCNDFBN0QzIiB4bXA6Q3JlYXRvclRvb2w9IkFkb2JlIFBob3Rvc2hvcCBDQyAyMDE1IChNYWNpbnRvc2gpIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9InhtcC5paWQ6OTYyRTNCMDNBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiIHN0UmVmOmRvY3VtZW50SUQ9InhtcC5kaWQ6OTYyRTNCMDRBREI4MTFFOEFFNTJDODlGREQ1OTUzMDMiLz4gPC9yZGY6RGVzY3JpcHRpb24+IDwvcmRmOlJERj4gPC94OnhtcG1ldGE+IDw/eHBhY2tldCBlbmQ9InIiPz7/7gAOQWRvYmUAZMAAAAAB/9sAhAABAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAgICAgICAgICAgIDAwMDAwMDAwMDAQEBAQEBAQIBAQICAgECAgMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwP/wAARCADuAO4DAREAAhEBAxEB/8QAfAABAAICAwEBAAAAAAAAAAAAAAYHBAgBAwUCCgEBAAAAAAAAAAAAAAAAAAAAABAAAgIBAgIECwQJBQAAAAAAAAECAwQRBSEGMWESF0FRgVITk+MUVJTUIkJiB5EyhBVFhbXFNnFygqJTEQEAAAAAAAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwD9vAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGHmbhg7fD0mbl4+LF69l32wrctPBCMmpTfUk2BG7ue+Wqm4rNsua6XTi5DWvVKyutPyaoDmnnrlq19l506W9NPTYuSk2/xQqnGPlaQElxM7Dzq/S4WVj5VfhlRbC1Rfil2G3GXU9GBlAAAAAAAAAAAAAAAAAAAAAAAAAAAA4bUU5SajGKblJtJJJattvgkkBVHMnP8AJSswtilHSLcLdxcVLV9DWHCWsdF/6ST1+6uiQFW35F+VbK/Jutvum9Z23WSssk/xTm3JgdIADvx8nIxLY34t9uPdD9W2myVc1412otPR6cV0MC1uWufvTTrwd8cITlpCrcYpQhKT4KOXBaQrbf346R8aXFgWmnrxXFPimvCAAAAAAAAAAAAAAAAAAAAAAAAAAFUfmBzHKLexYVjjrGMtxsg+LU12oYia6E4tSn400vOQFTAAAAAAAuDkDmSWRFbHm2OVtUHLb7ZvWU6oLWeK2+LdMV2ofgTX3UBaAAAAAAAAAAAAAAAAAAAAAAAABi52XDAwsvNs4wxce6+S10cvRQlNQX4ptaLrYGr+RfblX3ZN8nO7Itsutk/vWWSc5Pq4sDpAAAAAABlYWXbgZeNmUPS3Guruhx0TcJJ9mWnTGa4NeFMDaDGvrysejJqeteRTVfW/HC2EbI/9ZAdwAAAAAAAAAAAAAAAAAAAAAACJc8WurlncOzwdrxateqeVT2v0wTXlA18AAAAAAAAAbFcnXSu5a2mcnq402U/8cfJuoivJGtASYAAAAAAAAAAAAAAAAAAAAAABFOdqXdyzuSjxlWse7yVZVMp/or1YGvQAAAAAAAADY3lGiWPy3tNclo5Yzv8AF9nJusyYvyxtQEjAAAAAAAAAAAAAAAAAAAAAAAdGVj15eNkYty1qyaLaLF4exbCVctOvSXADWDNxLsDLycLIj2bsa6dM/E3B6KUfHCa0afhTAxQAAAAAAZ224Nu55+LgUp+kyboV6pa9iDetljXm1VpyfUgNnqaoUU1UVLs101wqrj4oVxUILyRQHYAAAAAAAAAAAAAAAAAAAAAAAAVrz5yzPNh++cGtzyaK1HNpgtZX0QX2bopcZW0R4NdLhp5ujCmQAAAAAAXbyLyzPbaXumdW4ZuVX2aKprSWNjS0bck+Mbr9FqumMeHS2gLDAAAAAAAAAAAAAAAAAAAAAAAAAACuOZOQ6c+dmbtDrxcubc7cWX2cbIm+LlW0n7vbLw8OxJ+bxbCpM7bM/bLXVn4l+NPVpekg1CenhrsWtdseuLaAwQAHo7ftO47raqsDEuyZapSlCOlVevhtul2aql/uaAt3lrkWjbJ15u5yry86Gk6qYrXFxpripfaSd90X0NpRi+hNpSAsIAAAAAAAAAAAAAAAAAAAAAAAAAAAAD4sqrug67a4W1y/WhZCM4P/AFjJNMDw7eVuXrm5T2jCTfT6Kr0C49VLrQHNPK/L1ElKvaMJtcU7alfo/Gle7FqB7cK4VQVdcIVwitIwhFQhFeJRikkgPsAAAAAAAAAAAAAAAAAAAAAAAAAY2XmYuBRPKzL68aiv9ay2XZjq+iKXTKcvBFJt+BARGf5g8uRk4q3LsSeinDFkoy60pyhPR9aQHz3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAHeHy752b8r7QB3h8u+dm/K+0Ad4fLvnZvyvtAMjG575cybY1PKtxnJpRnk0Trq1fglZHtxrXXLRLxgTCMozjGUZKUZJSjKLTjKLWqlFrVNNPgwOQAAAAAAAAAAAAAAAAAAAAUZ+YW43ZG9ywHOSx9vqpUa9fsu7IphkTta8MnCyMepLrYECAAAAAAAAAAAF0/lxuN2Tt+Zg2zlOO320uhyerhTlK1qpPzYWUSa8Xa06NALHAAAAAAAAAAAAAAAAAAAABr3zx/lO6fsX9OxAImAAAAAAAAAAALY/K/+Ofyz+4AWwAAAAAAAAAAAAAAAAAAAADXvnj/ACndP2L+nYgETAAAAAAAAAAAFsflf/HP5Z/cALYAAAAAAAAAAAAAAAAAAAABVvMfJG7bxvOZuONkbdCjI937Eb7cmNq9DiUUS7Ua8S2C1nU2tJPgB4fdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAHdrvvxe0+vzPoAJvyby1ncvfvH323Et989z9F7rZdPs+7+9dvt+loo019OtNNfD0ATcAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA//Z" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>Geek_9ca34e</span> <!----></div> <div class="_2_QraFYR_0">老师，你好：<br/>我练习实例的时候发现一个问题：如 案例五：唯一索引范围锁 bug<br/>begin;<br/>select * from t where id>10 and id<=15 for update;<br/>1、执行如上语句加锁范围(10,15]和(15,20]；<br/>2、因为10未加锁，所以我单独再开一个连接，执行delete from t where id=10;不会锁等待，能正常删除；<br/>3、但是我再执行insert into t values(10,10,10); 语句会等待，无法正常执行；<br/>4、经过分析我发现第一个连接执行的语句的加锁范围已经变成(5,15]和(15,20]，代表锁蔓延了；这是什么原因呢？</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 好问题，我会加到答疑文章中，<br/>Gap是一个动态的概念</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-09</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">12</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/0c/ca/6173350b.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>郭江伟</span> <!----></div> <div class="_2_QraFYR_0">老师这次的留下的问题，语句跟上次不一样，上期问题语句是select id  from t where c>=15 and c<=20 order by c desc  for update;；这次缺少了 order by c desc ，不加desc的话insert into t values(6,6,6);不会被堵塞；<br/>根据优化3：索引上的等值查询，在向右遍历时且最后一个值不满足等值条件的时候next-key lock退化为间隙锁；<br/>问题中的sql语句加了desc ，是向左扫描，该优化用不上，所以下限10是闭区间，为了防止c为10的行加入，需要锁定到索引c键（5,5）<br/>此例中insert into t values(6,5,6) 会堵塞，insert into t values(4,5,6) 不会堵塞，</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 嗯你说的对<br/><br/>不过是我少打一个词了，加上去了，要desc哦<br/><br/>重新分析下😄</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2018-12-31</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">8</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/11/40/59/f93698ec.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>乾坤</span> <!----></div> <div class="_2_QraFYR_0">您好，关于"优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。"，我觉得改为"从第一个满足等值条件的索引记录开始向右遍历到第一个不满足等值条件记录，并将第一个不满足等值条件记录上的next-key lock 退化为间隙锁"更明确些</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 感觉没大差别，嗯嗯，理解就好😄</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-01</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">7</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/24/a8/c0148cb8.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>Chu~♡·の~</span> <!----></div> <div class="_2_QraFYR_0">是不是可以总结为：<br/>1、查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）；<br/>2、等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁；<br/>3、范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 嗯嗯，可以的👍🏿</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-02</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">6</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/12/da/ec/779c1a78.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>往事随风，顺其自然</span> <!----></div> <div class="_2_QraFYR_0">这和分两步有什么关系？<br/>(5,10]已经是被锁住，分不分两步来加锁，这个间隙和行锁都被锁住了，session b应该是拿不到锁才对。</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-01</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>2</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">6</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/13/e8/5c/561caff2.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>陈</span> <!----></div> <div class="_2_QraFYR_0">老师在案列一中update t set d=d+1 where id=7 中id是主键也是唯一索引，按优化1应该退化成行锁才对，为什么insert into t values(8,8,8)会被锁住，我是那儿理解错了?</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 这一行存在的时候是行锁，这一行不存在，那就是间隙锁啦。<br/><br/>insert into t values(8,8,8)是被主键上(5,10)的间隙锁锁住的</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-11</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">5</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/DYAIOgq83epbtcXw5PHzibHcXlupLmnZOYXBLR10U4Hvn5tib14EYlkMBERYgGlgZ63BxgFSBTQmUErfSXibcKl6w/132" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>Geek_maxwell</span> <!----></div> <div class="_2_QraFYR_0">老师有个疑问，就是既然repeat read已经解决了幻读（利用next key lock)，那下一个事务级别（序列化）主要是解决什么问题呢？ 因为原本我以为repeat read 其实没有解决幻读</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-04-14</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span>1</span></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">2</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/PiajxSqBRaELZPnUAiajaR5C25EDLWeJURggyiaOP5GGPe2qlwpQcm5e3ybib8OsP4tvddFDLVRSNNGL5I3SFPJHsA/132" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>null</span> <!----></div> <div class="_2_QraFYR_0">@唯她命 的问题，案例一为啥是间隙锁，而不是行锁？<br/><br/>我自己的理解：因为 id=7 的记录不存在，没有上锁的实体，所以无法使用优化 1。<br/><br/>优化 1 是说索引上的等值查询，如果是唯一索引，且记录存在，就退化成行锁。<br/>优化 2 是说在索引上的等值查询，不管是唯一索引，还是非唯一索引，只有向右遍历时，最后一个值不等于等值查询的条件，就会退化成间隙锁。如 where id=7，扫描完 id=5，接着扫描 id=10，但是 7 不等于 10，所以会退化成间隙锁。<br/><br/>如果理解有误，还望老师指正😁😁</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-04-13</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">2</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/10/ea/aa/cdd13ad2.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>王伯轩</span> <!----></div> <div class="_2_QraFYR_0">这两章今天来来回回啃了好几遍，终于感觉有关锁的知识成体系了。 <br/><br/>尤其是总结的加锁原则，简直太精辟了，感谢丁老师.<br/><br/>原则 2：查找过程中访问到的对象才会加锁。<br/><br/>对于原则2 我有个疑问，访问到的对象应该还应该包含其影响到的索引。<br/><br/>老师的例子中某些隐含了这些东西，其他同学的留言中也表明了这点，望老师指点<br/><br/>比如 一个表t（id ，c,d,e）<br/>id是主键 ，其他列都有非唯一索引。 <br/>执行insert 需要获取所有索引上的锁；<br/>执行删除（即使根据id删除）也需要获取其他索引的锁；<br/>执行update（即使where条件使用id）如果更新的有索引列，也需要获取上面的锁<br/><br/>因为更新操作肯定会导致索引树的修改，如果不更改，会导致索引搜索时返回的数据和实际不一样；如果更改的话，肯定需要防止并发就需要加锁<br/><br/>不知道理解的是否正确</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 对的，要update和delete的时候，要“先读后写”，这个读就开始加锁了。<br/><br/>insert的时候要有插入意向锁（就是会跟gap lock冲突的那个）<br/></p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-04-06</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">2</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/11/09/98/b11c372b.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>鸠翱</span> <!----></div> <div class="_2_QraFYR_0">老师我今天在回顾的时候又想到一个新问题，为什么RR级别下要以next-key lock作为加锁的基本单位呢？<br/>比如说案例5中的那个例子，我将其修改成select * from t where id > 10 and id < 15 for upadate，按照加锁规则，需要对(10,15]上锁，那么按照逻辑来看，15的那个行锁其实没必要的（难道是有必要的嘛？），既然next-key lock本质也是先加间隙锁再加行锁，为什么非要把那个行锁也加上，就是因为next-key lock是加锁基本单位嘛？</div> <div class="_10o3OAxT_0"><p class="_3KxQPN3V_0">作者回复: 代码实现上是这样子的。。</p></div> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-01-10</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">2</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/11/a0/cb/aab3b3e7.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>张三丰</span> <!----></div> <div class="_2_QraFYR_0">请教老师一个问题，<br/>A:update  t  set = 2 where  h  = 5<br/>B:update  t  set = 2 where  K  = 5<br/><br/>h,k都不是索引，如果执行语句A不提交，此时B会执行吗？<br/>因为锁是针对索引的，对不同的索引加锁彼此都不会影响，如果没有索引呢？两条语句都锁全表吗？</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-10-17</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">1</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/15/39/95/a72ef023.jpg" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>木子</span> <!----></div> <div class="_2_QraFYR_0">老师，你好，回头看第二遍的时候，突然发现一个地方没明白：在案例1中 id=7等值查询时，id是主键，也满足唯一索引，为什么没有根据优化1 next-key lock 退化为行锁而是加了（5,10）的间隙锁？而案例三中 主键索引范围锁案例中 第一步 找到id=10的记录 这时候是根据优化2 主键索引上的等值查询 退化为行锁？同样是主键索引的等值查询为什么一个加了间隙锁一个退化成了行锁？麻烦老师看到之后回复一下，非常感谢</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-05-14</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">1</span></div></div></div></div></div> <!----></li><li><div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/PiajxSqBRaELZPnUAiajaR5C25EDLWeJURggyiaOP5GGPe2qlwpQcm5e3ybib8OsP4tvddFDLVRSNNGL5I3SFPJHsA/132" class="_3FLYR4bF_0"/> <div class="_36ChpWj4_0"><div class="_2zFoi7sd_0"><span>null</span> <!----></div> <div class="_2_QraFYR_0">@坠落天使 的问题，案例六的 session A 不变，session B insert (c=5, id=50) 的记录被 block，可以借用“线段”的概念来帮助理解，下面是我自己的理解：<br/>session A 我们只关注 a (c=5, id=5) 和 b (c=10, id=10) 这两个点 (为了方便说明，其他的锁我们选择忽略)。<br/>在平面上，a 和 b两点连成的线段，就是我们的加锁范围。<br/>现在有一个点 f(c=5, id=6) 需要插入线段中，因为 a 和 f 的 c 值都是 5，但是 f 的 id 比 a 的 id 要大，所以在线段上表示，f 在 a 的右边，落入了线段内，即落入了锁区间，所以被 block 了。<br/>有一个点 e(c=5, id=4) 需要在平面展示，同理 e 点是在 a 点的左边，没落到锁区间，所以 insert 成功。</div> <!----> <div class="_3klNVc4Z_0"><div class="_3Hkula0k_0">2019-04-14</div> <div class="_3_7joXrw_0"><!----> <div class="_3r0uuaYZ_0"><i class="iconfont"></i> <span/></div> <div class="_24fTab90_0"><i class="iconfont"></i> <span class="">1</span></div></div></div></div></div> <!----></li></ul> <div class="_1DRwKyCv_0">
            收起评论<span class="iconfont _1QBZRYBq_0"></span></div></div></div><div class="ps__rail-x" style="left: 0px; bottom: 0px;"><div class="ps__thumb-x" tabindex="0" style="left: 0px; width: 0px;"/></div><div class="ps__rail-y" style="top: 0px; right: 0px;"><div class="ps__thumb-y" tabindex="0" style="top: 0px; ;"/></div></div> <div class="_35V_pofE_0"><div class="_19KVU7IX_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"><span>99+</span></div> <div class="_3QWdWikl_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"><!----></div> <div class="P00Ux77Z_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"></div> <div class="_1GGnOK0Z_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"></div> <div class="_23jDaBCo_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"></div> <!----> <!----> <div class="FSrxdnjI_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"><a href="//time.geekbang.org/download" target="_blank"><span class="_2NDI-Prg_0"></span><span class="_15yXCNVi_0">下载<br/>客户端</span></a></div> <div class="_2nVCVrRU_0 iconfont OJ1-O2lA_0 _3zAMGadh_0"><span class="_2NDI-Prg_0"></span><span class="_15yXCNVi_0">返回<br/>顶部</span></div></div> <div class="k7LpsVQS_0"><!----></div> <!----></div></div> <div class="_2sRsF5RP_0"/></div><div id="gkui-modal-controller"><!----> </div><div data-v-99cd8b4a="" id="gkui-message-list" style="top: 24px;"><span data-v-99cd8b4a=""/></div><!----></body>