Oracle引起阻塞的情况

Oracle引起阻塞的情况

一、唯一性约束引起的阻塞：
在session1执行下面的语句中：
--创建一个表
create table t(x int primary key);
--在表中插入一条数据，并提交。
insert into t values(1);
commit;
--修改这条数据，但不提交。
update t set x = 2 where x= 1;

在Sessions2中，执行以下操作：
update t set x = 20 where x= 1;
那么此时会出现这样的情况（我是在PL/SQL Developer中执行的）：
状态栏一直显示“正在执行”，这是因为Session1在修改这条数据，没有提交，数据库在等待你做出决定（提交还是回滚），
那么此时另一个用户修改这条数据时，数据库为了保证数据一致性，就只能让这个用户等待，直到你作出决定。
Session2去修改这条数据时就会被Session1阻塞，直到Session1释放锁。

Session1的SID：
select sid from v$mystat where rownum = 1;
       SID
----------
        88
       
Session2的SID：
select sid from v$mystat where rownum = 1;
       SID
----------
       246

可以从视图中看到这些信息：      
select sid,type,id1,id2,lmode,request,block from v$lock where sid in (88,246) order by sid;        
    SID TYPE ID1  ID2  LMODE REQUEST BLOCK
 1 88 TM 157267 0    3   0    0
 2 88 TX 589844 197707 6   0    1
 3 246 TX 589844 197707 0   6    0
 4 246 TM 157267 0    3   0    0
 Block=1表示正在阻塞其他的会话
 TYPE=TM:表级共享锁
 TYPE=TX:行级排他锁，如果一个表有多个段，每个段上都会加TM。
某个SID的REQUEST不等于0，那么说明它正在等待一个锁；如果BLOCK为1，则说明此SID持有了一个锁，并阻塞别人获得这个锁。

那么在实际情况中，我们可以通过查询v$lock查看是否有阻塞——获取SID。
然后根据SID查询用户使用的机器名称：
select machine from v$session where sid in (88,246);

 

--对表加分区试验：
--创建2个分区，x值小于20的在分区p1，大于等于20的在P2分区。
create table t(x int primary key) partition by range(x) (partition p1 values less than(20),partition p2 values less than(maxvalue));
--向T中插入1条数据，X=1，此时X<20因此在分区p1。此时数据并未提交。
insert into t values(1);

--查询v$lock视图，可以看到在表和p1分区上加了TM锁。
select sid,type,id1,id2,lmode,request,block from v$lock where sid in (88,246) order by sid;    
    SID TYPE ID1  ID2  LMODE REQUEST BLOCK
1  246 TM 157269 0    3   0   0
2  246 TM 157270 0    3   0   0
3  246 TX 196616 188629 6   0   0

select object_id,object_name,subobject_name from dba_objects where object_id in (157269,157270);
    OBJECT_ID OBJECT_NAME SUBOBJECT_NAME
1  157269  T 
2  157270  T      P1

可以看到，Object_id=157270的就是分区P1。

那么，此时删除T：
drop table t;
得到的结果是：ORA-00054: resource busy and acquire with NOWAIT specified

那么，删除分区p1呢？
alter table t drop partition p1
得到的结果是：ORA-00054: resource busy and acquire with NOWAIT specified

那么，删除分区p2呢？
alter table t drop partition p2;
结果：Table altered

为什么会这样呢？
因为p2上没有TM锁，因此可以执行DDL，因为事物并未影响到p2，因此在p2分区上做DDL是可以的。
在v$lock视图看到在p1分区insert1条数据，并未提交，此时持有一个TM锁。
Oracle允许同级别（或更低级别）的锁同时设置，但拒绝更高级别的锁。DDL操作是更高级别的锁。
因此是不被允许的。

刚刚将p2分区删除了，现在重新加上：
alter table t add partition p2 values less than (maxvalue);

insert1条数据(x>20因此落在p2分区):
insert into t values(30);、
--在分区p1查询
select * from t partition (p1);

从v$lock视图查询信息（向分区p1插入的数据已commit）：
select sid,type,id1,id2,lmode,request,block from v$lock where sid in (88,246) order by sid;    
    SID TYPE ID1   ID2   LMODE REQUEST BLOCK
1  88 TM  157269 0    3   0    0
2  88 TM  157273 0    3   0    0
3  88 TX  655399 281590 6   0    0

可以看到，此时在p2上加了TM锁，因为数据未提交，还有一个TX锁。

 

二、select for update
 人事部的员工A修改某雇员的工资：
 1.页面查询此雇员的工资：
 select sal from emp where ename = 'scott';
 假定sal = 3000;
 
 2.修改工资为原来的1.1倍，但是有事离开，并未点击提交按钮。
 
 此时人事部B也来修改此雇员的工资。
 1.查询此雇员的工资：
 select sal from emp where ename = 'scott';
 假定sal = 3000;
 
 2.修改工资为原来的1.1倍，并提交，那么提交的SQL为：
 update emp set sal = 1.1*sal where ename = 'scott';
 commit;
 那么此时此雇员的工资就是3000*1.1=3300;
 
 A回来了，单击提交按钮，SQL：
 update emp set sal = 1.1*sal where ename = 'scott';
 commit;
 那么此时此雇员的工资就是：3300*1.1=3630
 
 那么此时A就会觉得奇怪，最开始的工资是3000，乘以1.1怎么就变成3630了呢？！
 而且，如果A没发现的话，那此雇员的工资就变成了3630，这样就不对了。
 由此看来，这是程序的一个BUG，在修改时并未保证不能被其他人修改。
 一个简单的办法就是使用select...for update的方式，以排他的方式获取数据，在保证修改完成之前，其他用户无法修改。
 
 但是这样存在另外一个问题：
 用户A要修改时执行了select...for update操作，此时这条数据就被锁住了，如果A不提交，
 那么用户B要修改时同样执行了select...for update操作，就会一直等待，对用户B来说，可能他都不知道怎么回事。
 
 那么更好的方式就是加上nowait:
 select * from emp where ename = 'scott' for update nowait;
 这样就可以得到一个错误：
 ORA-00054: resource busy and acquire with NOWAIT specified
 那么程序就可以捕获这个异常，提示用户有其他用户正在修改。
 
 
 
 三、外键和索引
 如果有主外键关系，且满足下面3个条件的一个，就应该考虑给外键字段创建索引：
 1.主表有频繁修改操作。
 2.主表有频繁删除操作。
 3.主表和从表经常做关联查询。
 
 第1，2这2个条件，主表会在从表创建锁定，以保证主表修改的数据不会导致从表的数据引用上出现问题。
 如果主表经常做修改和删除操作，或每次操作的记录很多，那么从表就要被锁定很长时间，影响其他用户的正常操作。
 主表修改100行，从表就会扫描100次，以保证主表的没条数据的修改都不会对从表的引用产生影响。
 
 --创建主表T
 create table t (x int primary key);
 --创建从表T1
 create table t1 (x references t(x));
 --向主表插入一些数据
 insert into t select rownum from user_objects;
 commit;
 --向从表插入一些数据
 insert into t1 select rownum from user_objects where rownum <= 100;
 commit;
 
 alter session set sql_trace = true;
 ORA-01031: insufficient privileges
 郁闷，公司的电脑，没权限。
 
 从主表删除1条记录：
 delete t where x = 1;
 结果：ORA-02292: integrity constraint (IMSDATA.SYS_C0098495) violated - child record found
 
 主表删除1条数据，会对从表执行一次全表扫描。
 主表删除1000条数据，会对从表执行1000次全表扫描。
 
 如果在外键字段创建了索引呢？
 create index t1_idx on t1(x);
 
 收集统计信息：
 exec dbms_stats.gather_table_stats(user,'t',cascade => true);
 exec dbms_stats.gather_table_stats(user,'t1',cascade => true);
 
 set autotrace trace stat;
 --让t1执行全表扫描
 select /*+ full(t1) */ t1.x from t,t1 where t1.x = t.x and t1.x <= 100;
 查看执行计划，T1执行了全表扫描。
 
 --让Oracle自己选择执行计划
 select t1.x from t,t1 where t1.x = t.x and t1.x <= 100;
 此时，Oracle选择了使用索引关联查询。
 分析2次执行计划，走索引的代价更小。
 而且一般从表数据量很大，如果这种情况下没有索引，那查询就很慢了。