oracle锁整理

LOCK TABLE用来锁定整个表，锁定可分为共享模式或者独占模式，共享模式可以防止其他会话获得独占锁定，但是允许其他会话获得一个共享锁定，独占模式则即防止其他会话获取共享锁定，又防止其他会话获得独占锁定。

ORACLE里锁有以下几种模式:
        0：none
       1：null 空
       2：Row-S 行共享(RS)：共享表锁，sub share   Permits concurrent access to the locked table, but prohibits other users from locking the entire table for exclusive access.
       3：Row-X 行独占(RX)：用于行的修改，sub exclusive   Same as ROW SHARE, but also prohibits locking in SHARE mode. This type of lock is obtained automatically with standard DML commands such as UPDATE, INSERT, or DELETE.  
       4：Share 共享锁(S)：阻止其他DML操作，share    Permits concurrent queries but prohibits updates to the table; this mode is required to create an index on a table and is automatically obtained when using the CREATE INDEX statement.
      5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive     Used to query a whole table and to allow other users to query the  table, but to prevent other users from locking the table in SHARE mode  or updating rows.
      6：exclusive 独占(X)：独立访问使用，exclusive   The most restrictive locking mode; permits queries on the locked table but prohibits any DML by any other users. This mode is required to
drop the table and is automatically obtained when using the DROP TABLE statement.
    
    数字越大锁级别越高, 影响的操作越多。
    
    1级锁有：Select，有时会在v$locked_object出现。
    2级锁有：Select for update,Lock For Update,Lock Row Share 
    select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
    3级锁有：Insert, Update, Delete, Lock Row Exclusive
    没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
    4级锁有：Create Index, Lock Share
    locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
    00054, 00000, "resource busy and acquire with NOWAIT specified"
    // *Cause: Resource interested is busy.
    // *Action: Retry if necessary.
    5级锁有：Lock Share Row Exclusive 
    具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
    6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
   

        以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
            --查看锁对象
            select b.owner,b.object_name,l.session_id,l.locked_mode
            from v$locked_object l, dba_objects b
            where b.object_id=l.object_id
           --查看锁的会话
            select t2.username,t2.sid,t2.serial#,t2.logon_time
            from v$locked_object t1,v$session t2
            where t1.session_id=t2.sid order by t2.logon_time


    如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：
    
    alter system kill session 'sid,serial#';
    
    如果出现了锁的问题, 某个DML操作可能等待很久没有反应。
    
    当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。

查看数据库中的锁（或者某张表上的锁）

select V$SESSION.sid,v$session.SERIAL#,v$process.spid,
rtrim(object_type) object_type,rtrim(owner) || '.' || object_name object_name,
decode(lmode,   0, 'None',
1, 'Null', --空
2, 'Row-S',--行共享(RS)：共享表锁，sub share
3, 'Row-X',--行独占(RX)：用于行的修改，sub exclusive
4, 'Share',--共享锁(S)：阻止其他DML操作，share
5, 'S/Row-X',--共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6, 'Exclusive', --独占(X)：独立访问使用，exclusive
'Unknown') LockMode,
decode(lmode,   0, 'None',
1, '空',
2, '行共享(RS)：共享表锁，sub share',
3, '--行独占(RX)：用于行的修改，sub exclusive ',
4, '共享锁(S)：阻止其他DML操作，share',
5, '共享行独占(SRX)：阻止其他事务操作，share/sub exclusive ',
6, '独占(X)：独立访问使用，exclusive',
'Unknown') LockMode
,ctime, block b,
v$session.username,MACHINE,MODULE,ACTION,
decode(A.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType
from (SELECT * FROM V$LOCK) A, all_objects,V$SESSION,v$process
where A.sid > 6
and object_name<>'OBJ$'
and A.id1 = all_objects.object_id
and A.sid=v$session.sid
and v$process.addr=v$session.paddr
--and object_name like '%TABLE_NAME%';

--6722是ps下的进程号

select s.sid,s.serial#,s.*,p.* from v$session s,v$process p
      where s.paddr=p.addr and p.spid='6722'
 --1    35    1885

--根据sid查看 是执行的sql语句
SELECT  a.sql_text ,a.*
   FROM v$sqltext a
  WHERE a.hash_value = (SELECT sql_hash_value
                          FROM v$session b
                         WHERE b.SID ='90') ;-- 56 120 99

设立封锁机制主要是为了对并发操作进行控制，对干扰进行封锁，保证数据的一致性和准确性。Oracle数据库封锁方式有三种：共享封锁，独占封锁，共享更新封锁
封锁类型
Oracle　RDBMS的封锁类型可分为如下三类：
１、内部级封锁
内部级封锁是用于保护ORACLE内部结构，由系统内部实现，用户不能访问，因此我们不必对此做过多的了解。
２、DDL级封锁（字典/语法分析封锁）
DDL级封锁也是由ORACLE　RDBMS来控制，它用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对SQL定义语句作语法分析时自动地加锁，无需用户干予。字典/语法分析封锁共分三类：
（１）、字典操作锁：用于对字典操作时，锁住数据字典，此封锁是独占的，从而保护任何一个时刻仅能对一个字典操作。
（２）、字典定义锁：用于防止在进行字典操作时又进行语法分析，这样可以避免在查询字典的同时改动某个表的结构。
（３）、表定义锁：用于　一个SQL语句正当访问某个表时，防止字典中与该表有关的项目被修改。
３、DML级封锁
DML级封锁用于控制并发事务中的数据操纵，保证数据的一致性和完整性，其封锁对象可以是表或行。
对用户的数据操纵，Oracle可以自动为操纵的数据进行封锁，但如果有操纵授权，则为满足并发操纵的需要另外实施封锁。DML封锁可由一个用户进程以显式的方式加锁，也可通过某些SQL语句隐含方式实现。

DML锁有如下三种封锁方式：
（1）、共享封锁方式（SHARE）
（2）、独占封锁方式（EXCLUSIVE）
（3）、共享更新封锁（SHARE UPDATE）
其中SHARE，EXCLUSIVE用于表封锁，SHARE UPDATE用于行封锁。
1、共享方式的表封锁
共享方式的表封锁是对表中的所有数据进行封锁，该锁用于保护查询数据的一致性，防止其它用户对已封锁的表进行更更新。其它用户只能对该表再施加共享方式的锁，而不能再对该表施加独占方式的封锁，共享更新锁可以再施加，但不允许持有共享更新封锁的进程做更新。共享该表的所有用户只能查询表中的数据，但不能更新。共享方式的表封锁只能由用户用SQL语句来设置，基语句格式如下：
引用：LOCK TABLE <表名>;[,<表名>;]...
                      IN SHARE MODE [NOWAIT]


执行该语句，对一个或多个表施加共享方式的表封锁。当指定了选择项NOWAIT，若该封锁暂时不能施加成功，则返回并由用户决定是进行等待，还是先去执行别的语句。
持有共享锁的事务，在出现如下之一的条件时，便释放其共享锁：
A、执行COMMIT或ROLLBACK语句。
B、退出数据库（LOG　OFF）。
C、程序停止运行。
共享方式表封锁常用于一致性查询过程，即在查询数据期间表中的数据不发生改变。

2、独占方式表封锁
独占方式表封锁是用于封锁表中的所有数据，拥有该独占方式表封锁的用户，即可以查询该表，又可以更新该表，其它的用户不能再对该表施加任何封锁（包括共享、独占或共享更新封锁）。其它用户虽然不能更新该表，但可以查询该表。
独占方式的表封锁可通过如下的SQL语句来显示地获得：
LOCK TABLE <表名>;[,<表名>;]....
IN EXCLUSIVE MODE [NOWAIT]
独占方式的表封锁也可以在用户执行DML语句INSERT、UPDATE、DELETE时隐含获得。
拥有独占方式表封锁的事务，在出现如下条件之一时，便释放该封锁：
（1）、执行COMMIT或ROLLBACK语句。
（2）、退出数据库（LOG OFF）
（3）、程序停止运行。
独占方式封锁通常用于更新数据，当某个更新事务涉及多个表时，可减少发生死锁。

3、共享更新封锁方式
共享更新封锁是对一个表的一行或多行进行封锁，因而也称作行级封锁。表级封锁虽然保证了数据的一致性，但却减弱了操作数据的并行性。行级封锁确保在用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改。因而行级锁即可保证数据的一致性又能提高数据操作的迸发性。
可通过如下的两种方式来获得行级封锁：
（1）、执行如下的SQL封锁语句，以显示的方式获得：
LOCK TABLE <表名>;[,<表名>;]....
IN SHARE UPDATE　MODE [NOWAIT]
（2）、用如下的SELECT ...FOR UPDATE语句获得：
SELECT <列名>;[,<列名>;]...
FROM <表名>;
WHERE <条件>;
FOR UPDATE OF <列名>;[,<列名>;].....[NOWAIT]
一旦用户对某个行施加了行级封锁，则该用户可以查询也可以更新被封锁的数据行，其它用户只能查询但不能更新被封锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式封锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。
当出现如下之一的条件，便释放共享更新锁：
（１）、执行提交（COMMIT）语句；
（２）、退出数据库（LOG　OFF）
（３）、程序停止运行。
执行ROLLBACK操作不能释放行锁。
从上面讲述可见，ORACLE　RDBMS的加锁机制，解决了并发事务的相容与互斥问题。相容保证事务的并发性，互斥确保数据的一致性。不同用户锁的相容与互斥关系由下图给出。

其中最后一行最后一列为其它用户提供在不同行上设置SHARE UPDATE锁。但当用户１在某行上进行更新操作时，用户２只有等待用户１提交事务后，才能更新自己所封锁的行。

死锁
封锁虽然能够有效的解决并发操作，但是任何资源的独占都会有死锁的危险。例如：有两个事务T1，T2，T1对数据A施加独占封锁，T2对数据B施加了独占封锁。再假设T1要对数据B加锁，由于B已被T2独占封锁，因此T1置于等待状态，等待B被释放；现在若T2也要对A进行封锁，由于A已被T1独占封锁，因此T2也被置于等待状态。这样就形成了两个事务相互等待的状态，而且永远不能结束，此种情况称为死锁。
在Oracle系统中能自动发现死锁，并选择代价最小的，即完成工作量最少的事务予以撤消，释放该事务所拥有的全部锁，记其它的事务继续工作下去。
从系统性能上考虑，应该尽可能减少资源竞争，增大吞吐量，因此用户在给并发操作加锁时，应注意以下几点：
１、对于UPDATE和DELETE操作，应只封锁要做改动的行，在完成修改后立即提交。
２、当多个事务正利用共享更新的方式进行更新，则不要使用共享封锁，而应采用共享更新封锁，这样其它用户就能使用行级锁，以增加并行性。
３、尽可能将对一个表的操作的并发事务施加共享更新锁，从而可提高并行性。
４、在应用负荷较高的期间，不宜对基础数据结构（表、索引、簇和视图）进行修改。

1 .通过netstat查找出来的SPID ，并找出ADDR

2.通过ADDR可以找到SESSION

3.通过V$SESSION中的SADDR可以找到当前执行的TRANSACTION

4.通过SQL_ADDRESS  可以找到SQL_TEXT

 

 根据当前活动的transaction 查询出 ses_addr , 然后找出对应v$session的saddr 

 根据saddr查找出对应的paddr ,根据paddr找到对应v$process 的addr .

 根据addr找到对应的spid ，即当前的服务端机器进程号 (如：8696)。

 根据Ps –ef | grep  8696  命令可以查看到当前连接的客户端的端口号，即客户端工具sqlplus的端口号。

 

一些sql：

select  addr ,sid,username ,s.status , process ,program  from v$session s , v$transaction t   where  s.saddr = t.addr;

-- 查询当前正在活动的进程号：线程号，程序名称

select addr,pid spid , program fromv$process ;

select saddr,  sid, paddr ,username, status  from  v$session

select  sql_text ,address  , hash_value from v$sql q ,v$sessions  where  s.sid=14  and s.PREV_SQL_ADDR=q.ADDRESS ;