oracle锁机制的模拟试验

首先我们用pl sql模拟3个用户

conn system/beyondsanli
select sys_context('userenv','sessionid') from dual;

SYS_CONTEXT('USERENV','SESSIONID')

--------------------------------------------------------------------------------
1009


conn system/beyondsanli


select sys_context('userenv','sessionid') from dual;

SYS_CONTEXT('USERENV','SESSIONID')

--------------------------------------------------------------------------------
1010




select sid, serial#, audsid, paddr, username, lockwait, ROW_WAIT_OBJ#
  from v$session
where audsid in (1009, 1010);


  SID    SERIAL#     AUDSID PADDR    USERNAME                       LOCKWAIT ROW_WAIT_OBJ#

---------- ---------- ---------- -------- ------------------------------ -------- -------------
1 132 852 1010 1EE5015C SYSTEM -1
2 147 1192 1009 1EE4FB6C SYSTEM -1

(查询得知，session1的sid=1009，session2的sid=1010)


session 1中

update czk_locktest set modulename='locktest here' where id=1


select sid, serial#, audsid, paddr, username, lockwait, ROW_WAIT_OBJ#
  from v$session
where audsid in (1009, 1010);


  SID    SERIAL#     AUDSID PADDR    USERNAME                       LOCKWAIT ROW_WAIT_OBJ#

---------- ---------- ---------- -------- ------------------------------ -------- -------------
1 132 852 1010 1EE5015C SYSTEM -1
2 147 1192 1009 1EE4FB6C SYSTEM 52547


(查询得知，session1的sid=132，session2的sid=147)  这里row_wait_obj# 不是-1



  select XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,PROCESS,LOCKED_MODE from v$locked_object;

1 7 8 362 52547 147 3012:1336 3

session2
update czk_locktest set modulename='locktest here 2' where id=2



  select XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,PROCESS,LOCKED_MODE from v$locked_object;


1 10 9 357 52547 131 2268:1228 3
2 9 2 380 52547 141 3328:5164 3




select XIDUSN,XIDSLOT,XIDSQN,a.OBJECT_ID,ub.object_name,SESSION_ID,PROCESS,LOCKED_MODE from v$locked_object a left outer join user_objects ub on ub.object_id=a.OBJECT_ID;



1 7 8 362 52547 CZK_LOCKTEST 147 3012:1336 3

评论

1 楼 beyondsanli 2008-04-07  

强行关闭锁定的状态
select sid, serial#, audsid, paddr, username, lockwait, ROW_WAIT_OBJ#
from v$session
where audsid in (446, 449);

1 145 121 446 1EE4E99C SYSTEM -1
2 146 366 449 1EE5015C SYSTEM -1

select XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,PROCESS,LOCKED_MODE from v$locked_object;

alter system kill session '145,121'


附加：

select * from tablename for update no wait
select * from tablename  for update wait 10