oracle鎖級別相關測試

賀子_DBA時代發表於2022-05-21

收到報警 資料庫出現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.


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-2895994/,如需轉載,請註明出處,否則將追究法律責任。

相關文章