oracle11gddl锁测试

session1 ：alter table baixyu2 add(id number default 0);

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               200     327705   3094          6      0      0 TXSCOTT               200      89562      0          6      0      0 TM

查看锁的信息，上面的alter语句添加了一个tx锁，和一个tm锁，scott用户在没有执行任何命令的情况下就有AE，TO类型的锁

SQL> select * from v$lock_type where type in('TX','TM','AE','TO');TYPE       NAME              ID1_TAG    ID2_TAG    IS_USE DESCRIPTION---------- ------------------------- ---------- ---------- ------ ------------------------------TM     DML               object #   table/part YES    Synchronizes accesses to an ob                        ition         jectTX     Transaction           usn<<16 |  sequence   YES    Lock held by a transaction to                     slot             allow other transactions to wa                                  it for itAE     Edition Lock          edition ob 0      NO     Prevent Dropping an edition in                     j#                useTO     Temp Object           object #   1      NO     Synchronizes DDL and DML operaTYPE       NAME              ID1_TAG    ID2_TAG    IS_USE DESCRIPTION---------- ------------------------- ---------- ---------- ------ ------------------------------                                  tions on a temp object

在上面的命令运行的时候，在另一个会话中执行update语句，

update baixyu2 set object_name='aa' where owner='SCOTT';

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               197 -435190464      0          6      0      0 CUSCOTT               200     327705   3094          6      0      0 TXSCOTT               200      89562      0          6      0      0 TM

看到新的会话197额锁类型是CU，而且没有处于等待状态，在看下锁的解释,在编译时恢复游标，以防死亡

SQL> select * from v$lock_type where type in('CU');TYPE       NAME              ID1_TAG    ID2_TAG    IS_USE DESCRIPTION---------- ------------------------- ---------- ---------- ------ ------------------------------CU     Cursor            handle handle     NO     Recovers cursors in case of de                                  ath while compiling

添加字段的语句确实会使dml语句无法执行，但是不是让dml语句处于等待的状态
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.

删除字段的语句，是会阻塞dml语句

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type,v$lock.block from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE        BLOCK-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               197      89562      0          0      3      0 TM          0SCOTT               200     393249   3267          6      0      0 TX          0SCOTT               200        499      0          3      0      0 TM          0SCOTT               200        494      0          3      0      0 TM          0SCOTT               200      89562      0          6      0      1 TM          1

SQL> alter table baixyu2 modify(object_name varchar2(200));

Table altered.

SQL> alter table baixyu2 modify(id2 default 2);

Table altered.

上面2个语句执行的都很快

alter table baixyu2 move;这个语句的效果跟drop字段的效果是一样的，别的会话不能ddl，不能dml多了一个锁类型

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type,v$lock.block from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE        BLOCK-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               197      89562      0          0      3      0 TM          0SCOTT               200     327703   3095          6      0      0 TX          0SCOTT               200      4   16777386          6      0      0 TS          0SCOTT               200      89562      0          6      0      1 TM          1

create index idx_sub_name on baixyu2(SUBOBJECT_NAME);添加索引的语句也会阻塞dml操作

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type,v$lock.block from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE        BLOCK-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               197      89562      0          0      3      0 TM          0SCOTT               200     524288   3135          6      0      0 TX          0SCOTT               200     18      0          3      0      0 TM          0SCOTT               200      89562      0          4      0      1 TM          1SCOTT               200      89562      0          3      0      0 DL          0SCOTT               200      89562      0          3      0      0 DL          0

在11g中可以online创建索引，不会创建添加排他ddl锁。

create index idx_sub_name on baixyu2(SUBOBJECT_NAME) online;

SQL> select  username,v$lock.sid,id1,id2,lmode,request,block,v$lock.type,v$lock.block from v$lock,v$session where v$lock.sid=v$session.sid and  username='SCOTT' and v$lock.type not in('AE','TO');USERNAME            SID        ID1    ID2      LMODE    REQUEST  BLOCK TYPE        BLOCK-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------SCOTT               197      89562      0          3      0      0 TM          0SCOTT               200     327708   3105          6      0      0 TX          0SCOTT               200      89715      0          4      0      0 TM          0SCOTT               200      89562      0          2      0      0 TM          0SCOTT               200      89562      0          3      0      0 DL          0SCOTT               200      89562      0          4      0      0 OD          0SCOTT               200      89562      0          3      0      0 DL          0

看到此时是没有阻塞的,在上面的测试中可以看到排他ddl是会导致数据表结构无法修改，数据无法被更新，在创建视图或是存储过程的过程中，会对被依赖的表添加共享ddl，这个时候能对表的数据进行修改但是不能对表的结构进行修改。
ddl的最后一种类型是可中断解析锁，会话在解析语句的时候，会对语句中引用的每一个对象设置一个解析锁，当修改或删除一个被引用的对象的时候，可以将共享池中已解析缓存的语句设置为无效，重新解析。视图dba_ddl_locks可以显示所有的ddl锁。

SQL> create or replace procedure p   2  as  3  begin  4  null;  5  end;  6  /Procedure created.SQL> exec pPL/SQL procedure successfully completed.SQL> select session_id sid,owner,name,type, mode_held held,mode_requested request from dba_ddl_locks where session_id=(select sid from v$mystat where rownum=1);       SID OWNER        NAME                   TYPE         HELD           REQUEST---------- -------------------- ------------------------------ -------------------- ------------------ ------------------       201 SCOTT        P                  Table/Procedure/Type Null           None       201 SYS          DBMS_APPLICATION_INFO          Body         Null           None       201 SYS          DBMS_STANDARD              Table/Procedure/Type Null           None       201 SYS          DBMS_APPLICATION_INFO          Table/Procedure/Type Null           None       201 SCOTT        SCOTT                  18           Null           None       201          SCOTT                  73           Share          None       201 SYS          DATABASE               18           Null           None7 rows selected.

看到这个视图中多了一个p的锁记录，持有者是scott，在解析了这个存储过程后，设置了一个解析锁，重新编译这个存储过程

SQL> alter procedure p compile;Procedure altered.SQL> select session_id sid,owner,name,type, mode_held held,mode_requested request from dba_ddl_locks where session_id=(select sid from v$mystat where rownum=1);       SID OWNER        NAME                   TYPE         HELD           REQUEST---------- -------------------- ------------------------------ -------------------- ------------------ ------------------       201 SYS          DBMS_APPLICATION_INFO          Body         Null           None       201 SYS          DBMS_STANDARD              Table/Procedure/Type Null           None       201 SYS          DBMS_APPLICATION_INFO          Table/Procedure/Type Null           None       201 SCOTT        SCOTT                  18           Null           None       201          SCOTT                  73           Share          None       201 SYS          DATABASE               18           Null           None

可以看到在重新编译后，这个锁记录消失了，可以使用这个会话查看存储过程被阻塞的原因。