创建索引资源正忙的解决方案及原理

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2128

问题回顾：

创建索引的时候报错ORA-00054: resource busy and acquire with NOWAIT specified

解决步骤：

1：等待其他会话释放资源

2：找出占用资源的会话，并删除 

3：重启数据库

原理分析：    

1：创建索引时会产生的锁

2：dml 语句会产生的锁

3：索引创建时加上关键字 online时产生的锁

问题回顾

1. 1

创建索引时失败报错

create index sa.idx_test_1_id on sa.test_1 (id);

NOWAIT ：关键字表示sql语句采用非阻塞的方式,如果发现涉及到的数据被占有(被锁),则立即通知Oracle该资源被占用,返回错误信息

 

方法/步骤2

1. 1

等待其他会话释放资源

在创建语句中添加online，会话释放资源之后，该语句会自动执行。

create index sa.idx_test_1_id on sa.test_1 (id) online;

这种方式是采用阻塞方式，不报错

 

找出占用资源的会话，并删除 

1：找出所有被锁的对象，定位出哪个回话占用

select l.session_id,o.owner,o.object_name

from v$locked_object l,dba_objects o

where l.object_id=o.object_id

结果：

session_id owner object_name

158 SA TEST_1

146 SA TEST_1

131 SA TEST_3

136 SA TEST_2

对比想要创建的索引，定位哪些会话需要被删除

2：找出所有照成锁的会话

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

结果：

username  sid  serial# logon_time

SA 158 15184 2014/12/4 14:55:59

SA 146 8229 2014/12/4 15:23:22

SA 136 14314 2014/12/4 16:09:59

SA 131 54 2014/12/4 16:10:06

3：kill 所有占用资源的会话

命令形式：alter system kill session 'sid,serial#';

占用test_1的资源的会话：

alter system kill session '158,15184';

alter system kill session '146 ,8229';

 

重启数据库

如果数据不重要的话，可以重启数据库回滚所有未提交事务，将资源释放出来

END

原理分析

1. 创建索引时会产生的锁

1：查看当前回话号

SQL> select sid from v$mystat where rownum<2;

2：创建索引

SQL> create index sa.idx_clxsgj_HPHM_jgsj on sa.clxsgj(HPHM,jgsj);

3：查询当前会话号产生的锁

select rpad(oracle_username, 10) o_name,

session_id sid,

decode(locked_mode,

0,

'None',

1,

'Null',

2,

'Row share',

3,

'Row Exclusive',

4,

'Share',

5,

'Share Row Exclusive',

6,

'Exclusive') lock_type,

object_name,

xidusn,

xidslot,

xidsqn

from v$locked_object, all_objects

where v$locked_object.object_id = all_objects.object_id 

and session_id=140

从图片中可以看出

在创建索引的时候，会在每个分区产生共享锁（share）,并在OBJ$表上产生

行级排他锁（Row Exclusive）

注释：

排他锁（row exclusive）：行级别，释放前，其他事物不能修改被锁的资源

共享锁（share）：段级别，释放之前，对象上可以继续加其他类型的锁

共享锁（row share）：行级别

同一个段级不能同时存在share和row exclusive，

同一个段级row share 和row exclusive 可以同时存在

段级：一个普通表、分区表的每个分区、普通索引、索引的每个分区

行级：一行数据

锁范围大小：

EXCLUSIVE > ROW SHARE EXCLUSIVE > SHARE > ROW EXCLUSIVE > ROW SHARE 

1. dml 语句会产生的锁

1：连接用

SQL> conn sa/ednns

2：执行DML语句，但不提交

SQL> update CLXSGJ set cdfx=2 where clgjid=300000040044785;

3：户查看当前回话号

SQL> select sid from v$mystat where rownum<2;

       SID

----------

       140

4：查看DML语句产生的锁

select rpad(oracle_username, 10) o_name,

session_id sid,

decode(locked_mode,

0,

'None',

1,

'Null',

2,

'Row share',

3,

'Row Exclusive',

4,

'Share',

5,

'Share Row Exclusive',

6,

'Exclusive') lock_type,

object_name,

xidusn,

xidslot,

xidsqn

from v$locked_object, all_objects

where v$locked_object.object_id = all_objects.object_id 

and session_id=140

从中可以看出

DML语句会在更新数据所在的分区上产生行级排他锁。

之前已经

由于DML语句在CLXSGJ某些分区已经行级排他锁，而create index会对所有分区产生段级共享锁，对象上已经存在的锁不允许比他大一级的锁产生。

1. 索引创建时加上关键字 online时产生的锁

加上online 对每个分区仅仅产生ROW SHARE锁，且不会对OBJ$表产生

ROW EXCLUSIVE，而是产生一个临时表，并在临时表中产生SHARE 锁

注释：

ROW EXCLUSIVE 可以允许比他小一级的ROW SHARE 锁产生，所以不会报错

 

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2128