锁.docx
《锁.docx》由会员分享,可在线阅读,更多相关《锁.docx(21页珍藏版)》请在冰豆网上搜索。
锁
1:
检查系统中锁的简单脚本:
Selects.username,s.sid,l.type,l.id1,l.id2,l.lmode,l.request,p.spidPID
Fromv$lockl,v$sessions,v$processp
Wheres.sid=l.sidAndp.addr=s.paddrAnds.usernameisnotnull
OrderByid1,s.sid,request;
获取用户SID,PID,锁的种类,锁的类型等信息
2:
获取数据库锁的信息(用户ID,OBJECT,SQL)
REM*****************************************************************
REMTITLE:
GenericScriptwhichdisplaysSQLText,REMSID
andObjectnameofthelockscurrentlyREMbeingheldinthedatabase.
REMMODULE:
lock_held.sql
Setpagesize60
Setlinesize132
selects.usernameusername,a.sidsid,a.owner||'.'||a.objectobject,s.lockwait,t.sql_textSQL
fromv$sqltextt,v$sessions,v$accessa
wheret.address=s.sql_addressandt.hash_value=s.sql_hash_value
ands.sid=a.sidanda.owner!
='SYS'
andupper(substr(a.object,1,2))!
='V$';
REMREMEndof"LockMonitoringScript"REM
3:
产生等待锁的用户报告
SELECTsn.username,m.sid,m.type,DECODE(m.lmode,0,'None',1,'Null',2,'RowShare',
3,'RowExcl.',4,'Share',5,'S/RowExcl.',6,'Exclusive',lmode,
ltrim(to_char(lmode,'990')))lmode,
DECODE(m.request,0,'None',1,'Null',2,'RowShare',3,'RowExcl.',4,'Share',
5,'S/RowExcl.',6,'Exclusive',request,ltrim(to_char(m.request,'990')))request,
m.id1,m.id2
FROMv$sessionsn,v$lockm|
WHERE(sn.sid=m.sidANDm.request!
=0)
OR(sn.sid=m.sidANDm.request=0ANDlmode!
=4AND(id1,id2)IN(SELECTs.id1,s.id2
FROMv$locks
WHERErequest!
=0ANDs.id1=m.id1ANDs.id2=m.id2))
ORDERBYid1,id2,m.request;
4:
显示持有锁的信息:
selectnvl(S.USERNAME,'Internal')username,nvl(S.TERMINAL,'None')terminal,L.SID||','||S.SERIAL#Kill,U1.NAME||'.'||substr(T1.NAME,1,20)tab,
decode(L.LMODE,1,'NoLock',2,'RowShare',3,'RowExclusive',4,'Share',
5,'ShareRowExclusive',6,'Exclusive',null)lmode,
decode(L.REQUEST,1,'NoLock',2,'RowShare',3,'RowExclusive',4,'Share',
5,'ShareRowExclusive',6,'Exclusive',null)request
fromV$LOCKL,V$SESSIONS,SYS.USER$U1,SYS.OBJ$T1
whereL.SID=S.SIDandT1.OBJ#=decode(L.ID2,0,L.ID1,L.ID2)andU1.USER#=T1.OWNER#andS.TYPE!
='BACKGROUND'
orderby1,2,5
SQL脚本
1.诊断系统中的锁
为了找出系统中那些用户锁住资源以及那些用户在等待相应的资源,可使用以下语句(其中的/*+NO_MERGE(..)*/千万不可省略,否则会很慢):
--looklock.sql
--usetheNO_MERGEhintscanspeedupthequery
select/*+NO_MERGE(a)NO_MERGE(b)NO_MERGE(c)*/'Wait'"Status",a.username,a.machine,a.sid,a.serial#,a.last_call_et"Seconds",b.id1,c.sql_text"SQL"
fromv$sessiona,v$lockb,v$sqltextc
wherea.usernameisnotnull
anda.lockwait=b.kaddr
andc.hash_value=a.sql_hash_value
union
select/*+NO_MERGE(a)NO_MERGE(b)NO_MERGE(c)*/'Lock'"Status",a.username,a.machine,a.sid,a.serial#,a.last_call_et"Seconds",b.id1,c.sql_text"SQL"
fromv$sessiona,v$lockb,v$sqltextc
whereb.id1in
(select/*+NO_MERGE(d)NO_MERGE(e)*/distincte.id1
fromv$sessiond,v$locke
whered.lockwait=e.kaddr)
anda.usernameisnotnull
anda.sid=b.sid
andb.request=0
andc.hash_value=a.sql_hash_value;
执行后的结果如下所示:
StatUSERNAMEMACHINESIDSERIAL#SecondsID1
--------------------------------------------------------------------------------------
SQL
----------------------------------------------------------------
LockCIQUSRCIQDULMACER12966245131089
select*fromc_trade_modeforupdate
WaitCIQUSRCIQDULMACER10735111131089
updatec_trade_modesetx_name='zzz'wherex_code='5'
WaitCIQUSRCIQDULMACER151061094131089
select*fromc_trade_modeforupdate
其中:
Status有两种状态,LOCK表明该进程锁住了某个资源,WAIT表示该进程正在等待某个资源。
Username,Machine分别为ORACLE用户名及机器名
SID,SERIAL#可用于随后的解锁操作
Seconds表示该进程最后一次进行操作至当前的时间(秒)
ID1,锁标识。
某个LOCK状态的ID1与某个WAIT状态的ID1相同,可说明锁的正是另一个进程等待的。
SQL:
锁住资源的SQL语句
2.解除锁
诊断出锁的状态后,若发现该阻塞其它用户进程的进程是正常操作中,则可通知该用户对其进行提交,从而达到释放锁资源的目的;若为非正常操作,即,其状态为"inactive",且其Seconds已为较多长时间,则可执行以下语句将该进程进行清除,系统会自动对其进行回滚,从而释放锁住的资源。
altersystemkillsession'sid,serial#';
例如:
对于上例中显示的结果,可用以下语句清除锁住资源的进程:
altersystemkillsession'12,966';
关于你所说:
在网络断掉(通过拔掉网线)或非正常终止进程(通过taskmanager强行关闭sql*plus)时,oracle在有限的时间内(我只观查了5-10分)内,oracle未能对该进程作任何处理。
这个处理与TCP协议有关,因为SQLNET在使用TCP/IP协议进行网络连接时是一种短连接,当ORACLE连接异常终止时,因为是异常终止,终止信号并没有通过网络通知server端,因此只有下次server有结果从服务器端返回需与client通信时,server才会发现此client已经端掉。
因此出现你前面所提ORACLE处理异常终止进程延时情况.
查锁语句:
查询产生锁的用户锁sql
selecta.usernameusername,a.sidsid,a.serial#serial,b.id1id1,c.sql_textsqltext
fromv$sessiona,v$lockb,v$sqltextc
whereb.id1in
(selectdistincte.id1
fromv$sessiond,v$locke
whered.lockwait=e.kaddr)
anda.sid=b.sid
andc.hash_value=a.sql_hash_value
andb.request=0;
一个加速查询锁表和等待锁的方法
---这些锁定中有"只读锁"、"排它锁","共享排它锁"等多种类型,而且每种类型又有"行级锁"(一次锁住一条记录),"页级锁"(一次锁住一页,即数据库中存储记录的最小可分配单元),"表级锁"(锁住整个表)
---若为"行级排它锁",则除被锁住的该行外,该表中其它行均可被其它的用户进行修改(update)或删除(delete)操作,若为"表级排它锁",则所有其它用户只能对该表进行查询(select)操作,而无法对其中的任何记录进行修改或删除。
当程序对所做的修改进行提交(commit)或回滚后(rollback)后,锁住的资源便会得到释放,从而允许其它用户进行操作。
在oracle8.0.x中执行"获取正在等待锁资源的用户名"的查询语句十分缓慢
selecta.username,a.sid,a.serial#,b.id1
fromv$sessiona,v$lockb
wherea.lockwait=b.kaddr
查找阻塞其它用户的用户进程也十分缓慢
selecta.username,a.sid,a.serial#,b.id1
fromv$sessiona,v$lockb
whereb.id1in
(selectdistincte.id1
fromv$sessiond,v$locke
whered.lockwait=e.kaddr)
anda.sid=b.sid
andb.request=0
一个解决办法。
即通过将问题发生时的v$lock,v$session视图中的相关记录保存于自己建立的表中,再对该表进行查询,则速度大大提高,可以迅速发现问题。
经实际使用,效果非常好。
在接到用户反映后,几秒钟即可查出由于锁住资源而影响其它用户的进程,并进行相应的处理。
首先,以dba身份(不一定为system)登录入数据库中,创建三个基本表:
my_session,my_lock,my_sqltext,并在将会进行查询的列上建立相应的索引。
语句如下:
rem从v$session视图中取出关心的字段,创建my_session表,并在查询要用到的字段上创建索引,以加快查询速度
droptablemy_session;
createtablemy_session
as
selecta.username,a.sid,a.serial#,
a.lockwait,a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
fromv$sessiona
where1=2;
createuniqueindexmy_session_u1onmy_session(sid);
createindexmy_session_n2onmy_session(lockwait);
createindexmy_session_n3onmy_session(sql_hash_value);
----rem从v$lock视图中取出字段,创建my_lock表,并在查询要用到的字段上创建索引,以加快查询速度
droptablemy_lock;
createtablemy_lock
as
selectid1,kaddr,sid,request,type
fromv$lock
where1=2;
createindexmy_lock_n1onmy_lock(sid);
createindexmy_lock_n2onmy_lock(kaddr);
----rem从v$sqltext视图中取出字段,创建my_sqltext表,并在查询要用到的字段上创建索引,以加快查询速度
droptablemy_sqltext;
createtablemy_sqltext
as
selecthash_value,sql_text
fromv$sqltext
where1=2;
createindexmy_sqltext_n1onmy_sqltext(hash_value);
--然后,创建一个sql脚本文件,以便需要时可从sql*plus中直接调用。
其中,首先用truncatetable表名命令将表中的记录删除。
之所以用truncate命令,而不是用delete命令,是因为delete命令执行时,将会产生重演记录,速度较慢,而且索引所占的空间并未真正释放,若反复做insert及delete,则索引所占的空间会不断增长,查询速度也会变慢。
而truncate命令不产生重演记录,速度执行较delete快,而且索引空间被相应地释放出来。
删除记录后,再将三个视图中的相关记录插入自己创建的三个表中。
最后,对其进行查询,由于有索引,同时由于在插入时条件过滤后,记录数相对来说较少,因而查询速度很快,马上可以看到其结果。
----此时,若发现该阻塞其它用户进程的进程是正常操作中,则可通知该用户对其进行提交,从而达到释放锁资源的目的;若为未正常操作,即,其状态为"inactive",且其last_call_et已为较多长时间,则可执行以下语句将该进程进行清除,系统会自动对其进行回滚,从而释放锁住的资源。
altersystemkillsession''sid,serial#''
--sql脚本如下:
setechooff
setfeedbackoff
prompt''删除旧记录.....''
truncatetablemy_session;
truncatetablemy_lock;
truncatetablemy_sqltext;
prompt''获取数据.....'
insertintomy_session
selecta.username,a.sid,a.serial#,
a.lockwait,a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
fromv$sessiona
wherenvl(a.username,''null'')<>''null”;
insertintomy_lock
selectid1,kaddr,sid,request,type
fromv$lock;
insertintomy_sqltext
selecthash_value,sql_text
fromv$sqltexts,my_sessionm
wheres.hash_value=m.sql_hash_value;
columnusernameformata10
columnmachineformata15
columnlast_call_etformat99999heading"seconds"
columnsidformat9999
prompt"正在等待别人的用户"
selecta.sid,a.serial#,
a.machine,a.last_call_et,a.username,b.id1
frommy_sessiona,my_lockb
wherea.lockwait=b.kaddr;
prompt"被等待的用户"
selecta.sid,a.serial#,a.machine,a.last_call_et,a.username
b.type,a.status,b.id1
frommy_sessiona,my_lockb
whereb.id1in
(selectdistincte.id1
frommy_sessiond,my_locke
whered.lockwait=e.kaddr)
anda.sid=b.sid
andb.request=0;
prompt"查出其sql"
selecta.username,a.sid,a.serial#,
b.id1,b.type,c.sql_text
frommy_sessiona,my_lockb,my_sqltextc
whereb.id1in
(selectdistincte.id1
frommy_sessiond,my_locke
whered.lockwait=e.kaddr)
anda.sid=b.sid
andb.request=0
andc.hash_value=a.sql_hash_value;
评论|0引用
ORACLE里锁有以下几种模式:
oracle—作者zybing@10:
22
ORACLE里锁有以下几种模式:
0:
none
1:
null
空
2:
Row-S
行共享(RS):
共享表锁
3:
Row-X
行专用于行的修改
4:
Share
共享锁(S):
阻止其他DML操作
5:
S/Row-X
共享行专用(SRX):
阻止其他事务操作
6:
exclusive
专用(X):
独立访问使用
数字越大锁级别越高,影响的操作越多。
一般查询语句如select...from...;是小于2的锁,有时会在v$locked_object出现。
select...from...forupdate;是2的锁。
当对话使用forupdate子串打开一个游标时,
所有返回集中的数据行都将处于行级(Row-X)独占式锁定,
其他对象只能查询这些数据行,不能进行update、delete或select...forupdate操作。
insert/update/delete...;是3的锁。
没有commit之前插入同样的一条记录会没有反应,
因为后一个3的锁会一直等待上一个3的锁,我们必须释放掉上一个才能继续工作。
创建索引的时候也会产生3,4级别的锁。
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,
但DDL(alter,drop等)操作会提示ora-00054错误。
有主外键约束时update/delete...;可能会产生4,5的锁。
DDL语句时是6的锁。
以DBA角色,查看当前数据库里锁的情况可以用如下SQL语句:
selectobject_id,session_id,locked_modefromv$locked_object;
selectt2.username,t2.sid,t2.serial#,t2.logon_time
fromv$locked_objectt1,v$sessiont2
wheret1.session_id=t2.sidorderbyt2.logon_time;
如果有长期出现的一列,可能是没有释放的锁。
我们可以用下面SQL语句杀掉长期没有释放非正常的锁:
altersystemkillsession'sid,serial#';
如果出现了锁的问题,某个DML操作可能等待很久没有反应。
当你采用的是直接连接数据库的方式,
也不要用OS系统命令$killprocess_num或者$kill-9process_num来终止用户连接,
因为一个用户进程可能产生一个以上的锁,杀OS进程并不能彻底清除锁的问题。
记得在数据库级别用altersystemkillsession'sid,serial#';杀掉不正常的锁。
V$LOCK
字段内容:
SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK
TYPE:
TX(TM,DML,表锁),MR介质恢复,ST磁盘空间管理
LMODE/REQUEST:
01:
空,2:
RS,3:
RX4:
S5:
SRX6:
X
LMODE:
0,1表明进程已经获得一个锁,非0,等待获得一个琐
ID1:
TX状态下,等待锁的对象ID,TM状态下,回滚号码的十进制
ID2:
TM:
0TX:
回滚槽重新使用的次数
V$session_wait
列举了活动会话正在等待的事件,其中P2TEXT,P3