深入浅出oracle锁（ZT）

在网上看到蛮不错的锁介绍，收藏一下。

深入浅出oracle锁---原理篇

深入浅出oracle锁---锁阻塞的分析

[@more@] 深入浅出oracle锁---原理篇在现代的多用户多任务系统中，必然会出现多个用户同时访问共享的某个对象，这个对象可能是表，行，或者内存结构，为了解决多个用户并发性访问带来的数据的安全性，完整性及一致性问题，必须要有一种机制，来使对这些共享资源的并发性访问串行化，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锁---锁阻塞的分析

上篇深入浅出oracle锁---原理篇是以理论为主，此文章列举三个关于锁阻塞的例子，并对此作详细的说明，话不多说，直接开题。

一：外键没有索引，引起阻塞

外键没有建立索引而引起的阻塞应该是最常见到，下面举例对此详细分析

SQL> DELETE EMP WHERE EMPNO=7900;

已删除1行。

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

10 TX 458791 1813 6 0 416 0

10 TM 30139 0 3 0 416 0

10 TM 30137 0 2 0 416 0

SQL> select trunc(458791/power(2,16)) ,mod(458791,power(2,16)) from dual;

TRUNC(458791/POWER(2,16)) MOD(458791,POWER(2,16))

------------------------- -----------------------

7 39

再来查v$transaction，看看sid=10会话当前使用的undo segment和slot

SQL> select addr,xidusn,xidslot,xidsqn from v$transaction

2 where addr in (select taddr from v$session where sid=10);

ADDR XIDUSN XIDSLOT XIDSQN

-------- ---------- ---------- ----------

67BAB0CC 7 39 1813

对于tx锁，v$lock中的id1即为持有该锁的事务的回滚段号，事务槽号的组合

再来看

SQL> select owner,object_name from dba_objects where object_id in (30139,30137);

OWNER OBJECT_NAME

------------------------------ --------------------------------------------------------------------------------

SCOTT DEPT

SCOTT EMP

对于tm锁，id1即为持有该锁的对象的id，结合上一个查询，可以看到，该会话对30139即emp表加了lmode=3的锁，即rx锁，同时，对30137即dept加了lmode=2的锁，即rs锁。

接下来在另外一会话中执行如下语句

delete dept where1=0；

该会话被阻塞

查看v$lock

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

10 TX 458791 1813 6 0 416 0

10 TM 30139 0 3 0 416 1

10 TM 30137 0 2 0 416 0

12 TM 30139 0 0 4 372 0

12 TM 30137 0 3 0 372 0

可以看到，另一个会话(sid=12)对30137即dept加了lmode=3的锁，即rx锁。同时请求对30139即emp表加了lmode=4的锁，即s锁(request=4)，但该请求被阻塞(s和rx不相容)，可以通过id1,id2,,block字段看到，此会话sid=10会话阻塞。

接下来，在emp的外键字段deptno建立索引

SQL> create index idx_emp_dept on emp(deptno);

Index created

按原步骤重复上面的实验

SQL> DELETE EMP WHERE EMPNO=7900;

已删除1行。

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

10 TX 196632 2251 6 0 386 0

10 TM 30139 0 3 0 386 0

10 TM 30137 0 2 0 386 0

可以看到，是否建立索引，对emp表的锁情况没有影响。

在另外一个会话中执行如下语句

SQL> DELETE DEPT WHERE 1=0;

已删除0行。

该语句没有被阻塞，可以执行。看看当前的锁信息

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

10 TX 196632 2251 6 0 435 0

10 TM 30139 0 3 0 435 0

10 TM 30137 0 2 0 435 0

12 TM 30139 0 2 0 9 0

12 TM 30137 0 3 0 9 0

可以看到，在外键上建立索引后，删除父表，对子表加的是rs锁，比没有索引加s的强度要低，这样避免了阻塞的发生，提高了系统的并行性。

二：位图索引带来的阻塞

位图索引适合建在低基数列上面，在数据仓库比较常用，如果是在并发性要求较高的oltp系统就要慎重了，不当的应用，可能会带来严重的阻塞。

SQL> create bitmap index bidx_emp_job on emp(job);

索引已创建。

SQL> DELETE EMP WHERE EMPNO=7369;

已删除1行。

在另外一会话中执行sql语句

SQL> DELETE EMP WHERE EMPNO=7876;

该语句阻塞

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

10 TX 524312 2362 6 0 116 1

10 TM 30139 0 3 0 116 0

10 TM 30137 0 2 0 116 0

12 TX 589864 2399 6 0 98 0

12 TM 30139 0 3 0 98 0

12 TM 30137 0 2 0 98 0

12 TX 524312 2362 0 4 98 0

可以看到，sid=12的会话等待sid=10持有的tx锁，虽然删除的是不同记录，但因为

empno等于7876，7369的job字段值都是’CLERK’,而我们恰恰在job字段建立了位图索引，第一个dml语句引起了对job=CLERK位图段的锁定，进而阻塞了第二个会话对该位图段其他记录的dml操作（注：如此时对job<>CLERK的记录做dml是不会阻塞的）。

三：Maxtrans带来的阻塞

在创建一个对象的时候，可以指定initrans，maxtrans参数，这二个参数指定了分配给该对象中每个block初始和最大允许并发事务数的，对每个事务，在其受影响的block内都对应一个itl，受其影响的行都会在行的lock byte（lb）位置此itl号（dump一个dml过的但还没有commit或者rollback的block可以清楚的看到），如果在一个并发性非常高的系统中，甚至超过了maxtrans的值(或者block的free space用完)，那么oracle就无法增加itl，因此过低的maxtrans同样会引起sql语句的阻塞。

SQL> drop index bidx_emp_job;

索引已丢弃。

SQL> alter table emp maxtrans 3;

表已更改。

SQL>select distinct dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from emp;

FILE_ID BLOCK_ID

---------- ---------

1 50466

所有的记录都在1号文件的50466块，开三个session，分别删除三条不同的记录，三个session都可以正常执行，现在再开第四个session，删除一个不同的记录，该session被阻塞

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1001589/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1001589/