创建索引-资源正忙的解决方案及原理
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
创建索引时失败报错
create index sa.idx_test_1_id on sa.test_1 (id);
NOWAIT :关键字表示sql语句采用非阻塞的方式,如果发现涉及到的数据被占有(被锁),则立即通知Oracle该资源被占用,返回错误信息
方法/步骤2
- 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:查看当前回话号
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
- 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会对所有分区产生段级共享锁,对象上已经存在的锁不允许比他大一级的锁产生。
- 索引创建时加上关键字 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