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;
SIDTYPEID1ID2LMODEREQUESTBLOCK
188TM1572670300
288TX589844197707601
3246TX589844197707060
4246TM1572670300
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;
SIDTYPEID1ID2LMODEREQUESTBLOCK
1246TM1572690300
2246TM1572700300
3246TX196616188629600

select object_id,object_name,subobject_name from dba_objects where object_id in (157269,157270);
OBJECT_IDOBJECT_NAMESUBOBJECT_NAME
1157269T
2157270TP1

可以看到，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;
SIDTYPEID1ID2LMODEREQUESTBLOCK
188TM1572690300
288TM1572730300
388TX655399281590600

可以看到，此时在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次执行计划，走索引的代价更小。
而且一般从表数据量很大，如果这种情况下没有索引，那查询就很慢了。