<span class="Apple-style-span" style="border-collapse:separate;color:#000000;font-family:'Times New Roman';font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;orphans:2;text-align:auto;text-indent:0px;text-transform:none;white-space:normal;widows:2;word-spacing:0px;-webkit-border-horizontal-spacing:0px;-webkit-border-vertical-spacing:0px;-webkit-text-decorations-in-effect:none;-webkit-text-size-adjust:auto;-webkit-text-stroke-width:0px;font-size:medium;"> <div style="margin-top:0px;margin-right:auto;margin-bottom:0px;margin-left:auto;padding-top:20px;padding-right:20px;padding-bottom:20px;padding-left:20px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-family:Helvetica, arial, freesans, clean, sans-serif;font-size:14px;line-height:1.6;color:#333333;background-color:#FFFFFF;max-width:960px;"> <p style="margin-top:0px !important;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 没想到距此篇博文的上半部分发表(http://blog.itpub.net/7192724/viewspace-1614865/)已经有半年的时间，上篇博文是5月小长假的时候，在开往杭州的高铁上完成的，话说第二天就有了我的小baby:)，难道写博客还有助孕的效果？需要的朋友不妨一试，哈哈，归根结底，还是需要作为IT从业者的我们，紧张工作之余，要有放松的安排，不仅是身体上的放松，还要有精神、心灵上的放松，俗话说得好“天空飘来五个字，那都不是事，是事也就烦一会，一会就没事”。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 有点扯远了，说正事儿，作为本篇博文的内容，主要包含两个方面： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1.<strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">话题1：不同锁模式的实验 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 接着上篇博文的话题，针对几种常见的锁模式通过实验感受下之间的不同。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 2.<strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">话题2：不同方式新增字段的效率 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 最近有个系统执行新增字段的操作出现了hang，借此使用实验说明下不同新增字段的方式对效率的影响和原理。 <h2 style="margin-top:20px;margin-right:0px;margin-bottom:10px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:1px;border-left-width:0px;border-style:initial;border-color:initial;font-weight:bold;-webkit-font-smoothing:antialiased;font-size:24px;border-bottom-style:solid;border-bottom-color:#CCCCCC;color:#000000;"> 话题1：不同锁模式的实验 </h2> <p style="margin-top:10px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 上篇博文查看了执行如下新增字段的trace文件： <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">alter table t add (sex varchar2(1));</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 发现执行该语句时是以NOWAIT方式对表添加了一个ROW EXCLUSIVE模式锁：<code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 我们看下Oracle的官方解释： <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ROW SHARE <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">ROW SHARE允许并发访问被锁定的表，但是禁止用户以排他访问的方式锁定整张表。 <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ROW EXCLUSIVE <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">ROW EXCLUSIVE和ROW SHARE相同，但禁止以SHARE模式锁定。当执行update、insert或delete语句时会自动获得ROW EXCLUSIVE锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 我是初学者，反正我是没太明白两者的区别，晦涩。只有通过实验，才是最有助于理解其含义的方法。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验版本： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select * from v$version where rownum = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 2此时可以执行以下语句： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set a = 1 where id =1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1 row updated. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1和session 2都可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 1可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> update t set a = 1 where id = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1 row updated. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> update t set a = 1 where id = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但可以对其他行记录操作： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'B' where id = 2; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1 row updated. <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行以下两条语句： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但session 2执行以下语句hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从ROW SHARE的效果来看，这种行级锁，允许不同session同时持有ROW SHARE或SHARE或ROW EXCLUSIVE锁，但某一session执行DML语句后，其他session就无法针对相同的数据行做DML操作，处于hang，除非上一session的DML操作commit或rollback，但此时还是可以允许并发的只读访问。但不允许其他session获得EXCLUSIVE锁。证明了Oracle官方所说的“禁止用户以排他访问的方式锁定整张表”。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 接下来看看ROW EXCLUSIVE模式锁的实验。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 2以下语句均可执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set a = 1 where id = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1 row updated. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但此时session 1再执行同行的操作则会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set a = 1 where id = 1; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1和session 2均可执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 如果session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set a = 1 where id = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1 row updated. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2执行以下语句就会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set a = 1 where id = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1和session 2均可执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行以下两条语句： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但执行以下两条语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从ROW EXCLUSIVE的效果来看，这种行级锁，允许不同session同时持有ROW EXCLUSIVE或ROW SHARE锁，但某一session执行DML语句后，其他session就无法针对相同的数据行做DML操作，处于hang，除非上一session的DML操作commit或rollback，但此时还是可以允许并发的只读访问。即他允许多个会话拥有ROW行级EXCLUSIVE或SHARE锁，但无法同时获得EXCLUSIVE或SHARE锁，从限制上要比ROW SHARE更严格。证明了Oracle官方所说的“禁止以SHARE模式锁定”，EXCLUSIVE比SHARE更严格，自然也不能获得EXCLUSIVE锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 以上是对两种ROW行级锁的实验，结论就是ROW EXCLUSIVE和ROW SHARE均可以允许并发只读操作，从锁的强弱看，ROW EXCLUSIVE > ROW SHARE，但其实这种行级锁可能更多地还是通过DML语句自动获得，而不是用实验中的LOCK语句。以上只是为了更好地说明两者区别。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 说完了行级锁，接下来看下表级锁： <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SHARE <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SHARE permits concurrent queries but prohibits updates to the locked table. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">SHARE锁允许并发查询，但是禁止其他session对锁定的表更新。 <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> EXCLUSIVE <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> EXCLUSIVE permits queries on the locked table but prohibits any other activity on it. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;">EXCLUSIVE允许锁定表的查询操作，但禁止其他session对该表的任何操作 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 我觉得这里Oracle的介绍是有些问题的，不够严谨，至少没有说清楚到底限制有何不同，接下来，我们还是通过实验的方式进行说明。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 首先来看SHARE模式锁： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1和session 2均可执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1和session 2均可执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但此时无论是session 1还是session 2先执行以下语句都会hang(比如此处是session 1先执行)： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2执行以下语句也会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但同时session 1处于hang的语句会报错： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> update t set b = 'a' where a = 1 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> * <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ERROR at line 1: <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ORA-00060: deadlock detected while waiting for resource <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session2还处于hang的状态。 <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但以下两条语句均会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从SHARE模式锁的效果来看， <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (1) 如果某一session获得SHARE模式锁后，其他session还可以执行DML操作。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (2) 如果多个session同时获得SHARE模式锁，则这些session只能执行读操作，做DML操作会hang。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (3) 如果两个session同时对一行记录做DML操作，则第一个session会报ORA-60死锁错误，直接被Oracle检测退出，第二个session继续处于hang。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (4) 如果一个session获得了SHARE模式锁，则其他session不能再获得ROW EXCLUSIVE或EXCLUSIVE模式锁，但可以获得ROW SHARE或SHARE模式锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 因此对开始的介绍： <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SHARE锁允许并发查询，但是禁止其他session对锁定的表更新。 </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 更严谨的是说对多个获得SHARE锁的session来说，允许并发读，但禁止做DML操作，即只需看，不许改，这也是SHARE的含义。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 接下来看看EXCLUSIVE模式锁的实验。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 2执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2执行以下语句均会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` 从EXCLUSIVE模式锁的效果来看， <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (1) 如果某一session获得EXCLUSIVE模式锁，则其他session只能允许读操作，禁止DML操作。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> (2) 如果某一session获得EXCLUSIVE模式锁，则禁止其他session再获得ROW SHARE、SHARE、ROW EXCLUSIVE或EXCLUSIVE各种模式锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 因此对开始的介绍： <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> EXCLUSIVE允许锁定表的查询操作，但禁止其他session对该表的任何操作。 </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 更严谨的是说对多个蝴蝶EXCLUSIVE模式锁的session来说，除了读操作外，禁止其他任何操作。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 显然，从锁的强弱看，EXCLUSIVE>SHARE>ROW EXCLUSIVE>ROW SHARE。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 另外，还有一种锁 <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SHARE ROW EXCLUSIVE <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ** SHARE ROW EXCLUSIVE模式锁用来查看整张表，允许其他session检索表中的行，但禁止其他session以SHARE模式锁定表或者更新行。 ** <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> update t set b = 'a' where a = 1; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 2执行以下语句会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share row exclusive mode; <hr style="clear:both;margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;height:0px;overflow-x:hidden;overflow-y:hidden;border-top-style:none;border-right-style:none;border-bottom-style:solid;border-left-style:none;border-width:initial;border-color:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-color:transparent;border-bottom-width:4px;border-bottom-color:#DDDDDD;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;background-position:initial initial;background-repeat:initial initial;" /> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> session 1执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table(s) Locked. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 此时session 2可以执行： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 但session 2执行以下语句均会hang： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in share mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in row exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> lock table t in exclusive mode; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从SHARE ROW EXCLUSIVE的效果来看，相比SHARE允许其他session同时获得SHARE模式锁，其禁止其他session获得SHARE模式锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从锁的强弱看，EXCLUSIVE(exclusive，X)>SHARE ROW EXCLUSIVE(S/Row-X，SRX)>SHARE(Share，S)>ROW EXCLUSIVE(Row-X，RX)>ROW SHARE(Row-S，RS)。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 引述一篇博客的总结(http://blog.itpub.net/9252210/viewspace-626388/) <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 2级锁Row-S 行共享(RS)：共享表锁，sub share，锁有：Select for update,Lock For Update,Lock Row Share。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 3级锁Row-X 行独占(RX)：用于行的修改，sub exclusive，锁有：Insert, Update, Delete, Lock Row Exclusive。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 4级锁Share 共享锁(S)：阻止其他DML操作，share，锁有：Create Index, Lock Share，locked_mode为2，3，4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会hang。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 5级锁S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive，锁有：Lock Share Row Exclusive，具体来讲有主外键约束时update / delete ... ; 可能会产生4，5的锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 6级锁exclusive 独占(X)：独立访问使用，exclusive，锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 数字越大锁级别越高, 影响的操作越多。 <h2 style="margin-top:20px;margin-right:0px;margin-bottom:10px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:1px;border-left-width:0px;border-style:initial;border-color:initial;font-weight:bold;-webkit-font-smoothing:antialiased;font-size:24px;border-bottom-style:solid;border-bottom-color:#CCCCCC;color:#000000;"> 话题2：不同方式新增字段的效率 </h2> <p style="margin-top:10px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验1： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> set timing on <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> alter table t add add_a number; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table altered. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Elapsed: 00:00:00.29 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 新增一个允许NULL，且无默认值的字段，用时0.29秒。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 前文介绍了，获得的是一个ROW EXCLUSIVE模式锁。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验2： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> alter table t add add_b number default 0; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table altered. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Elapsed: 00:00:59.34 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 新增一个允许NULL，但有默认值的字段，用时59秒。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从10046的trace文件看，他会首先用EXCLUSIVE模式锁来锁定表。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">LOCK TABLE "T" IN EXCLUSIVE MODE NOWAIT</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 同时，在最后执行了更新字段ADD_B为默认值的操作： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">update "T" set "ADD_B"=0;</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 因此不难想为什么耗时这样久，就是还需要更新所有字段为默认值，另外，还会因为数据量的增加，可能需要更多的UNDO空间，进而可能因为一条新增字段的操作，导致整个库的UNDO表空间不够用，不仅影响对这张表的正常增删改操作(因为获取了最高级别EXCLUSIVE锁)，还有可能影响其他业务功能(因为UNDO表空间不够用)。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 实验3： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> alter table t add add_c number default 0 not null; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table altered. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Elapsed: 00:00:00.16 ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 新增一个包含NOT NULL约束，有默认值的字段，用时0.16秒。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从10046的trace文件看，会获得一个ROW EXCLUSIVE模式锁来锁定表。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT</code>` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 锁的级别比实验2要低，而且该默认值是存储于col$数据字典表中的，并不是保存在原表记录上，这点的原因可以参见David的博文(http://blog.csdn.net/tianlesoftware/article/details/7226893)。即新增一个NOT NULL和默认值的字段，以后每次需要使用该字段时，默认值都是从数据字典中查询到的，这样就减少了新增字段时的DDL语句时间，也减少了存储空间(不用每条需要使用默认值的记录都存储默认值)。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` 实验4： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> select count(*) from t; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 1000000 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> alter table t add add_h number not null; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> alter table t add add_h number not null <pre style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:6px;padding-right:10px;padding-bottom:6px;padding-left:10px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:13px;font-family:Consolas, 'Liberation Mono', Courier, monospace;background-color:#F8F8F8;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#CCCCCC;border-right-color:#CCCCCC;border-bottom-color:#CCCCCC;border-left-color:#CCCCCC;line-height:19px;overflow-x:auto;overflow-y:auto;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;"><code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:pre;border-top-style:none;border-right-style:none;border-bottom-style:none;border-left-style:none;background-color:transparent;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;border-width:initial;background-image:initial;background-attachment:initial;background-origin:initial;background-clip:initial;background-position:initial initial;background-repeat:initial initial;"> * </code></pre> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ERROR at line 1: <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ORA-01758: table must be empty to add mandatory (NOT NULL) column <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 新增一个仅有NOT NULL约束，没有默认值的字段，则需要表为空。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从11g之后，对于新增字段，Oracle进行了优化，引述官方介绍： <blockquote style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:15px;padding-bottom:0px;padding-left:15px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:4px;border-style:initial;border-color:initial;border-left-style:solid;border-left-color:#DDDDDD;color:#777777;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Adding Table Columns <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML. <p style="margin-top:15px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value. </blockquote> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 说的很明白了，如果新增一个含有默认值的字段，那么会立即更新每一行，在更新过程中，会有一个EXCLUSIVE级别的锁在该表上。如果指定NOT NULL和默认值，则会进行优化，降低阻止DML操作的时间。如果增加一个仅有NOT NULL的约束字段，那么需要表不能包含任何记录，否则就需要必须指定一个默认值，这也好理解，如果执行之前有记录，又要求NOT NULL，那么之前的记录字段默认值是什么就需要指定才行。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 顺带提一句，删除表字段的操作： <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> SQL> alter table t drop column add_b; <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Table altered. <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> Elapsed: 00:00:43.44 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> ``` <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 从10046的trace文件看，也是获得了一个EXCLUSIVE锁，进而更新的过程中是对整张表的DML操作有影响的。 <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:1px;border-right-width:1px;border-bottom-width:1px;border-left-width:1px;border-style:initial;border-color:initial;font-size:12px;font-family:Consolas, 'Liberation Mono', Courier, monospace;white-space:nowrap;border-top-style:solid;border-right-style:solid;border-bottom-style:solid;border-left-style:solid;border-top-color:#EAEAEA;border-right-color:#EAEAEA;border-bottom-color:#EAEAEA;border-left-color:#EAEAEA;background-color:#F8F8F8;border-top-left-radius:3px 3px;border-top-right-radius:3px 3px;border-bottom-right-radius:3px 3px;border-bottom-left-radius:3px 3px;">LOCK TABLE "T" IN EXCLUSIVE MODE NOWAIT</code> <p style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 总结起来： <ol style="margin-top:15px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:30px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <li style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 11g以上的版本，如果使用NOT NULL和默认值的方式新增字段，那么执行时间会大大降低。且只会有一个ROW EXCLUSIVE级别锁。 </li> <li style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 11g以上的版本，如果使用默认值，没有NOT NULL约束的方式新增字段，那么执行时间会很久，取决于表中数据量的大小，获得的是EXCLUSIVE级别锁，期间会影响所有记录的DML操作，可能会因UNDO不足对其他操作有影响。 </li> <li style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> <p style="margin-top:0px;margin-right:0px;margin-bottom:15px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 11g以上的版本，如果新增字段没有默认值，也没有NOT NULL约束，则还是会使用ROW EXCLUSIVE模式锁，但由于不需要更新字段值，执行时间也是比较短。 </li> </ol> <p style="margin-top:15px;margin-right:0px;margin-bottom:0px !important;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;"> 如何选择11g上新增字段的方式，看来是有一个比较清晰的方向了。 </div>

ITPub博客

alter table新增字段操作究竟有何影响？(下篇)

最新文章