锁.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
升级会员 