master

分支 (1)

管理

管理

master

mysql45
/
40讲insert语句的锁为什么这么多.html

<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
          content="width=device-width,initial-scale=1,maximum-scale=1,minimum-scale=1,user-scalable=no,viewport-fit=cover">
    <meta name="format-detection" content="telephone=no">
    <style type="text/css">

#watermark {

  position: relative;
  overflow: hidden;
}

#watermark .x {
  position: absolute;
  top: 800;
  left: 400;
  color: #3300ff;
  font-size: 50px;
  pointer-events: none;
  opacity:0.3;
  filter:Alpha(opacity=50);


}
    </style>


    <style type="text/css">
 html{color:#333;-webkit-text-size-adjust:100%;-ms-text-size-adjust:100%;text-rendering:optimizelegibility;font-family:Helvetica Neue,PingFang SC,Verdana,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,sans-serif}html.borderbox *,html.borderbox :after,html.borderbox :before{box-sizing:border-box}article,aside,blockquote,body,button,code,dd,details,dl,dt,fieldset,figcaption,figure,footer,form,h1,h2,h3,h4,h5,h6,header,hr,input,legend,li,menu,nav,ol,p,pre,section,td,textarea,th,ul{margin:0;padding:0}article,aside,details,figcaption,figure,footer,header,menu,nav,section{display:block}audio,canvas,video{display:inline-block}body,button,input,select,textarea{font:300 1em/1.8 PingFang SC,Lantinghei SC,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,Helvetica,sans-serif}button::-moz-focus-inner,input::-moz-focus-inner{padding:0;border:0}table{border-collapse:collapse;border-spacing:0}fieldset,img{border:0}blockquote{position:relative;color:#999;font-weight:400;border-left:1px solid #1abc9c;padding-left:1em;margin:1em 3em 1em 2em}@media only screen and (max-width:640px){blockquote{margin:1em 0}}abbr,acronym{border-bottom:1px dotted;font-variant:normal}abbr{cursor:help}del{text-decoration:line-through}address,caption,cite,code,dfn,em,th,var{font-style:normal;font-weight:400}ol,ul{list-style:none}caption,th{text-align:left}q:after,q:before{content:""}sub,sup{font-size:75%;line-height:0;position:relative}:root sub,:root sup{vertical-align:baseline}sup{top:-.5em}sub{bottom:-.25em}a{color:#1abc9c}a:hover{text-decoration:underline}.typo a{border-bottom:1px solid #1abc9c}.typo a:hover{border-bottom-color:#555;color:#555}.typo a:hover,a,ins{text-decoration:none}.typo-u,u{text-decoration:underline}mark{background:#fffdd1;border-bottom:1px solid #ffedce;padding:2px;margin:0 5px}code,pre,pre tt{font-family:Courier,Courier New,monospace}pre{background:hsla(0,0%,97%,.7);border:1px solid #ddd;padding:1em 1.5em;display:block;-webkit-overflow-scrolling:touch}hr{border:none;border-bottom:1px solid #cfcfcf;margin-bottom:.8em;height:10px}.typo-small,figcaption,small{font-size:.9em;color:#888}b,strong{font-weight:700;color:#000}[draggable]{cursor:move}.clearfix:after,.clearfix:before{content:"";display:table}.clearfix:after{clear:both}.clearfix{zoom:1}.textwrap,.textwrap td,.textwrap th{word-wrap:break-word;word-break:break-all}.textwrap-table{table-layout:fixed}.serif{font-family:Palatino,Optima,Georgia,serif}.typo-dl,.typo-form,.typo-hr,.typo-ol,.typo-p,.typo-pre,.typo-table,.typo-ul,.typo dl,.typo form,.typo hr,.typo ol,.typo p,.typo pre,.typo table,.typo ul,blockquote{margin-bottom:1rem}h1,h2,h3,h4,h5,h6{font-family:PingFang SC,Helvetica Neue,Verdana,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,sans-serif;color:#000;line-height:1.35}.typo-h1,.typo-h2,.typo-h3,.typo-h4,.typo-h5,.typo-h6,.typo h1,.typo h2,.typo h3,.typo h4,.typo h5,.typo h6{margin-top:1.2em;margin-bottom:.6em;line-height:1.35}.typo-h1,.typo h1{font-size:2em}.typo-h2,.typo h2{font-size:1.8em}.typo-h3,.typo h3{font-size:1.6em}.typo-h4,.typo h4{font-size:1.4em}.typo-h5,.typo-h6,.typo h5,.typo h6{font-size:1.2em}.typo-ul,.typo ul{margin-left:1.3em;list-style:disc}.typo-ol,.typo ol{list-style:decimal;margin-left:1.9em}.typo-ol ol,.typo-ol ul,.typo-ul ol,.typo-ul ul,.typo li ol,.typo li ul{margin-bottom:.8em;margin-left:2em}.typo-ol ul,.typo-ul ul,.typo li ul{list-style:circle}.typo-table td,.typo-table th,.typo table caption,.typo table td,.typo table th{border:1px solid #ddd;padding:.5em 1em;color:#666}.typo-table th,.typo table th{background:#fbfbfb}.typo-table thead th,.typo table thead th{background:hsla(0,0%,95%,.7)}.typo table caption{border-bottom:none}.typo-input,.typo-textarea{-webkit-appearance:none;border-radius:0}.typo-em,.typo em,caption,legend{color:#000;font-weight:inherit}.typo-em{position:relative}.typo-em:after{position:absolute;top:.65em;left:0;width:100%;overflow:hidden;white-space:nowrap;content:"\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB"}.typo img{max-width:100%}.common-content{font-weight:400;color:#353535;line-height:1.75rem;white-space:normal;word-break:normal;font-size:1rem}.common-content img{display:block;max-width:100%;background-color:#eee}.common-content audio,.common-content video{width:100%;background-color:#eee}.common-content center,.common-content font{margin-top:1rem;display:inline-block}.common-content center{width:100%}.common-content pre{margin-top:1rem;padding-left:0;padding-right:0;position:relative;overflow:hidden}.common-content pre code{font-size:.8rem;font-family:Consolas,Liberation Mono,Menlo,monospace,Courier;display:block;width:100%;box-sizing:border-box;padding-left:1rem;padding-right:1rem;overflow-x:auto}.common-content hr{border:none;margin-top:1.5rem;margin-bottom:1.5rem;border-top:1px solid #f5f5f5;height:1px;background:none}.common-content b,.common-content h1,.common-content h2,.common-content h3,.common-content h4,.common-content h5,.common-content strong{font-weight:700}.common-content h1,.common-content h2{font-size:1.125rem;margin-bottom:.45rem}.common-content h3,.common-content h4,.common-content h5{font-size:1rem;margin-bottom:.45rem}.common-content p{font-weight:400;color:#353535;margin-top:.15rem}.common-content .orange{color:#ff5a05}.common-content .reference{font-size:1rem;color:#888}.custom-rich-content h1{margin-top:0;font-weight:400;font-size:15.25px;border-bottom:1px solid #eee;line-height:2.8}.custom-rich-content li,.custom-rich-content p{font-size:14px;color:#888;line-height:1.6}table.hljs-ln{margin-bottom:0;border-spacing:0;border-collapse:collapse}table.hljs-ln,table.hljs-ln tbody,table.hljs-ln td,table.hljs-ln tr{box-sizing:border-box}table.hljs-ln td{padding:0;border:0}table.hljs-ln td.hljs-ln-numbers{min-width:15px;color:rgba(27,31,35,.3);text-align:right;white-space:nowrap;cursor:pointer;user-select:none}table.hljs-ln td.hljs-ln-code,table.hljs-ln td.hljs-ln-numbers{font-family:SFMono-Regular,Consolas,Liberation Mono,Menlo,Courier,monospace;font-size:12px;line-height:20px;vertical-align:top}table.hljs-ln td.hljs-ln-code{position:relative;padding-right:10px;padding-left:10px;overflow:visible;color:#24292e;word-wrap:normal;white-space:pre}video::-webkit-media-controls{overflow:hidden!important}video::-webkit-media-controls-enclosure{width:calc(100% + 32px);margin-left:auto}.button-cancel{color:#888;border:1px solid #888;border-radius:3px;margin-right:12px}.button-cancel,.button-primary{-ms-flex-positive:1;flex-grow:1;height:35px;display:inline-block;font-size:15px;text-align:center;line-height:36px}.button-primary{color:#fff;background-color:#ff5a05;border-radius:3px}@font-face{font-family:iconfont;src:url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.eot);src:url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.eot#iefix) format("embedded-opentype"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.woff) format("woff"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.ttf) format("truetype"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.svg#iconfont) format("svg")}@font-face{font-family:player-font;src:url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.eot);src:url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.eot#iefix) format("embedded-opentype"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.woff) format("woff"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.ttf) format("truetype"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.svg#player-font) format("svg")}.iconfont{font-family:iconfont!important;font-size:16px;font-style:normal;-webkit-font-smoothing:antialiased;-webkit-text-stroke-width:.2px;-moz-osx-font-smoothing:grayscale}html{background:#fff;min-height:100%;-webkit-tap-highlight-color:rgba(0,0,0,0)}body{width:100%}body.fixed{overflow:hidden;position:fixed;width:100vw;height:100vh}i{font-style:normal}a{word-wrap:break-word;-webkit-tap-highlight-color:rgba(0,0,0,0)}a:hover{text-decoration:none}.fade-enter-active,.fade-leave-active{transition:opacity .3s}.fade-enter,.fade-leave-to{opacity:0}.MathJax,.MathJax_CHTML,.MathJax_MathContainer,.MathJax_MathML,.MathJax_PHTML,.MathJax_PlainSource,.MathJax_SVG{outline:0}.ios-app-switch .js-audit{display:none}._loading_wrap_{position:fixed;width:100vw;height:100vh;top:50%;left:50%;transform:translate(-50%,-50%);z-index:999}._loading_div_class_,._loading_wrap_{display:-ms-flexbox;display:flex;-ms-flex-pack:center;justify-content:center;-ms-flex-align:center;align-items:center}._loading_div_class_{word-wrap:break-word;padding:.5rem .75rem;text-align:center;z-index:9999;font-size:.6rem;max-width:60%;color:#fff;border-radius:.25rem;-ms-flex-direction:column;flex-direction:column}._loading_div_class_ .message{color:#353535;font-size:16px;line-height:3}.spinner{animation:circle-rotator 1.4s linear infinite}.spinner *{line-height:0;box-sizing:border-box}@keyframes circle-rotator{0%{transform:rotate(0deg)}to{transform:rotate(270deg)}}.path{stroke-dasharray:187;stroke-dashoffset:0;transform-origin:center;animation:circle-dash 1.4s ease-in-out infinite,circle-colors 5.6s ease-in-out infinite}@keyframes circle-colors{0%{stroke:#ff5a05}to{stroke:#ff5a05}}@keyframes circle-dash{0%{stroke-dashoffset:187}50%{stroke-dashoffset:46.75;transform:rotate(135deg)}to{stroke-dashoffset:187;transform:rotate(450deg)}}.confirm-box-wrapper,.confirm-box-wrapper .mask{position:absolute;top:0;left:0;right:0;bottom:0}.confirm-box-wrapper .mask{background:rgba(0,0,0,.6)}.confirm-box-wrapper .confirm-box{position:fixed;top:50%;left:50%;width:267px;background:#fff;transform:translate(-50%,-50%);border-radius:7px}.confirm-box-wrapper .confirm-box .head{margin:0 18px;font-size:18px;text-align:center;line-height:65px;border-bottom:1px solid #d9d9d9}.confirm-box-wrapper .confirm-box .body{padding:18px;padding-bottom:0;color:#353535;font-size:12.5px;max-height:150px;overflow:auto}.confirm-box-wrapper .confirm-box .foot{display:-ms-flexbox;display:flex;-ms-flex-direction:row;flex-direction:row;padding:18px}.confirm-box-wrapper .confirm-box .foot .button-cancel{border:1px solid #d9d9d9}.hljs{display:block;overflow-x:auto;padding:.5em;color:#333;background:#f8f8f8}.hljs-comment,.hljs-quote{color:#998;font-style:italic}.hljs-keyword,.hljs-selector-tag,.hljs-subst{color:#333;font-weight:700}.hljs-literal,.hljs-number,.hljs-tag .hljs-attr,.hljs-template-variable,.hljs-variable{color:teal}.hljs-doctag,.hljs-string{color:#d14}.hljs-section,.hljs-selector-id,.hljs-title{color:#900;font-weight:700}.hljs-subst{font-weight:400}.hljs-class .hljs-title,.hljs-type{color:#458;font-weight:700}.hljs-attribute,.hljs-name,.hljs-tag{color:navy;font-weight:400}.hljs-link,.hljs-regexp{color:#009926}.hljs-bullet,.hljs-symbol{color:#990073}.hljs-built_in,.hljs-builtin-name{color:#0086b3}.hljs-meta{color:#999;font-weight:700}.hljs-deletion{background:#fdd}.hljs-addition{background:#dfd}.hljs-emphasis{font-style:italic}.hljs-strong{font-weight:700}


    </style>
    <style type="text/css">
        .button-cancel[data-v-87ffcada]{color:#888;border:1px solid #888;border-radius:3px;margin-right:12px}.button-cancel[data-v-87ffcada],.button-primary[data-v-87ffcada]{-webkit-box-flex:1;-ms-flex-positive:1;flex-grow:1;height:35px;display:inline-block;font-size:15px;text-align:center;line-height:36px}.button-primary[data-v-87ffcada]{color:#fff;background-color:#ff5a05;border-radius:3px}.pd[data-v-87ffcada]{padding-left:1.375rem;padding-right:1.375rem}.article[data-v-87ffcada]{max-width:70rem;margin:0 auto}.article .article-unavailable[data-v-87ffcada]{color:#fa8919;font-size:15px;font-weight:600;line-height:24px;border-radius:5px;padding:12px;background-color:#f6f7fb;margin-top:20px}.article .article-unavailable .iconfont[data-v-87ffcada]{font-size:12px}.article .main[data-v-87ffcada]{padding:1.25rem 0;margin-bottom:52px}.article-title[data-v-87ffcada]{color:#353535;font-weight:400;line-height:1.65rem;font-size:1.34375rem}.article-info[data-v-87ffcada]{color:#888;font-size:.9375rem;margin-top:1.0625rem}.article-content[data-v-87ffcada]{margin-top:1.0625rem}.article-content.android video[data-v-87ffcada]::-webkit-media-controls-fullscreen-button{display:none}.copyright[data-v-87ffcada]{color:#b2b2b2;padding-bottom:20px;margin-top:20px;font-size:13px}.audio-player[data-v-87ffcada]{width:100%;margin:20px 0}.to-comment[data-v-87ffcada]{overflow:hidden;padding-top:10px;margin-bottom:-30px}.to-comment a.button-primary[data-v-87ffcada]{float:right;height:20px;font-size:12px;line-height:20px;padding:4px 8px;cursor:pointer}.article-comments[data-v-87ffcada]{margin-top:2rem}.article-comments h2[data-v-87ffcada]{text-align:center;color:#888;position:relative;z-index:1;margin-bottom:1rem}.article-comments h2[data-v-87ffcada]:before{border-top:1px dotted #888;content:"";position:absolute;top:56%;left:0;width:100%;z-index:-1}.article-comments h2 span[data-v-87ffcada]{font-size:15.25px;font-weight:400;padding:0 1rem;background:#fff;display:inline-block}.article-sub-bottom[data-v-87ffcada]{z-index:10;cursor:pointer}.switch-btns[data-v-87ffcada]{height:76px;cursor:pointer;padding-top:24px;padding-bottom:24px;border-bottom:10px solid #f6f7fb;position:relative}.switch-btns[data-v-87ffcada]:before{content:" ";height:1px;background:#e8e8e8;position:absolute;top:0;left:0;-webkit-box-sizing:border-box;box-sizing:border-box;left:1.375rem;right:1.375rem}.switch-btns .btn[data-v-87ffcada]{height:38px;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.switch-btns .btn .tag[data-v-87ffcada]{-webkit-box-flex:0;-ms-flex:0 0 62px;flex:0 0 62px;text-align:center;color:#888;font-size:14px;border-radius:10px;height:22px;line-height:22px;background:#f6f7fb;font-weight:400}.switch-btns .btn .txt[data-v-87ffcada]{margin-left:10px;-webkit-box-flex:1;-ms-flex:1 1 auto;flex:1 1 auto;color:#888;font-size:15px;height:22px;line-height:22px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;font-weight:400}@media (max-width:769px){.article .breadcrumb[data-v-87ffcada]{padding-top:10px;padding-bottom:10px}}


    </style>

    <style type="text/css">
        .comment-item{list-style-position:inside;width:100%;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;margin-bottom:1rem}.comment-item a{border-bottom:none}.comment-item .avatar{width:2.625rem;height:2.625rem;-ms-flex-negative:0;flex-shrink:0;border-radius:50%}.comment-item .info{margin-left:.5rem;-webkit-box-flex:1;-ms-flex-positive:1;flex-grow:1}.comment-item .info .hd{width:100%;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-pack:justify;-ms-flex-pack:justify;justify-content:space-between;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.comment-item .info .hd .username{color:#888;font-size:15.25px;font-weight:400;line-height:1.2}.comment-item .info .hd .control{display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.comment-item .info .hd .control .btn-share{color:#888;font-size:.75rem;margin-right:1rem}.comment-item .info .hd .control .btn-praise{display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-align:center;-ms-flex-align:center;align-items:center;font-size:15.25px;text-decoration:none}.comment-item .info .hd .control .btn-praise i{color:#888;display:inline-block;font-size:.75rem;margin-right:.3rem;margin-top:-.01rem}.comment-item .info .hd .control .btn-praise i.on,.comment-item .info .hd .control .btn-praise span{color:#ff5a05}.comment-item .info .bd{color:#353535;font-size:15.25px;font-weight:400;white-space:normal;word-break:break-all;line-height:1.6}.comment-item .info .time{color:#888;font-size:9px;line-height:1}.comment-item .info .reply .reply-hd{font-size:15.25px}.comment-item .info .reply .reply-hd span{margin-left:-12px;color:#888;font-weight:400}.comment-item .info .reply .reply-hd i{color:#ff5a05;font-size:15.25px}.comment-item .info .reply .reply-content{color:#353535;font-size:15.25px;font-weight:400;white-space:normal;word-break:break-all}.comment-item .info .reply .reply-time{color:#888;font-size:9px}


    </style>
</head>
<body>
<div id="app">


    <div data-v-87ffcada="" class="article" id="watermark">
        <div data-v-87ffcada="" class="main main-app">
            <h1 data-v-87ffcada="" class="article-title pd">
                40讲insert语句的锁为什么这么多
            </h1>
            <div data-v-87ffcada="" class="article-content typo common-content pd"><img data-v-87ffcada=""
                                                                                        src="https://static001.geekbang.org/resource/image/cd/d1/cdc3c75524018404710ada288e0e07d1.jpg">

                <div>
                    <audio controls="controls" height="100" width="100">
                        <source src="40讲insert语句的锁为什么这么多.mp3" type="audio/mp3" />
                        <embed height="100" width="100" src="40讲insert语句的锁为什么这么多.mp3" />
                    </audio>
                </div>
                <div data-v-87ffcada="" id="article-content" class="">
                    <div class="text">
                        <p>在上一篇文章中，我提到MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。</p><p>因此，insert语句是一个很轻量的操作。不过，这个结论对于“普通的insert语句”才有效。也就是说，还有些insert语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后就立马释放自增锁。</p><p>那么，今天这篇文章，我们就一起来聊聊这个话题。</p><h1>insert … select 语句</h1><p>我们先从昨天的问题说起吧。表t和t2的表结构、初始化数据语句如下，今天的例子我们还是针对这两个表展开。</p><pre><code>CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t
</code></pre><p>现在，我们一起来看看为什么在可重复读隔离级别下，binlog_format=statement时执行：</p><pre><code>insert into t2(c,d) select c,d from t;
</code></pre><p>这个语句时，需要对表t的所有行和间隙加锁呢？</p><p>其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：</p><p><img src="https://static001.geekbang.org/resource/image/33/86/33e513ee55d5700dc67f32bcdafb9386.png" alt=""></p><center><span class="reference">图1 并发insert场景</span></center><p>实际的执行效果是，如果session B先执行，由于这个语句对表t主键索引加了(-∞,1]这个next-key lock，会在语句执行完成后，才允许session A的insert语句执行。</p><p>但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。于是，在binlog_format=statement的情况下，binlog里面就记录了这样的语句序列：</p><!-- [[[read_end]]] --><pre><code>insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;
</code></pre><p>这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。</p><h1>insert 循环写入</h1><p>当然了，执行insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。</p><p>如果现在有这么一个需求：要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。</p><p>此时，我们可以这么写这条SQL语句 ：</p><pre><code>insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
</code></pre><p>这个语句的加锁范围，就是表t索引c上的(4,supremum]这个next-key lock和主键索引上id=4这一行。</p><p>它的执行流程也比较简单，从表t中按照索引c倒序，扫描第一行，拿到结果写入到表t2中。</p><p>因此整条语句的扫描行数是1。</p><p>这个语句执行的慢查询日志（slow log），如下图所示：</p><p><img src="https://static001.geekbang.org/resource/image/3e/74/3efdf8256309a44e23d93089459eda74.png" alt=""></p><center><span class="reference">图2 慢查询日志--将数据插入表t2</span></center><p>通过这个慢查询日志，我们看到Rows_examined=1，正好验证了执行这条语句的扫描行数为1。</p><p>那么，如果我们是要把这样的一行数据插入到表t中的话：</p><pre><code>insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
</code></pre><p>语句的执行流程是怎样的？扫描行数又是多少呢？</p><p>这时候，我们再看慢查询日志就会发现不对了。</p><p><img src="https://static001.geekbang.org/resource/image/6f/18/6f90b04c09188bff11dae6e788abb918.png" alt=""></p><center><span class="reference">图3 慢查询日志--将数据插入表t</span></center><p>可以看到，这时候的Rows_examined的值是5。</p><p>我在前面的文章中提到过，希望你都能够学会用explain的结果来“脑补”整条语句的执行过程。今天，我们就来一起试试。</p><p>如图4所示就是这条语句的explain结果。</p><p><img src="https://static001.geekbang.org/resource/image/d7/2a/d7270781ee3f216325b73bd53999b82a.png" alt=""></p><center><span class="reference">图4 explain结果</span></center><p>从Extra字段可以看到“Using temporary”字样，表示这个语句用到了临时表。也就是说，执行过程中，需要把表t的内容读出来，写入临时表。</p><p>图中rows显示的是1，我们不妨先对这个语句的执行流程做一个猜测：如果说是把子查询的结果读出来（扫描1行），写入临时表，然后再从临时表读出来（扫描1行），写回表t中。那么，这个语句的扫描行数就应该是2，而不是5。</p><p>所以，这个猜测不对。实际上，Explain结果里的rows=1是因为受到了limit 1 的影响。</p><p>从另一个角度考虑的话，我们可以看看InnoDB扫描了多少行。如图5所示，是在执行这个语句前后查看Innodb_rows_read的结果。</p><p><img src="https://static001.geekbang.org/resource/image/48/d7/489281d8029e8f60979cb7c4494010d7.png" alt=""></p><center><span class="reference">图5 查看 Innodb_rows_read变化</span></center><p>可以看到，这个语句执行前后，Innodb_rows_read的值增加了4。因为默认临时表是使用Memory引擎的，所以这4行查的都是表t，也就是说对表t做了全表扫描。</p><p>这样，我们就把整个执行过程理清楚了：</p><ol>
<li>
<p>创建临时表，表里有两个字段c和d。</p>
</li>
<li>
<p>按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。</p>
</li>
<li>
<p>由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。</p>
</li>
</ol><p>也就是说，这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。</p><p>至于这个语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。</p><p>由于实现上这个语句没有在子查询中就直接使用limit 1，从而导致了这个语句的执行需要遍历整个表t。它的优化方法也比较简单，就是用前面介绍的方法，先insert into到临时表temp_t，这样就只需要扫描一行；然后再从表temp_t里面取出这行数据插入表t1。</p><p>当然，由于这个语句涉及的数据量很小，你可以考虑使用内存临时表来做这个优化。使用内存临时表优化时，语句序列的写法如下：</p><pre><code>create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;
</code></pre><h1>insert 唯一键冲突</h1><p>前面的两个例子是使用insert … select的情况，接下来我要介绍的这个例子就是最常见的insert语句出现唯一键冲突的情况。</p><p>对于有唯一键的表，插入数据时出现唯一键冲突也是常见的情况了。我先给你举一个简单的唯一键冲突的例子。</p><p><img src="https://static001.geekbang.org/resource/image/83/ca/83fb2d877932941b230d6b5be8cca6ca.png" alt=""></p><center><span class="reference">图6 唯一键冲突加锁</span></center><p>这个例子也是在可重复读（repeatable read）隔离级别下执行的。可以看到，session B要执行的insert语句进入了锁等待状态。</p><p>也就是说，session A执行的insert语句，发生主键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。我们前面说过，一个next-key lock就是由它右边界的值定义的。这时候，session A持有索引c上的(5,10]共享next-key lock（读锁）。</p><p>至于为什么要加这个读锁，其实我也没有找到合理的解释。从作用上来看，这样做可以避免这一行被别的事务删掉。</p><p>这里<a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html">官方文档</a>有一个描述错误，认为如果冲突的是主键索引，就加记录锁，唯一索引才加next-key lock。但实际上，这两类索引冲突加的都是next-key lock。</p><blockquote>
<p>备注：这个bug，是我在写这篇文章查阅文档时发现的，已经<a href="https://bugs.mysql.com/bug.php?id=93806">发给官方</a>并被verified了。</p>
</blockquote><p>有同学在前面文章的评论区问到，在有多个唯一索引的表中并发插入数据时，会出现死锁。但是，由于他没有提供复现方法或者现场，我也无法做分析。所以，我建议你在评论区发问题的时候，尽量同时附上复现方法，或者现场信息，这样我才好和你一起分析问题。</p><p>这里，我就先和你分享一个经典的死锁场景，如果你还遇到过其他唯一键冲突导致的死锁场景，也欢迎给我留言。</p><p><img src="https://static001.geekbang.org/resource/image/63/2d/63658eb26e7a03b49f123fceed94cd2d.png" alt=""></p><center><span class="reference">图7 唯一键冲突--死锁</span></center><p>在session A执行rollback语句回滚的时候，session C几乎同时发现死锁并返回。</p><p>这个死锁产生的逻辑是这样的：</p><ol>
<li>
<p>在T1时刻，启动session A，并执行insert语句，此时在索引c的c=5上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁（如果你的印象模糊了，可以回顾下<a href="https://time.geekbang.org/column/article/75659">第21篇文章</a>介绍的加锁规则）。</p>
</li>
<li>
<p>在T2时刻，session B要执行相同的insert语句，发现了唯一键冲突，加上读锁；同样地，session C也在索引c上，c=5这一个记录上，加了读锁。</p>
</li>
<li>
<p>T3时刻，session A回滚。这时候，session B和session C都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁。</p>
</li>
</ol><p>这个流程的状态变化图如下所示。</p><p><img src="https://static001.geekbang.org/resource/image/3e/b8/3e0bf1a1241931c14360e73fd10032b8.jpg" alt=""></p><center><span class="reference">图8 状态变化图--死锁</span></center><h1>insert into … on duplicate key update</h1><p>上面这个例子是主键冲突后直接报错，如果是改写成</p><pre><code>insert into t values(11,10,10) on duplicate key update d=100;
</code></pre><p>的话，就会给索引c上(5,10] 加一个排他的next-key lock（写锁）。</p><p><strong>insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。</strong></p><p>注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。</p><p>现在表t里面已经有了(1,1,1)和(2,2,2)这两行，我们再来看看下面这个语句执行的效果：</p><p><img src="https://static001.geekbang.org/resource/image/5f/02/5f384d6671c87a60e1ec7e490447d702.png" alt=""></p><center><span class="reference">图9 两个唯一键同时冲突</span></center><p>可以看到，主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行。</p><p>需要注意的是，执行这条语句的affected rows返回的是2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert和update都认为自己成功了，update计数加了1， insert计数也加了1。</p><h1>小结</h1><p>今天这篇文章，我和你介绍了几种特殊情况下的insert语句。</p><p>insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。</p><p>而如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。</p><p>insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-key lock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。</p><p>最后，我给你留一个问题吧。</p><p>你平时在两个表之间拷贝数据用的是什么方法，有什么注意事项吗？在你的应用场景里，这个方法，相较于其他方法的优势是什么呢？</p><p>你可以把你的经验和分析写在评论区，我会在下一篇文章的末尾选取有趣的评论来和你一起分析。感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。</p><h1>上期问题时间</h1><p>我们已经在文章中回答了上期问题。</p><p>有同学提到，如果在insert … select 执行期间有其他线程操作原表，会导致逻辑错误。其实，这是不会的，如果不加锁，就是快照读。</p><p>一条语句执行期间，它的一致性视图是不会修改的，所以即使有其他事务修改了原表的数据，也不会影响这条语句看到的数据。</p><p>评论区留言点赞板：</p><blockquote>
<p>@长杰 同学回答得非常准确。</p>
</blockquote><p><img src="https://static001.geekbang.org/resource/image/09/77/09c1073f99cf71d2fb162a716b5fa577.jpg" alt=""></p>
                    </div>
                </div>

            </div>
            <div data-v-87ffcada="" class="article-comments pd"><h2 data-v-87ffcada=""><span
                    data-v-87ffcada="">精选留言</span></h2>
                <ul data-v-87ffcada="">

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/14/8c/94/5282994c.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">huolang</span>
                            </div>
                            <div class="bd">老师，死锁的例子，关于sessionA拿到的c=5的记录锁，sessionB和sessionC发现唯一键冲突会加上读锁我有几个疑惑：<br>1. sessionA拿到的c=5的记录锁是写锁吗？<br>2. 为什么sessionB和sessionC发现唯一键冲突会加上读锁？<br>3. 如果sessionA拿到c=5的记录所是写锁，那为什么sessionB和sessionC还能加c=5的读锁，写锁和读锁不应该是互斥的吗？<br>4.  sessionA还没有提交，为什么sessionB和sessionC能发现唯一键冲突？ <br></div>
                            <span class="time">2019-02-13 17:33</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">1. 是的<br>2. 这个我觉得是为了防止这个记录再被删除（不过这个理由不是很硬，我还没有找到其他解释<br>3. 互斥的，所以这两个语句都在等待。注意next-key lock是由间隙锁和记录锁组成的哦， 间隙锁加成功了的。好问题。<br>4. 还没有提交，但是这个记录已经作为最新记录写进去了，复习一下08篇哈</p>
                                <p class="reply-time">2019-02-14 10:45</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e3/2e/77ad18f4.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">滔滔</span>
                            </div>
                            <div class="bd">老师，之前提到的一个有趣的问题&quot;A、B两个用户，如果互相喜欢，则成为好友。设计上是有两张表，一个是like表，一个是friend表，like表有user_id、liker_id两个字段，我设置为复合唯一索引即uk_user_id_liker_id。语句执行顺序是这样的：<br>以A喜欢B为例：<br>1、先查询对方有没有喜欢自己（B有没有喜欢A）<br>select * from like where user_id = B and liker_id = A<br>2、如果有，则成为好友<br>insert into friend<br>3、没有，则只是喜欢关系<br>insert into like&quot;，这个问题中如果把select语句改成&quot;当前读&quot;，则当出现A,B两个人同时喜欢对方的情况下，是不是会出现由于&quot;当前读&quot;加的gap锁导致后面insert语句阻塞，从而发生死锁？ <br></div>
                            <span class="time">2019-02-13 14:28</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">好问题<br><br>这种情况下一般是造成锁等待，不会造成死锁吧 😆</p>
                                <p class="reply-time">2019-02-14 10:18</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJCscgdVibmoPyRLRaicvk6rjTJxePZ6VFHvGjUQvtfhCS6kO4OZ1AVibbhNGKlWZmpEFf2yA6ptsqHw/132" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">夹心面包</span>
                            </div>
                            <div class="bd"><br>1 关于insert造成死锁的情况,我之前做过测试,事务1并非只有insert,delete和update都可能造成死锁问题,核心还是插入唯一值冲突导致的.我们线上的处理办法是 1 去掉唯一值检测 2减少重复值的插入 3降低并发线程数量<br>2 关于数据拷贝大表我建议采用pt-archiver,这个工具能自动控制频率和速度,效果很不错,提议在低峰期进行数据操作 <br></div>
                            <span class="time">2019-02-13 10:50</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">👍，这两点都是很有用的建议</p>
                                <p class="reply-time">2019-02-13 15:39</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/f9/48/bf570bab.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">sonic</span>
                            </div>
                            <div class="bd">你好，<br>我想问下文章中关于为什么需要创建临时表有这一句话：<br>如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。<br><br>我的疑问是：既然隔离级别是可重复读，照理来说新插入的的记录应该不会参与计算逻辑呀。 <br></div>
                            <span class="time">2019-02-14 18:17</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e5/39/951f89c8.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">信信</span>
                            </div>
                            <div class="bd">老师好，<br>图6下方“发生主键冲突的时候”是不是应该改为“发生唯一键冲突的时候”？因为c不是主键。<br>还有，图7下方：T2时刻session b 发现“唯一键冲突”，这里为啥不是锁冲突？因为如果没有锁冲突，仅有唯一键冲突，就对应图6的情况，这时加的是next-key lock，而不仅仅是记录锁了。 <br></div>
                            <span class="time">2019-02-14 17:45</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e2/9d/fbbd4611.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">cyberbit</span>
                            </div>
                            <div class="bd">在表件迁移拷贝数据，用的pt-archiver工具做的，但是它不支持utf8mb4字符集，这个问题一直困扰我，不知道怎么解决。 <br></div>
                            <span class="time">2019-02-14 16:17</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/03/f7/3a493bec.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">老杨同志</span>
                            </div>
                            <div class="bd">课后问题：<br>      我用的最多还是insert into select 。如果数量比较大，会加上limit 100,000这种。并且看看后面的select条件是否走索引。缺点是会锁select的表。方法二：导出成excel，然后拼sql 成 insert into values(),(),()的形式。方法3，写类似淘宝调动的定时任务，任务的逻辑是查询100条记录，然后多个线程分到几个任务执行，比如是个线程，每个线程10条记录，插入后，在查询新的100条记录处理。<br>       <br></div>
                            <span class="time">2019-02-13 21:52</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">👍</p>
                                <p class="reply-time">2019-02-14 14:59</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTI01GCG9MdMibbI3ykWu9zicMTTaE06bGzGRrb0MKDZaSAmcAmOUNysBaBYB1CB2atURtzbcHrdreqg/132" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">颜海航</span>
                            </div>
                            <div class="bd">[Note] Multi-threaded slave: Coordinator has waited 8551 times hitting slave_pending_jobs_<br>size_max; current event size = 8198. 老师 我们数据库一直报这个错，然后数据库就进行crash recovery，是是什么状况。。 <br></div>
                            <span class="time">2019-02-13 19:39</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/57/6e/dd0eee5f.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">夜空中最亮的星（华仔）</span>
                            </div>
                            <div class="bd">老师威武。<br>终于追上更新了。 <br></div>
                            <span class="time">2019-02-13 18:54</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/df/e7/e3c450c2.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">lionetes</span>
                            </div>
                            <div class="bd">mysql&gt; insert into t select null,5,5; 已经又4条记录<br>mysql&gt; select * from t;<br>| 1 | 1 | 1 |<br>| 2 | 2 | 100 |<br>| 3 | 3 | 3 |<br>| 4 | 4 | 4 |<br>| 5 | 5 | 5 |<br>+----+------+------+<br>5 rows in set (0.00 sec)<br><br>mysql&gt; select last_insert_id();<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>| 5 |<br>+------------------+<br>1 row in set (0.00 sec)<br><br>mysql&gt; select * from t2; 已经有三条记录<br>+----+------+------+<br>| id | c | d |<br>+----+------+------+<br>| 5 | 1 | NULL |<br>| 6 | 6 | 6 |<br>| 7 | 7 | 7 |<br>+----+------+------+<br>3 rows in set (0.01 sec)<br>mysql&gt; select last_insert_id(); 此处的自增ID 是否理解为 最近一次的 insert 操作的 获取的ID<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>| 5 |<br>+------------------+<br>1 row in set (0.00 sec)<br><br>mysql&gt; insert into t2 select null,8,8;<br>Query OK, 1 row affected (0.01 sec)<br>Records: 1 Duplicates: 0 Warnings: 0<br><br>mysql&gt; select last_insert_id();<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>| 8 |<br>+------------------+<br>1 row in set (0.00 sec)<br><br>mysql&gt; select * from t2;<br>+----+------+------+<br>| id | c | d |<br>+----+------+------+<br>| 5 | 1 | NULL |<br>| 6 | 6 | 6 |<br>| 7 | 7 | 7 |<br>| 8 | 8 | 8 |<br>+----+------+------+<br>4 rows in set (0.00 sec)<br>mysql&gt; select last_insert_id(); 此处的自增ID 是否理解为 最近一次的 insert 操作的 获取的ID<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>| 8 |<br>+------------------+<br>1 row in set (0.00 sec)<br>上面的 ID 与那个表没有关系, 至于当前session insert 最新一次记录为准 <br>mysql&gt; select last_insert_id();<br>|                8 |<br>+------------------+<br>1 row in set (0.00 sec)<br>mysql&gt; insert into t2 select 19,19,19;<br>mysql&gt; select last_insert_id();<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>|                8 |<br>+------------------+<br>1 row in set (0.00 sec)<br>mysql&gt; insert into t2 select null,20,20;<br>Query OK, 1 row affected (0.01 sec)<br>Records: 1  Duplicates: 0  Warnings: 0<br><br>mysql&gt; select last_insert_id();<br>+------------------+<br>| last_insert_id() |<br>+------------------+<br>|               20 |<br>+------------------+<br>ID 除非为空的时候 才能获取到新的last_insert_id <br></div>
                            <span class="time">2019-02-13 17:28</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">很好的验证👍<br>你再试试一个insert插入多行的例子，就完整了😆</p>
                                <p class="reply-time">2019-02-14 10:32</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/df/90/68408c1b.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">郭烊千玺</span>
                            </div>
                            <div class="bd">有个问题想请教下大神  information_schema.tables  表里的这三个字段data_length  data_free  index_length的值准确吗，mysql内部是怎么计算每个表的这个三个值的？在没有碎片的情况下，实践上用du 命令统计的ibd的大小和这几个字段的值感觉差别很大，所以很想知道这几个字段的值得准确度如何，还是仅供参考，因为实践中可能需要知道是否有碎片，如果date_free值不准确，而盲目的alter table一下，表大的话代价很高啊 求回答啊 感觉这也是很多dba关心的一个问题 <br></div>
                            <span class="time">2019-02-13 17:26</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/0f/db/80/6b7629d7.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">roaming</span>
                            </div>
                            <div class="bd">MySQL8.0.12环境下，<br>执行insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);<br>slow log Rows_examined: 2<br>Innodb_rows_read 的值增加1<br><br>是不是MySQL8进行了优化，先把子查询的结果读出来，再写入临时表？ <br></div>
                            <span class="time">2019-02-13 16:28</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">看来是的了，<br><br>👍，很好的验证，我加到明天文章末尾说明</p>
                                <p class="reply-time">2019-02-14 16:41</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/14/00/f0/08409e78.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">一大只😴</span>
                            </div>
                            <div class="bd">老师，我想问下：insert 语句出现唯一键冲突，会加next-key lock，而产生死锁的例子中，同样也是唯一键冲突却只加了记录锁，然后我按照唯一键冲突中的两个例子试了试<br>1、比如t表中有两条记录(19,19,19)，(22,22,22)，这时候我再insert (22,22,22)造成了主键冲突，这时候加的就是(19,22]的next-key lock，这个insert为啥不是等值查询？<br>2、根据死锁的例子，我又在t表中准备插入一行<br>      session A :begin; insert into t values (25,25,25)<br>      session B :insert into t values (25,25,25)  这时候sessionB锁等待<br>      session C：insert into t values (24,24,24)  锁等待，等B锁等待超时，session C插入成功<br>      那这里的session B应该是加了个(22,25]的next-key lock，并没有因为是唯一键退化成记录锁<br>我想死锁的例子中t表已经有了(1,1,1),(2,2,2),(3,3,3),(4,4,4)4条记录，这时候insert (null,5,5)，是不是加的(4,5]这个next-key lock，由于是整型并且间隙非常小，所以将他当成记录锁？ <br></div>
                            <span class="time">2019-02-13 14:26</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">“那这里的session B应该是加了个(22,25]的next-key lock，并没有因为是唯一键退化成记录锁” 由于insert主键冲突导致的锁，是不会退化的。<br><br>session B 加了next-key lock，<br>这样session C插入也要等待，然后等session B超时，释放了这个next-key lock，session C就可以执行了。<br>跟我们文中说的是一致的哦。<br><br><br>你这个验证挺合理的呀，<br><br>不会有因为“间隙非常小，所以将他当成记录锁”这种逻辑哈, a和a+1之间也是有间隙的😆。<br><br>不过这个是个好的实验和好问题👍</p>
                                <p class="reply-time">2019-02-14 16:40</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e3/2e/77ad18f4.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">滔滔</span>
                            </div>
                            <div class="bd">老师您好，想问一下next key lock是gap锁和行锁的组合，但究竟是gap锁和共享锁还是排它锁的组合是不是要看具体的sql语句？具体哪些sql语句中的next key lock是由共享锁组成，哪些是由排它锁组成呢？🤔 <br></div>
                            <span class="time">2019-02-13 13:03</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">一般select ...lock in share mode就是共享锁；<br>select ... for update 和 IUD语句，就是排他锁。</p>
                                <p class="reply-time">2019-02-14 10:15</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/11/7c/56/6462173d.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">K先生</span>
                            </div>
                            <div class="bd">表结构<br>CREATE TABLE `PushTask` (<br>  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;主键ID，自增长&#39;,<br>  `DpId` varchar(100) NOT NULL DEFAULT &#39;&#39;,<br>  `DetailId` int(11) NOT NULL,<br>  `SceneType` tinyint(1) DEFAULT NULL,<br>  `DataId` int(11) DEFAULT NULL,<br>  `SendTime` datetime NOT NULL,<br>  `AddTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,<br>  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,<br>  `Status` tinyint(1) DEFAULT &#39;0&#39;,<br>  `SendDate` date DEFAULT NULL,<br>  PRIMARY KEY (`Id`),<br>  UNIQUE KEY `IX_DpId_SendDate_DetailId` (`DpId`,`SendDate`,`DetailId`),<br>  KEY `IX_UpdateTime` (`UpdateTime`),<br>  KEY `IX_SendTime` (`SendTime`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br><br>请问老师，为啥insert ... ON DUPLICATE KEY UPDATE<br>        UpdateTime = now()的时候会出现死锁？ <br></div>
                            <span class="time">2019-02-13 10:22</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">show engine innodb status\G<br>里面lastest deadlock那一段发来看下哈<br></p>
                                <p class="reply-time">2019-02-13 16:41</p>
                            </div>

                        </div>
                    </li>


                </ul>
            </div>
        </div>
    </div>
</div>
</body>
</html>