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);
从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锁。