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;
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;