佩鲁贾地区介绍

      

       在现代的多用户多任务系统中，必然会出现多个用户同时访问共享的某个对象，这个对象可能是表，行，或者内存结构，为了解决多个用户并发性访问带来的数据的安全性，完整性及一致性问题，必须要有一种机制，来使对这些共享资源的并发性访问串行化，oracle中的锁就可以提供这样的功能，当事务在对某个对象进行操作前，先向系统发出请求，对其加相应的锁，加锁后该事务就对该数据对象有了一定的控制权限，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作（可以做select动作，但select 利用的是undo中的前镜像数据了）.
Oracle锁的分类
Oracle锁基本上可以分为二类
a：共享锁（share locks） 也称读锁，s锁
b：排它锁 (exclusive locks) 也称写锁，x锁
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

按锁保护的内容分类
oracle提供多粒度封锁机制，按保护对象来分，据此又可以分为
a：dml锁， data locks 数据锁，用来保护数据的完整性和一致性
b：ddl锁， dictionary locks 字典锁，用来保护数据对象的结构，如table，index的定义
c：内部锁和闩 internal locks and latchs 用来保护数据库内部结构，如sga内存结构

dml锁
DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TM锁的种类有S,X,SR,SX,SRX五种，TX锁称为事务锁或行级锁。当Oracle执行delete,update,insert,select for update DML语句时，oracle首先自动在所要操作的表上申请TM类型的锁。当TM锁获得后，再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位(lb 即lock bytes)进行置位。在记录被某一会话锁定后，其他需要访问被锁定对象的会话会按先进先出的方式等待锁的释放，对于select操作而言，并不需要任何锁，所以即使记录被锁定，select语句依然可以执行，实际上，在此情况下，oracle是用到undo的内容进行一致性读来实现的。

在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。在数据行上只有X锁（排他锁），就是说TX锁只能是排他锁，在记录行上设置共享锁没有意义。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

在数据表上，oracle默认是共享锁，在执行dml语句的时候，oracle会先申请对象上的共享锁，防止其他会话在这个对象上做ddl语句，成功申请表上的共享锁后，再在受影响的记录上加排它所，防止其他会话对这些做修改动作。

这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如表1所示。

和锁相关的性能视图介绍
v$lock
SID          会话的sid，可以和v$session 关联  
TYPE         区分该锁保护对象的类型，如tm，tx，rt，mr等
ID1          锁表示1，详细见下说明               
ID2          锁表示2，详细见下说明          
LMODE        锁模式，见下面说明            
REQUEST      申请的锁模式，同lmode                
CTIME        已持有或者等待锁的时间               
BLOCK        是否阻塞其他会话锁申请 1:阻塞 0:不阻塞

LMODE取值0,1,2,3,4,5,6, 数字越大锁级别越高, 影响的操作越多。
1级锁：
Select，有时会在v$locked_object出现。
2级锁即RS锁
相应的sql有：Select for update ,Lock xxx in Row Share mode，select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁即RX锁
相应的sql有：Insert, Update, Delete, Lock xxx in Row Exclusive mode，没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁即S锁
相应的sql有：Create Index, Lock xxx in Share mode
5级锁即SRX锁
相应的sql有：Lock xxx in Share Row Exclusive mode，当有主外键约束时update/delete ... ; 可能会产生4,5的锁。
6级锁即X锁
相应的sql有：Alter table, Drop table, Drop Index, Truncate table, Lock xxx in Exclusive mode

ID1,ID2的取值含义根据type的取值而有所不同
对于TM 锁
ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0
对于TX 锁
ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号,其组形式:
0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER
ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数
                                            
v$locked_object
XIDUSN               undo segment number ， 可以和v$transaction关联   
XIDSLOT              undo slot number     
XIDSQN               序列号                        
OBJECT_ID            被锁定对象的object_id ，   可以和dba_objects关联
SESSION_ID           持有该锁的session_id，     可以和v$session关联
ORACLE_USERNAME   持有该锁的oracle帐号                    
OS_USER_NAME       持有该锁的操作系统帐号                     
PROCESS              操作系统的进程号，可以和v$process关联     
LOCKED_MODE        锁模式，含义同v$lock.lmode

Dba_locks 和v$lock 内容差不多,略

V$session 如果某个session被因为某些行被其他会话锁定而阻塞，则该视图中的下面四个字段列出了这些行所属对象的相关信息
ROW_WAIT_FILE# 等待的行所在的文件号
ROW_WAIT_OBJ# 等待的行所属的object_id
ROW_WAIT_BLOCK# 等待的行所属的block
ROW_WAIT_ROW#   等待的行在blcok中的位置

手工释放锁
alter system kill session 'sid,serial#';

怎样快速查出Oracle 数据库中的锁等待

---- 在大型数据库系统中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。

---- 这些锁定中有"只读锁"、"排它锁"，"共享排它锁"等多种类型，而且每种类型又有"行级锁"（一次锁住一条记录），"页级锁"（一次锁住一页，即数据库中存储记录的最小可分配单元），"表级锁"（锁住整个表）。

---- 若为"行级排它锁"，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改(Update)或删除(delete)操作，若为"表级排它锁"，则所有其它用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。

---- 但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现"死机"，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。

---- 因而，如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。

---- 由于数据库应用系统越来越复杂， 一旦出现由于锁资源未及时释放的情况，便会引起对一相同表进行操作的大量用户无法进行操作，从而影响到系统的使用。此时，DBA应尽量快地解决问题。但是，由于在Oracle 8.0.x 中执行"获取正在等待锁资源的用户名"的查询语句

select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where a.lockwait = b.kaddr

---- 十分缓慢，（在 Oracle 7.3.4中执行很快)，而且，执行"查找阻塞其它用户的用户进程"的查询语句
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request = 0

---- 执行得也十分缓慢。因而，往往只好通过将 v$session 中状态为"inactive"（不活动）并且最后一次进行操作时间至当前已超过 20 分钟以上(last_call_et>20*60 秒)的用户进程清除，然后才使得问题得到解决。
---- 但是，这种方法实际上是"把婴儿与脏水一起泼掉"。因为，有些用户的进程尽管也为"inactive"，并且也已有较长时间未活动，但是，那是由于他们处于锁等待状态。

---- 因而，我想出了一个解决办法。即通过将问题发生时的 v$lock，v$session视图中的相关记录保存于自己建立的表中，再对该表进行查询，则速度大大提高，可以迅速发现问题。经实际使用，效果非常好。在接到用户反映后，几秒钟即可查出由于锁住资源而影响其它用户的进程，并进行相应的处理。

---- 首先，以 dba 身份（不一定为system）登录入数据库中，创建三个基本表:my_session，my_lock, my_sqltext，并在将会进行查询的列上建立相应的索引。语句如下： rem 从 v$session 视图中取出关心的字段，创建 my_session 表,并在查询要用到的字段上创建索引，以加快查询速度

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where 1=2 ;

create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

---- rem 从 v$lock 视图中取出字段，创建 my_lock 表，并在查询要用到的字段上创建索引，以加快查询速度
drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
from v$lock
where 1=2;

create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

---- rem 从 v$sqltext 视图中取出字段，创建 my_sqltext 表，并在查询要用到的字段上创建索引，以加快查询速度
drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
from v$sqltext
where 1=2;

create index my_sqltext_n1 on my_sqltext ( hash_value);

---- 然后，创建一个 SQL 脚本文件，以便需要时可从 SQL*Plus 中直接调用。其中，首先用 truncate table 表名命令将表中的记录删除。之所以用 truncate 命令，而不是用delete 命令，是因为delete 命令执行时，将会产生重演记录，速度较慢，而且索引所占的空间并未真正释放，若反复做 insert及delete,则索引所占的空间会不断增长，查询速度也会变慢。而 truncate命令不产生重演记录，速度执行较delete快，而且索引空间被相应地释放出来。删除记录后，再将三个视图中的相关记录插入自己创建的三个表中。最后，对其进行查询，由于有索引，同时由于在插入时条件过滤后，记录数相对来说较少，因而查询速度很快，马上可以看到其结果。
---- 此时，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为未正常操作，即，其状态为"inactive"，且其last_call_et已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。

alter system kill session 'sid, serial#';
---- SQL 脚本如下：
set echo off
set feedback off
prompt '删除旧记录.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;

prompt '获取数据.....'
insert into my_session
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where nvl(a.username,'NULL')< >'NULL;

insert into my_lock
select id1, kaddr, sid, request,type
from v$lock;

insert into my_sqltext
select hash_value , sql_text
from v$sqltext s, my_session m
where s.hash_value=m.sql_hash_value;

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

prompt "正在等待别人的用户"
select a.sid, a.serial#,
a.machine,a.last_call_et, a.username, b.id1
from my_session a, my_lock b
where a.lockwait = b.kaddr;

prompt "被等待的用户"
select a.sid, a.serial#,
a. machine, a.last_call_et,a.username,
b. b.type,a.status,b.id1
from my_session a, my_lock b
where b.id1 in
(select distinct e.id1
from my_session d, my_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0;

prompt "查出其 sql "
select a.username, a.sid, a.serial#,
b.id1, b.type, c.sql_text
from my_session a, my_lock b, my_sqltext c
where b.id1 in
(select distinct e.id1
from my_session d, my_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;

---- 以上思路也可用于其它大型数据库系统如 Informix, Sybase,DB2中。通过使用该脚本，可以极大地提高获取系统中当前锁等待的情况，从而及时解决数据库应用系统中的锁等待问题。而且，由于实际上已取出其 program 名及相应的 sql 语句，故可以在事后将其记录下来，交给其开发人员进行分析并从根本上得到解决。