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.