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 TX
SCOTT 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 ject
TX Transaction usn<<16 | sequence YES Lock held by a transaction to
slot allow other transactions to wa
it for it
AE Edition Lock edition ob 0 NO Prevent Dropping an edition in
j# use
TO Temp Object object # 1 NO Synchronizes DDL and DML opera
TYPE 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 CU
SCOTT 200 327705 3094 6 0 0 TX
SCOTT 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 0
SCOTT 200 393249 3267 6 0 0 TX 0
SCOTT 200 499 0 3 0 0 TM 0
SCOTT 200 494 0 3 0 0 TM 0
SCOTT 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 0
SCOTT 200 327703 3095 6 0 0 TX 0
SCOTT 200 4 16777386 6 0 0 TS 0
SCOTT 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 0
SCOTT 200 524288 3135 6 0 0 TX 0
SCOTT 200 18 0 3 0 0 TM 0
SCOTT 200 89562 0 4 0 1 TM 1
SCOTT 200 89562 0 3 0 0 DL 0
SCOTT 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 0
SCOTT 200 327708 3105 6 0 0 TX 0
SCOTT 200 89715 0 4 0 0 TM 0
SCOTT 200 89562 0 2 0 0 TM 0
SCOTT 200 89562 0 3 0 0 DL 0
SCOTT 200 89562 0 4 0 0 OD 0
SCOTT 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 p
PL/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 None
7 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
可以看到在重新编译后,这个锁记录消失了,可以使用这个会话查看存储过程被阻塞的原因。