oracle鎖級別相關測試

收到報警 資料庫出現6級別的鎖，於是研究下6級別的鎖是什麼！監控的sql如下：
select count(*) from  v$locked_object   where  locked_mode=6;
做實驗模擬出出現6級別鎖的場景！
1、ddl_lock_timeout引數
DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned
2、檢視自己的session id
select userenv('sid') from dual;
3、日常操作加鎖級別 ，因為drop和truncate的過程非常快，所以我寫了一個無限迴圈的指令碼，一直在執行探測鎖資訊，經過證實，如下所列的操作對應的鎖的資訊如下：兩個方法來檢視具體的操作的鎖級別！
方法1：可以透過select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b  where b.object_id=l.object_id;這個查詢來檢視加了什麼級別的鎖！
方法2“其實還有一個方法來探測某個操作加上鎖，就是製造阻塞的情況，然後再透過select sid,id1,id2,type,lmode,request from v$lock where sid in (1328,194) order by sid; 查詢被阻塞的session再等待（request）什麼級別的鎖，
update table  ----3級別鎖
LOCK TABLE htsl.T_PPF_INTERFACELOG  IN Exclusive MODE---6級別鎖，
alter 新增列------4級別鎖，
create index 新增索引----4級別鎖，
drop 表-----6級別鎖    
truncate table ----6級別鎖
truncate  分割槽---6級別鎖
alter drop 分割槽----6級別鎖
alter新增分割槽 ---- 這個場景沒測出來，猜測對整個表影響不大，select  * from  p_range_test for  update; 也不阻塞新增分割槽的操作，分割槽表的後設資料鎖是一個分割槽一個（不同於MySQL），就是某個分割槽的TM鎖，不阻塞其他分割槽的操作！所以猜測對整個表影響不大！如下測試結果：
建立一個range 分割槽：
create table p_range_test
(id number,name varchar2(100))
partition by range(id)(
partition t_p1 values less than (10),
partition t_p2 values less than (20),
partition t_p3 values less than (30)
);
insert into  p_range_test  values(1,'liuwenhe');
insert into  p_range_test  values(15,'liuwenhe15');
insert into  p_range_test  values(25,'liuwenhe25');
commit;
session 1:執行update某個分割槽的一條資料,不提交，這裡我選擇了更新t_p2分割槽的一個條資料
update  p_range_test  set   name='liu'  where  id=15
session 2：執行drop分割槽t_p2，發現阻塞，可以透過設定ddl_lock_timeout>0來更直觀的看到處於鎖等待，
SQL>  alter  table  p_range_test  drop  partition  t_p2;
alter  table  p_range_test  drop  partition  t_p2
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
session3：執行drop其他分割槽，發現是可以的
SQL>  alter  table  p_range_test  drop  partition  t_p1;
Table altered.
所以：oracle 分割槽表和MySQL分割槽表在表後設資料鎖上的區別：
MySQL：在 server 層，認為這是同一張表，因此所有分割槽共用同一個 MDL 鎖；
在引擎層，認為這是不同的表，因此 MDL 鎖之後的dml執行過程，會根據分割槽表規則，只訪問必要的分割槽
Oracle：類似於MySQL的mdl鎖是 TM鎖，他是每個分割槽一個的，而不是共用同一個！
4、監控資料庫中出現6級別鎖的時候，查出所有鎖住的物件資訊，一行資料顯示同時鎖住多個物件資訊！
select LISTAGG(d.lock_object_names,',') WITHIN GROUP (ORDER BY d.lock_object_names) lock_objects from (select c.locked_mode, LISTAGG(c.lock_object_name,',') WITHIN GROUP (ORDER BY c.lock_object_name) lock_object_names FROM (select l.locked_mode,b.owner||'.'||b.object_name  lock_object_name from v$locked_object l,dba_objects b where  b.object_id=l.object_id and l.locked_mode=6  group by l.locked_mode, b.owner, b.object_name) c  GROUP BY c.locked_mode union all select 6,'0' from dual)d GROUP BY d.locked_mode;
5、檢視某個操作持有什麼級別的鎖
select b.owner,b.object_name,l.session_id,l.locked_mode
 from v$locked_object l, dba_objects b
 where b.object_id=l.object_id
6、注意某個操作處於等待狀態的時候，不加鎖，lock_mode=0, 區別於MySQL，這種狀態下，他不會阻塞後續的dml操作，如下實驗
1）session 1，執行update,不提交
SQL> update  htsl.T_PPF_INTERFACELOG  set  NAME1=10 where  ID=188873;
2）session 2，執行新增列的操作，發現處於等待狀態
SQL> alter table  htsl.T_PPF_INTERFACELOG  add  name11  int;
3）session 3再次執行update這個表的另一行資料，發現依舊可以執行成功
SQL> update  htsl.T_PPF_INTERFACELOG  set  NAME1=10 where  ID=190500;
1 row updated.
SQL> commit;
Commit complete.
所以說不像MySQL（rr和rc隔離級別） ，一旦某個alter操作處於等待狀態，那麼後面的所有操作都將處於等待狀態；

一、首先session1模擬dml操作， 不提交
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
          2649
SQL> insert  into  htsl.liuwenhe  values(1);
1 row created.
二、session2分別執行如下：
SQL> show  parameter ddl_lock_timeout
NAME                                 TYPE        VALUE
--------------------------------------------------------------------------------
ddl_lock_timeout                     integer     0
1）加索引，發現直接報錯
SQL> create index htsl.idx_name2 on htsl.liuwenhe  (name2) ;
create index htsl.idx_name2 on htsl.liuwenhe  (name2)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2）drop table,直接返回錯誤，
SQL> drop table  htsl.liuwenhe;
drop table  htsl.liuwenhe
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
3）新增欄位，發現處於等待狀態，
SQL> alter table  htsl.liuwenhe  add  name4 varchar(10);

實驗2
session 194執行，不提交
SQL> update   htsl.T_PPF_INTERFACELOG_bak  set  FCODE='1122'  where id=188875;
1 row updated.
session 1328執行 drop表，處於阻塞，阻塞60秒
SQL> alter  session  set ddl_lock_timeout=60;
Session altered.
SQL>
SQL> drop table  htsl.T_PPF_INTERFACELOG_bak;
阻塞中。。。。
session 3檢視相關鎖，發現session 1328當前的鎖模式為0，表示什麼鎖也沒加上，
SQL> select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b  where b.object_id=l.object_id;
OWNER        OBJECT_NAME                              SESSION_ID LOCKED_MODE
--------------------------------------------------------------------------------
HTSL         T_PPF_INTERFACELOG_BAK                         1328           0
HTSL         T_PPF_INTERFACELOG_BAK                          194           3
再檢視他在請求什麼鎖：發現1328會話再請求6級別鎖！
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (1328,194) order by sid;

       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       194     140165          0 TM          3          0
       194        100          0 AE          4          0
       194     655380    3125984 TX          6          0
      1328     140165          0 TM          0          6
      1328        100          0 AE          4          0
      1328          0          1 AE          4          0
      1328      79851          1 TO          3          0

7 rows selected.
針對truncate 操作現象同上，也在請求6級別鎖
針對新增列或者新增索引，如下所示，在請求4級別鎖
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (758,194) order by sid;

       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       194     655380    3125984 TX          6          0
       194     140165          0 TM          3          0
       194        100          0 AE          4          0
       758     140165          0 TM          3          0
       758     589825    1185636 TX          6          0
       758        100          0 AE          4          0
       758     140165          0 OD          6          0
       758     655380    3125984 TX          0          4
       758      79851          1 TO          3          0

9 rows selected.

針對drop 、truncate 、add分割槽的現象，
1）drop和truncate分割槽，也是請求6級別鎖：
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (758,949) order by sid;

       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       758        100          0 AE          4          0
       758     140175          0 TM          3          0
       758     140177          0 TM          0          6
       758      79851          1 TO          3          0
       949     140177          0 TM          3          0
       949     655380    3126024 TX          6          0
       949     140175          0 TM          3          0
       949        100          0 AE          4          0

8 rows selected.