oracle锁机制的模拟试验

ispgjabzfc

首先我们用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#

---------- ---------- ---------- -------- ------------------------------ -------- -------------
113285210101EE5015CSYSTEM-1
2147119210091EE4FB6CSYSTEM-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#

---------- ---------- ---------- -------- ------------------------------ -------- -------------
113285210101EE5015CSYSTEM-1
2147119210091EE4FB6CSYSTEM52547


(查询得知，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;

178362525471473012:13363

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;


1109357525471312268:12283
292380525471413328:51643




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;



17836252547CZK_LOCKTEST1473012:13363