lock 1

2008-04-01

step 1: create table
create table czk_locktest
(
id int not null primary key,
modulename varchar(50),
moduleDescript varchar(200),
moduleid varchar(50),
createtime date
)
select * from czk_locktest;

insert into czk_locktest values(1,'lockname1','lockdes2','moduleid1',sysdate)
step2 :现在我们来实践一个在更新的时候，锁表的情况

打开一个SQL windows :
update czk_locktest set modulename='sanli'

另外打开一个sql windows
select * from czk_locktest for update //死锁等待
select * from czk_locktest

你在事务中修改的每一行或SELECT FOR UPDATE都将“指向”该事务相关的一个TX锁。要理解为什么

select * from v$lock;

13 1D9C4074 1D9C408C 147 TM 52547 0 3 0 3 0
14 1DA008C4 1DA009E0 147 TX 196636 303 6 0 3 0
其实 select * from v$lock where block=1
说明那个操作会话被阻止

step 3:如何知道整个数据库中哪些表被锁定了？

Select gob.*,gp.spid From gv$locked_object gob ,gv$session gs,gv$process gp
Where gob.session_id=gs.sid And gs.paddr=gp.addr And gob.inst_id=gs.inst_id And gob.inst_id=gp.inst_id;

step 4:哪个用户在锁表
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where a.lockwait = b.kaddr

事务锁，但没有造成其它会话的阻塞

Select Null "2.被锁对象：" From dual;
Select gob.*,gp.spid From gv$locked_object gob ,gv$session gs,gv$process gp
Where gob.session_id=gs.sid And gs.paddr=gp.addr And gob.inst_id=gs.inst_id And gob.inst_id=gp.inst_id;

Select Null "3.锁等待关系：" From dual;
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from gv$lock a,gv$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.Block In (1,2) and b.block=0;