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