select lock in share mode 与 select for update

<p>参考链接：<a href="denied:" target="">https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html</a></p><p>最近一直在了解幻读方面的问题，就遇到了这两种命令，现在自己学习实践一下。</p><p><br></p><p>先准备一张表：</p><pre>CREATE TABLE IF NOT EXISTS `subject` (<br> `subject_id` int(11) NOT NULL AUTO_INCREMENT,<br> `subject_name` varchar(20) NOT NULL,<br> PRIMARY KEY (`subject_id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;<br><br>INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES<br> (1, '语文'),<br> (2, '数学'),<br> (3, '英语');</pre><p><br></p><p>测试工具：HeidiSQL客户端工具（你也可以用Navicat,SQLyog这些）。</p><div><br></div><div>一开始，我就只打开一个窗口，怎么测都测不出应有的效果。重新开个新窗口，也就相当于新会话，实践就ok了。</div><blockquote><div>需要多个会话进行测试，同一个窗口下多个查询属于同一个会话，所以会无法测试。<br></div></blockquote><div>文中将用WA，WB，WC，WD分别代表四个会话哈。</div><div><h3><br></h3></div><div><h3>SELECT ... LOCK IN SHARE MODE：</h3></div><div>第一点：在要查询的行记录上加上共享锁，其它会话可以读取这些记录，但不可修改它们，除非加共享锁的那个事务执行完成；</div><div>第二点：如果当前事务之前有其它事务修改了数据，那么当前的查询要等到其它事务结束，并查询出最新的数据。</div><div>第三点：在当前事务给某些记录加了共享锁，并不代表在当前事务里就能够修改这些记录。（有可能在当前事务之前，有其它会话的事务在同样的数据上也加了共享锁）</div><div><br></div><div>实践过程：</div><div>WA会话下，查询id=1的科目：</div><div><pre><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">lock</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-function"""="">in</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">share</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">mode</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div><br></div><div>WB会话下，更新id=1的科目：</div><div><pre><!--?xml version="1.0" encoding="UTF-8"?--><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">update</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">set</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_name</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-string"""="">'测试2'</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>结果报错：</div><div><pre>Lock wait timeout exceeded; try restarting transaction<br></pre></div><div><br></div><div>再将WA下的事务提交：</div><div><pre><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">lock</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-function"""="">in</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">share</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">mode</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">commit</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>接着执行WB下的事务，发现更新就可以执行了。</div><div>这样就验证了上面共享锁的第一点：</div><blockquote><div>在要查询的行记录上加上共享锁，其它会话可以读取这些记录，但不可修改它们，除非加共享锁的那个事务执行完成</div></blockquote><div><br></div><div>让我们继续验证后面的两点。</div><div>在WA中新建查询：</div><div><pre><code><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1 </span></code></pre></div><div>发现查询出来的仍然是旧的数据。</div><div><br></div><div>在WA中依然执行如下查询：</div><div><pre><!--?xml version="1.0" encoding="UTF-8"?--><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">lock</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-function"""="">in</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">share</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">mode</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>同样会报等待锁超时的提示。</div><div>但我在WB中，将更新事务提交，上面WA中的查询，就可以查询出数据了。</div><div><pre><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">update</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">set</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_name</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-string"""="">'测试2'</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">commit</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>这也就验证了lock in share mode用法的第二点：</div><blockquote><div>如果当前事务之前有其它事务修改了数据，那么当前的查询要等到其它事务结束，并查询出最新的数据</div></blockquote><div>以上，在WB会话下的更新事务还没提交时，此时相当于给subject_id=1的记录加了排他锁，但由于innodb引擎的MVCC机制，这并不影响它去读取subject_id=1这笔记录的数据，尽管读的数据是更新之前的旧数据。</div><div><br></div><div>关于MVCC机制的理解，请参考下面这篇文章：</div><div><br></div><div><br></div><div>接着验证第三点。</div><div>在WC中新建一个事务：</div><div><pre><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">2</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">lock</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-function"""="">in</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">share</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">mode</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">update</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">set</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_name</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-delimitedidentifier"""="">"数学1"</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-number"""="">2</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>查询有结果，结果是数学，而不是数学1。能够执行是因为subject_id=2这笔记录在此之前并没有其它会话给它加锁。</div><div><br></div><div>但如果WC下执行如下语句就会报错，因为id=1的行记录已经之前在WA下添加了共享锁，所以WC下即使再次加了共享锁，也是无法更新id=1的记录的。</div><div><pre><code><span class="" "sql1-reservedword"""="">start</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">transaction</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">select</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">*</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">from</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-number"""="">1</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">lock</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-function"""="">in</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">share</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">mode</span><span class="" "sql1-symbol"""="">; </span><span class="" "sql1-reservedword"""="">update</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-tablename"""="">subject</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">set</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_name</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-delimitedidentifier"""="">"测试4"</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-reservedword"""="">where</span><span class="" "sql1-space"""=""> </span><span class="" "sql1-identifier"""="">subject_id</span><span class="" "sql1-symbol"""="">=</span><span class="" "sql1-number"""="">1</span><span class="" "sql1-symbol"""="">; </span></code></pre></div><div>到这儿，共享锁的第三个特点也验证完了：</div><blockquote><div>在当前事务给某些记录加了共享锁，并不代表在当前事务里就能够修改这些记录。（有可能在当前事务之前，有其它会话的事务在同样的数据上也加了共享锁）</div></blockquote><div><br></div><div><h3>SELECT ... FOR UPDATE</h3></div><div>根据开篇英文文档里说的来理解：</div><div>当查询遇到索引列时，使用此语法，就会给查出的数据记录以及相关的索引入口(index entries,这个暂没有理解)加锁，此效果如同在查出的记录行上执行update更新语句一样。</div><div><br></div><div>文中写到，使用此语句可有此3个效果：</div><div>1.其它会话的事务将无法更新这些查出的记录</div><div>2.其它会话的事务也将无法给这些记录加共享锁，即执行selelct ... lock in share mode</div><div>3.在某些事务的隔离级别下，其它会话的事务甚至将无法读取这些记录的数据。<br></div><div><br></div><div>经过实践，以上3个效果里的“其它会话”字眼由本人添加。如果是在同一个会话下是无法测出这些效果的。</div><div><br></div><div>上面开始事务，我都是用的start transaction命令。</div><div>mysql里有个是否自动提交的：</div><div><pre><code><span class="sql1-reservedword">show</span><span class="sql1-space"> </span><span class="sql1-reservedword">variables</span><span class="sql1-space"> </span><span class="sql1-reservedword">like</span><span class="sql1-space"> </span><span class="sql1-string">'%autocommit%'</span><span class="sql1-symbol">; </span><span class="sql1-reservedword">set</span><span class="sql1-space"> </span><span class="sql1-identifier">autocommit</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-number">0</span><span class="sql1-symbol">; </span></code></pre></div><div>事务是否自动，经过实践并没有找出什么不同，此处还有待补充。</div><div><br></div><div><br></div><div>参考链接：各种锁<a href="https://blog.csdn.net/bigtree_3721/article/details/73731377" target="_blank">https://blog.csdn.net/bigtree_3721/article/details/73731377</a><br></div><div><br></div><div><a href="denied:" denied:"https:="" blog.csdn.net="" cug_jiang126com="" article="" details="" 50544728"""="" target="" "_blank"""="" style=""><br></a></div><div><br></div><div><br></div><p><br></p>