oracle lock
今天项目上碰到了oracle死锁的情况，感觉收获很大，现将自己看过的觉得比较有用的资料总结如下以备以后使用。
先来几个表。
1.v$Session
在本视图中，每一个连接到数据库实例中的session都拥有一条记录。包括用户session及后台进程如DBWR，LGWR，arcchiver等等。
V$SESSION中的常用列
V$SESSION是基础信息视图，用于找寻用户SID或SADDR。不过，它也有一些列会动态的变化，可用于检查用户。如例：
SQL_HASH_VALUE，SQL_ADDRESS：这两列用于鉴别默认被session执行的SQL语句。如果为null或0，那就说明这个session没有执行任何SQL语句。PREV_HASH_VALUE和PREV_ADDRESS两列用来鉴别被session执行的上一条语句。
注意：当使用SQL*Plus进行选择时，确认你重定义的列宽不小于11以便看到完整的数值。
STATUS：这列用来判断session状态是：
Achtive：正执行SQL语句(waiting for/using a resource)
Inactive：等待操作(即等待需要执行的SQL语句)
Killed：被标注为删除
下列各列提供session的信息，可被用于当一个或多个combination未知时找到session。
Session信息
SID：SESSION标识，常用于连接其它列
SERIAL#：如果某个SID又被其它的session使用的话则此数值自增加(当一个 SESSION结束，另一个SESSION开始并使用了同一个SID)。
AUDSID：审查session ID唯一性，确认它通常也用于当寻找并行查询模式
USERNAME：当前session在oracle中的用户名。
Client信息
数据库session被一个运行在数据库服务器上或从中间服务器甚至桌面通过SQL*Net连接到数据库的客户端进程启动，下列各列提供这个客户端的信息
OSUSER：客户端操作系统用户名
MACHINE：客户端执行的机器
TERMINAL：客户端运行的终端
PROCESS：客户端进程的ID
PROGRAM：客户端执行的客户端程序
要显示用户所连接PC的 TERMINAL、OSUSER，需在该PC的ORACLE.INI或Windows中设置关键字TERMINAL，USERNAME。
Application信息
调用DBMS_APPLICATION_INFO包以设置一些信息区分用户。这将显示下列各列。
CLIENT_INFO：DBMS_APPLICATION_INFO中设置
ACTION：DBMS_APPLICATION_INFO中设置
MODULE：DBMS_APPLICATION_INFO中设置
下列V$SESSION列同样可能会被用到：
ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#
2.V$SESSION_WAIT
这是一个寻找性能瓶颈的关键视图。它提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做，则显示它最后的等待事件)。当系统存在性能问题时，本视图可以做为一个起点指明探寻问题的方向。V$SESSION_WAIT中，每一个连接到实例的session都对应一条记录。
V$SESSION_WAIT中的常用列
SID: session标识
EVENT: session当前等待的事件，或者最后一次等待事件。
WAIT_TIME: session等待事件的时间(单位，百分之一秒)如果本列为0，说明session当前session还未有任何等待。
SEQ#: session等待事件将触发其值自增长
P1, P2, P3: 等待事件中等待的详细资料
P1TEXT, P2TEXT, P3TEXT: 解释说明p1,p2,p3事件
附注：
1.State字段有四种含义﹕
Waiting：SESSION正等待这个事件。
Waited unknown time：由于设置了timed_statistics值为false，导致不能得到时间信息。表示发生了等待，但时间很短。
Wait short time：表示发生了等待，但由于时间非常短不超过一个时间单位，所以没有记录。
Waited knnow time：如果session等待然后得到了所需资源，那么将从waiting进入本状态。
Wait_time值也有四种含义：
值>0：最后一次等待时间(单位：10ms)，当前未在等待状态。
值=0：session正在等待当前的事件。
值=-1：最后一次等待时间小于1个统计单位，当前未在等待状态。
值=-2：时间统计状态未置为可用，当前未在等待状态。
3.Wait_time和Second_in_wait字段值与state相关:
如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
3.v$SESSION_EVENT
本视图记录了每个session的每一项等待事件。由上文所知V$SESSION_WAIT显示了session的当前等待事件，而V$SESSION_EVENT则记录了session自启动起所有的事件。
V$SESSION_EVENT中的常用列
SID：session标识
EVENT：session等待的事件
TOTAL_WAITS：此session当前事件的总等待数
TIME_WAITED：此session总等待时间(单位，百分之一秒)
AVERAGE_WAIT：此session当前事件平均等待时间(单位，百分之一秒)
TOTAL_TIMEOUTS：等待超时次数
其它用法与V$SESSION_WAIT相似，不详述了
4.v$Lock
本视图列出Oracle 服务器当前拥有的锁以及未完成的锁或栓锁请求。如果你觉着session在等待等待事件队列那你应该检查本视图。如果你发现session在等待一个锁。那么按如下先后顺序：
1.使用V$LOCK找出session持有的锁。
2.使用V$SESSION找出持有锁或等待锁的session执行的sql语句。
3.使用V$SESSION_WAIT找出什么原因导致session持有锁堵塞。
4.使用V$SESSION获取关于持有锁的程序和用户的更多信息。
V$LOCK中的常用列
·SID：表示持有锁的会话信息。
·TYPE：表示锁的类型。值包括TM和TX等。
·LMODE：表示会话等待的锁模式的信息。用数字0－6表示。
·REQUEST：表示session请求的锁模式的信息。
·ID1,ID2：表示锁的对象标识。
公共锁类型
在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁，如下表1。
TX：行级锁，事务锁
·在改变数据时必须是排它模式(mode 6)。
·每一个活动事务都拥有一个锁。它将在事务结束(commit/rollback)时释放。
·如果一个块包括的列被改变而没有ITL(interested transaction list)槽位(entries)，那么session将锁置于共享模式(mode 4)。当session获得块的ITL槽位时释放。
·当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。
·指出回滚段和事务表项
按下列项以避免竞争：
·避免TX－6类型竞争，需要根据您的应用而定。
·避免TX－4类型竞争，可以考虑增加对象INITRANS参数值。
TM：表级锁
·数据库执行任何DDL语句时必须是排它模式；例如，alter table,drop table。
·执行像insert,update,delete这类DML语句时处于共享模式。它防止其它session对同一个对象同时执行ddl语句。
·任何对象拥有正被改变的数据，TM锁都将必须存在。
·锁指向对象。
在TM队列避免竞争，可以考虑屏蔽对象表级锁，屏蔽表级锁防止对象执行任何ddl语句。
ST：空间事务锁
·每个数据库(非实例)拥有一个ST锁。
·除了本地管理表空间，在space管理操作(新建或删除extents)时必须是排它模式。
·对象creation, dropping, extension, 以及truncation都处于这种锁
·多数公共原因的争夺，是在磁盘排序(并非使用真正的临时表空间)或回滚段扩展或收缩。
按如下项以避免竞争：
·使用真正的临时表空间(true temporary tablespaces)，利用临时文件。临时段在磁盘排序之后并不创建或删除。
·使用本地管理表空间。
·指定回滚段避免动态扩展和收缩，或使用自动undo management。
·避免应用执行创建或删除数据库对象。
UL：用户定义锁用户可以自定义锁。内容较多并与此节关系不大，略过。
如果session在等待锁，这可被用于找出session持有的锁，。
可被用于找出DML锁类型的被锁对象(type='TM')
可被用于找出行级事务锁(TYPE='TX')使用中的回滚段，不过，需要通过V$TRANSACTION连接查询得到。
表1 Oracle的TM锁类型
锁模式 锁描述 解释 SQL操作
0 none
1 NULL 空 Select
2 SS(Row-S) 行级共享锁，其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行级排它锁，在提交前不允许做DML操作 Insert、Update、Delete、Lock row share
4 S(Share) 共享锁 Create index、Lock share
5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive
6 X(Exclusive) 排它锁 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive
数字越大锁级别越高, 影响的操作越多。一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。select ... from ... for update; 是2的锁。
当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。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的锁。
如果出现了锁的问题, 某个DML操作可能等待很久没有反应。当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。记得在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。
5v$locked_object
本视图列出系统上的每个事务处理所获得的所有锁。
V$LOCKED_OBJECT中的列说明：
XIDUSN：回滚段号
XIDSLOT：槽号
XIDSQN：序列号
OBJECT_ID：被锁对象ID
SESSION_ID：持有锁的sessionID
ORACLE_USERNAME：持有锁的Oracle 用户名
OS_USER_NAME：持有锁的操作系统 用户名
PROCESS：操作系统进程号
LOCKED_MODE：锁模式，值同上表1
自己用的sql：
select s.USERNAME,s.OSUSER,object_name,session_id,os_user_name,oracle_username,l.process,locked_mode,a.status
from v$locked_object l ,all_objects a,v$session s
where l.OBJECT_ID=a.object_id and s.sid=l.SESSION_ID
alter system kill session '136,1740'（sid，serial#）
附：
资料地址：
http://junsansi.itpub.net/category/29894/47308。
这个写的好详细。什么时候能达到他的水平就好了。哈！
