ORACLE-锁
第01部分-如果没有隔离和锁机制并发读写会相互干扰
本帖最后由 zcs0237 于 2013-6-9 14:09 编辑
01.1-未确认的相关性dirty read（脏读｜错读）
1、Mary的原工资为2000, 财务人员将Mary的工资改为了8000(但未提交事务)
2、Mary读取自己的工资 ,发现自己的工资变为了8000，欢天喜地！
3、财务发现操作有误而回滚,Mary的工资还原成2k，Mary读到的数据就是垃圾数据。
01.2-两次结果不一致读nonrepeatable read（不可重复读）
1.在事务1中，Mary 读取了自己的工资为2k,操作并没有完成
2.在事务2中，这时财务人员修改了Mary的工资为3k,并提交了事务.
3.在事务1中，Mary 再次读取自己的工资时，工资变为了3k
01.3-不一致的统计phantom read（幻读|假读）
1.事务1,用where子句读统计目前工资为2k的员工，共10条记录。
2.事务2插入了一条员工记录，这个新行也满足事务1使用的查询where子句工资为2k
3.事务1再次读取所有工资为2k的员工 共读取到了11条记录，这个行被称为幻象，因为对事务1来说，这一行的出现是不可思议的幻像（怀疑看花眼了 ）。
01.4-不知对方已更新Lost update（丢失更新｜重复更新）
公司给员工张三加1k人民币，财务部两名操作员A和B，操作员A和B只要一前一后做提交，这个过程最后结果是：张三开心了这次涨了2k。过程情况若是这样的：
1、A在应用系统的页面上查询出张三的薪水信息，然后选择薪水记录进行修改，打开修改页面但A突然有事离开了，页面放在那没有做任何的提交。
2、这时候B同样在应用中查询出张三的薪水信息，然后选择薪水记录进行修改，录入增加薪水额1000，然后提交了。
3、这时候A操作员回来了，在自己之前打开的薪水修改页面上增加薪水额1k并提交。
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第02部分-用隔离与锁两种控制机制应对并发干扰
本帖最后由 zcs0237 于 2013-6-9 14:22 编辑
数据库是要被客户所共享访问的，在数据库操作过程中很可能出现相互干扰、结果不正确的情况。可从两方面采取措施确保每一用户能以一致的方式读取和修改数据：
02.1-在客户端实施事务隔离：Read Committed不很好解决不可重读、假读|幻读、丢失更新
oracle仅支持SQL92标准中的两个——即SERIALIZABLE | READ COMMITTED
oracle又支持两种非标准的——两种事务级事务隔离（Read Write｜Read Only）
1、两种会话级事务隔离
SQL> set transaction isolation level xxx;
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
2、两种事务级事务隔离（Read Write，同read commited）
SQL> set transaction read xxx;
ORA-02178: correct syntax is: SET TRANSACTION READ { ONLY | WRITE }
02.2-在共享数据上加锁可与事务隔离完美结合：使用合适的锁能解决不可重读、假读|幻读、丢失更新
1、行级6号TX排它锁
2、表级2~6号TX锁
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第03部分-实例演示事务的四种隔离功能
本帖最后由 zcs0237 于 2013-6-10 12:03 编辑
03.1-set transaction read only（支持repeatable read）
一、会话1: 不允许在read only事务中执行DML（SYS除外）
SQL> conn scott/tiger
create table test(id number);
insert into test values(1);
insert into test values(2);
commit;
set transaction read only;
delete from test where id=1;
--ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
Transaction
二、会话2: 只能看到事务开始前已提交结果, 看不到其他会话新提交的修改，即transaction-level read consistency,避免了非重复读和幻读
SQL> conn scott/tiger
insert into test values(3);
commit;
SQL> select * from test;
-- 1
-- 2
-- 3
会话1:
select * from test;
-- 1 //还是只看到两条
-- 2
03.2-set transaction read write（oracle默认, 基于undo的statement-level read consitency）
一、session 1:创建过程
SQL> set sqlprompt '_user @session1> ';
drop table t3 purge;
create table t3 (name varchar2(9),cash int);
insert into t3 values('zcs',10000);
commit;
SYS @session1> set transaction read write;
variable i refcursor;
exec open :i for select * from t3;
二、session 2:修改并提交
SQL> set sqlprompt '_user @session2>';
update t3 set cash=8888 where name='zcs';
commit;
SYS @session2> select '-- CASH '||CASH from t3;
-- CASH 8888 //查看到新值
三、session 1:CR读
SYS @session1> select distinct status,sum(bytes),count(*) from dba_undo_extents group by status;
UNEXPIRED 1310720 5 //有未过期的undo段
EXPIRED 4849664 29
SYS @session1> print i;
zcs 10000 //继续原查询是CR读：过程起始时间的值
SYS @session1> select name,cash from t3;
zcs 8888 //再次查询是当前模式读
03.3-alter session set isolation_level=read commited（默认）
一、session2提交前，session1只能看到原值
Session 1:
SQL> set transaction isolation level read committed;
Session 2:
SQL> update emp set sal=4000 where ename='SCOTT';
Session 1:——只能看到旧值
SQL> select sal from emp where ename='SCOTT';
3000
二、session2提交后，session1能看到新值
Session 2:
SQL> commit;
Session 1:
SQL> select sal from emp where ename='SCOTT';
4000
===================================================
做完实验后恢复emp的值默认值：
SQL> update emp set sal=3000 where ename='SCOTT';
commit;
03.4-alter session set isolation_level=serializable（其它会话看不到串行化事务新提交的数据，影响并发性能）
一、session1：(SYS用户不支持此种隔离)
SCOTT @session1> set heading off ;
create table test (id int) ;
alter session set isolation_level=serializable;
insert into test (id) values (1);
select count(*) from test;
1
二、session2：——无法看到会话1提交的变化
SCOTT @session2> set heading off ;
set sqlprompt '_user @session2> ';
select count(*) from test;
0
SCOTT @session2> insert into test (id) values(2);
commit;
select count(*) from test;
1
三、session2：——无法看到会话2递交的数据
SCOTT @session1> select count(*) from test;
1
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第04部分-补充：事务基础知识
本帖最后由 zcs0237 于 2013-6-10 12:38 编辑
04.1-transaction的组成
一、执行以下SQL 语句时事务开始
insert、delete、update、merge、select for update、lock table
二、遇到下面事件之一结束
1、commit或rollback被发布
2、DDL 或DCL 语句执行(隐式提交和回滚)
3、用户正常退出SQL*Plus等客户程序
4、异常退出
5、set autocommit on每执行一条语句，就自动提交。
三、commit前可基于保存点rollback或完全rollback
create table t5 (id int, name char(10));
insert into t5 values(1,'AAA');
insert into t5 values(1,'BBB');
insert into t5 values(1,'CCC');
commit;
update t5 set id=10 where name='AAA';
savepoint A;
update employee set id=20 where name='BBB';
savepoint B;
update employee set id=30 where name='CCC';
rollback to B;
select * from t5;
rollback;
select * from t5;
04.2-commit write <option>对lgwr的IO优化
1、wait、nowait控制什么时候将redo信息写入到redo logs
2、immediate、batch控制redo信息以怎样的方式写入到redo logs
一、手动指定提交选项
commit write wait; //写redofile完成后才使控制权返还给会话
commit write nowait; //使控制权立即返还给会话
commit write batch; //以批量方式将重做流写入到日志文件中，而不是每次提交都执行写操作。可以用这种技术来减少日志缓冲区刷新。
commit write immediate; //立即写入日志缓冲区
二、commit_write默认选项
1、设置提交选项：
Alter system set commit_write = { nowait| wait| batch,wait | batch,nowait};
Alter setion set commit_write ={ nowait| wait| batch,wait | batch,nowait};
COMMIT默认方式
All clauses after the COMMIT keyword are optional. If you specify only COMMIT, then the default is COMMIT WORK WRITE IMMEDIATE WAIT.
2、提交选项的优先级
对于任何参数，如果完成设置后，则该参数在系统级执行。如果有会话级的设置，则会话级设置优先，而最后如果 COMMIT 语句后面有子句，则该子句优先。
04.3-事务的四大特征——ACID
一、Atomicity
要么完全被执行,要么完全不执行,以保证数据库的一致性,这种特性就叫原子性。如：网上转账……
1、语句级原子性，比如insert语句，如果由于check约束而失败，就不会插入数据。
2、过程级原子性，把PL/SQL匿名块也当作是语句，也具有原子性。
3、事务级原子性，比如A想要从自己的帐户中转1000块钱到B的帐户里。那个从A开始转帐，到转帐结束的这一个过程，称之为一个事务。在这个事务里，要做如下操作：
a 、从A的帐户中减去1000块。如果A的帐户原来有3000块，现在就变成2000块。
b、 在B的帐户里加1000块钱。如果B的帐户原来有2000块，现在变成3000块。
c、 要么一起成功（A帐户成功减少1000，同时B帐户成功增加1000），要么一起失败（A帐户回到原来状态，B帐户也回到原来状态）的操作叫原子性操作。
二、Consistency
数据库中的数据是时刻都有可能发生变化的，但是这种变化必须是可以接受的和合理的，即数据必须保持一致性——一致性保证某个特定时间点数据只有一个特定值。
1、语句级的读一致性=一个单独查询返回的数据是语句开始的单一时间点上的数据集。
语 句性一致性读，比如语句在开始执行之前获得当前的scn号为10001，而另个事务T2可能将该块的行进行修改，设置该行所在块的一致性标记，生成的 SCN＝10002,这种情况就需要应用undo回滚到scn为10001的时刻，即为一致性状态，在单个语句执行期间这个值是不会改变的。
2、事务级的读一致性（可重复读）=所有数据的状态是在事务起始的状态，除非本事务修改的数据，这样就可以避免了不可重复读和幻像读。
三、Isolation
多个并发的事务(含自治事务)间是避免影响互相分离和程度。比如read commited隔离级别：一个事务的影响在该事务提交前对其他事务都不可见。
四、Durability
事务提交了，那么状态就是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第05部分-有哪几种DML锁？
本帖最后由 zcs0237 于 2013-6-13 20:41 编辑
05.1-两种基本锁类型——共享锁与排它锁
一、共享锁（Share Locks，即S锁，读锁）
1、共享锁只用于表级，Oracle在行上无S锁。
2、加了共享锁的对象，可以继续加共享锁，不能再加排他锁。
3、产生表级基本共享锁——4号S锁
create table test (id int, name char(30));
lock table test in share mode;
select lmode,type,block from v$lock where TYPE='TM';
-- 4 TM 0
二、排它锁（Exclusive Locks，即X锁，也叫独占锁，写锁）
1、一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。
2、当执行DDL操作时，就需要在全表上加排他锁
3、加了排他锁后，不能再加任何锁。
4、产生行级基本排它锁——6号X锁
create table test (id int, name char(30));
insert into test values(1,'zcs');
select lmode,type,block from v$lock where TYPE='TX';
-- 6 TX 0
05.2-按自动与主动划分——显式锁与隐式锁
一、隐含锁
Insert/Update/Delete/DDL/DCL会自动锁住需要加锁的资源以保护数据，这种锁是隐含的。
二、显式锁
有时这些自动的锁在实际应用时并不能满足需要，必须人工加for update/lock talble一些锁。
05.3-按数据的锁定时间分类——悲观锁、乐观锁
一、乐观封锁——容易被hung住
认为死锁、锁等待等情况的出现机率不高，把锁定延迟到进行提交更新（直接update）的时候，才会正式对数据的冲突与否进行检测和处理。
1、准备工作
SQL> create table t1 (id number,cash number);
insert into t1 values (1,1);
insert into t1 values (2,2);
commit;
/
2、第一个session：直接update乐观锁定
update t1 set id = 300 where id = 2;
3、第二个session：直接update，被hung住
update t1 set id = 300 where id = 2;
二、悲观封锁——影响并发性能（在此顺便了解select for update的用法）
认为在修改数据库数据的这段时间里存在着也想修改此数据的事务，在用户修改之前select…for update nowait打开一个游标，通知oracle锁定该行。接下来就可以update修改该行。
1、第一个session：悲观锁定，然后修改
SQL> select * from t1 where id=1 for update;
1 1
SQL> update t1 set id = 200 where id = 1;
2、第二个session：再对同一行加锁会出现锁冲突
SQL> select * from t1 for update skip locked;
2 2 //越过锁定的行，返回查询结果
SQL> select * from t1 for update nowait;
ERROR at line 1: //行已被其它会话锁定：直接抛ora-00054-resource busy，需重新select for update；
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> select * from t1 where id = 1 for update wait 3;
ERROR at line 1: //等待3秒后提示锁冲突，无结果，系统会抛ora-30006
ORA-30006: resource busy; acquire with WAIT timeout expired
SQL> select * from t1 where id = 1 for update;
ERROR at line 1: //会等待行锁释放之后，再加锁，返回查询结果。
05.4-按锁的粒度分类——行级锁和表级锁
一、行级锁=eXclusive锁=写锁=TransactionX锁=LMODE6
1、TX锁是Oracle支持锁定的最低级别
行级锁是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作，也不会锁定同一个数据块中的其他数据行。
一个TX锁可以锁定该事务涉及的多行数据，它们都会“指向”该事务的一个相关TX锁。
2、insert、update、delete操作默认加行级锁与select for update默认加此锁。在执行过程中，行级别锁会阻止这一行上的任何其他DML，使其他会话排队等待这个事务执行，直至事务执行commit或rollback。
3、加行锁前自动加TM表级锁以保护表结构
→执行DML语句→语句解析→数据块读入db buffer
→到共享池的锁池中检查表级TM锁相容性（不逐行检查锁标志）→获得表的TM锁（保护表结构）
→申请TX行锁（在块头ITL表中申请一空闲项）
→到undo表空间申请undo段
→将uba-xid写入到刚刚在块头申请到的空闲ITL表项中→在行头写itl项目编号
→事务开始
→copy行记录到undo段→修改行记录内容并放入dirty list
→当其它用户想并发修改同一条记录时，会根据记录头的ITL“指针”读取数据块头部ITL表项的内容，查看flag标志以确认这个事务是否已经提交。
→如果没有提交，出现锁等待，这个用户会等待前一个用户的TX锁的释放。
→commit或rollback 释放TM锁和TX锁
二、表级锁(TM)
1、TM表级锁的种类有S,X,SR,SX,SRX五种，限制程度升序排列为2、3、4、5、6，级别越高并发度最小
2、SRX(SSX): 事务既对表加S锁+同时要在个别行上加X锁
3、由insert/update/delete获得的X锁在表级表征为RX锁
SYS@session1 > insert into test values(1,'zcs');
select lmode,type,block from v$lock where TYPE=('TM');
-- 3 TM 0
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第06部分-模拟各种TM表级锁（lock table在工作中很少用）
本帖最后由 zcs0237 于 2013-6-10 10:26 编辑
06.1-Row Share=SS=2=作用：其它会话可DML和DDL
SYS@session1> set heading off
grant dba to scott;
conn scott/tiger
drop table test purge;
create table test (id int, name char(30));
insert into test select object_id,object_name from all_objects where rownum<99;
commit;
一、会话1——得到2号TM锁=SS锁
SCOTT@session1 > lock table test in row share mode;
select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 2 TM 0 0
二、会话2——可以DDL，申请独占锁hung住
SCOTT@session2 > alter table test drop column name;
Table altered.
SCOTT@session2 > lock table test in exclusive mode; --hung住
三、会话1——原因：2号行共享锁阻挡了6号TM独占锁请求
SYS@session1 > select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 2 TM 0 1
-- 0 TM 6 0
06.2-Row eXclusive=SX=3=作用：其它会话可DML及加2号行共享TM锁
一、会话1——获得3号TM行排它锁
SCOTT@session1 > lock table test in row exclusive mode;
select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 3 TM 0 0
二、会话2——其它会话可任意DML，可加行共享锁，不能加表共享锁
SCOTT @session2 > update test set id=1 where rownum=1;
SCOTT @session2 > rollback;
lock table test in row share mode;
select lmode,type,request,block from v$lock where sid in (select distinct sid from v$mystat);
-- 2 TM 0 0
SCOTT @session2 > rollback;
lock table test in share mode; --hung住
三、会话1——原因：3号行排它TM锁阻挡了4号TM表共享锁请求
SCOTT @session1 > select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 3 TM 0 1
-- 0 TM 4 0
06.3-Share=4=作用：其他会话不能DML,DDL，能加共享锁（若第一个s锁结束退出，第二个s锁继续避免出现幻读）
一、会话1——加4号TM表共享锁
SCOTT @session1 > lock table test in share mode;
select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 4 TM 0 0
二、会话2——加第2个4号TM表共享锁,若会话1退出仍然有一个4号共享TM锁继续防止DML避免出现幻读
SCOTT @session1 > lock table test in share mode;
select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 4 TM 0 0
-- 4 TM 0 0
SCOTT@session2 > rollback;
insert into test values(1,'zcs'); --hung住
三、会话3——hung住
SYS@session3 > select * from test where rownum=1 for update;
四、会话1——原因：5号共享行排它阻挡了3号TM行排它锁请求
SCOTT @session1 > select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 5 TM 0 1
-- 0 TM 3 0
06.4-Share Row eXclusive=SSX=5=作用：此时表只能被本会话专用，其它用户只读
一、会话1——事务既对表加S锁+同时要在个别行上加X锁
SCOTT@session1 > lock table test in share row exclusive mode;
select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
--5 TM 0
二、会话2——其它会话可以加2号锁
SCOTT@session2 > lock table test in share update mode;
select lmode,type,request,block from v$lock where sid in (select distinct sid from v$mystat);
-- 2 TM 0 0
SCOTT @session2 > rollback;
SCOTT @session3 > lock table test in row share mode;
select lmode,type,request,block from v$lock where sid in (select distinct sid from v$mystat);
-- 2 TM 0 0
SCOTT @session3 > rollback;
三、会话2——其它会话DML会被hung住
SCOTT @session2 > insert into test values(1,'zcs');
四、会话1——原因：5号TM共享行排它锁阻挡了3号TM行排它锁请求
SCOTT @session1 > select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 5 TM 0 1
-- 0 TM 3 0
06.5-eXclusive=6=作用：此时表只能被本会话专用锁定，其它用户只读
一、会话1——加6号TM表排它锁
SCOTT @session1 > lock table test in exclusive mode;
select lmode,type,block from v$lock where type in ('TM','TX');
-- 6 TM 1
二、会话2——插入被hung住
SCOTT @session2 > insert into test values(1,'zcs');
三、会话3——加表共享4号表锁被hung住
SCOTT @session3 > lock table test in share mode;
四、会话1——原因：申请3号TM行排它和4号表共享锁时被hung住
SCOTT @session1 > select lmode, type,request,block from v$lock where TYPE in ('TM','TX');
-- 6 TM 0 1
-- 0 TM 3 0
-- 0 TM 4 0
06.6-禁止TM表级锁（降低一致性，无实际意义）
SQL> alter table test disable table lock;
select table_name, table_lock from user_tables where TABLE_NAME='TEST';
--TEST DISABLED
SQL> Lock table emp in share mode;
--ORA-00069: cannot acquire lock -- table locks disabled for TEST
SQL> alter table test enable table lock;
update test set name='scott' where rownum=1;
select lmode,type,block from v$lock where type in ('TM','TX');
-- 6 TX 0
SQL> alter table test enable table lock;
update test set name='scott' where rownum=1;
select lmode,type,block from v$lock where type in ('TM','TX');
-- 3 TM 0
-- 6 TX 0
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第07部分-追踪一个动作会申请哪些enqueue锁——10704 event
本帖最后由 zcs0237 于 2013-6-10 13:25 编辑
SYS@session1> create table test (id int, name char(30));
insert into test values(1,'zcs');
commit;
create index test_idx on test(ID);
07.1-跟踪100704 event
SYS@session1> !oerr ora 10704
10704, 00000, "Print out information about what enqueues are being obtained"
// *Cause: When enabled, prints out arguments to calls to ksqcmi and
// ksqlrl and the return values.
// *Action: Level indicates details:
// Level: 1-4: print out basic info for ksqlrl, ksqcmi
// 5-9: also print out stuff in callbacks: ksqlac, ksqlop
// 10+: also print out time for each line
SYS@session1> alter session set events '10704 trace name context forever, level 12';
alter index test_idx rebuild online;
alter session set events '10704 trace name context off';
oradebug setmypid;
oradebug tracefile_name;
--/u01/app/oracle/admin/orcl/udump/orcl_ora_4695.trc
07.2-分析100704 trace文件
$ grep 'mode=' /u01/app/oracle/admin/orcl/udump/orcl_ora_4723.trc|egrep 'TM,|TX,'
ksqcmi: TM,c8ee,0 mode=4 timeout=21474836
ksqcmi: TM,c8ee,0 mode=2 timeout=21474836
ksqcmi: TM,c8ee,0 mode=4 timeout=21474836
ksqcmi: TM,c8f4,0 mode=6 timeout=0
其中的”1b79″,”1c15″是object_id的16进制号，可以转换成10进制察看相应的object_name
SQL> select to_number('c8ee','xxxxxxxxxxxx') from dual;
51438
SQL> select to_number('c8f4','xxxxxxxxxxxx') from dual;
51444
SQL> select object_name,object_type from user_objects where object_id=51438;
TEST TABLE
SQL> select object_name,object_type from user_objects where object_id=51444;
no rows selected
51444对应的是SYS_JOURNAL_ 的临时logging Table. Rebuild成功后被drop掉了。
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第08部分-加锁也可能带来麻烦——死锁
本帖最后由 zcs0237 于 2013-6-9 10:47 编辑
1、Deadlocks通常发生在两个或多个sessions对被彼此锁住的资源发出请求的情况下。其最常见的锁的类型为：row-level locks和block-level locks。
2、Oracle会自动侦察到死锁情况，并通过回滚其中一个造成死锁的语句，从而释放其中一个锁来解决它。需要说明的，如果一个事务中的某个语句造成死锁现象，回滚的只是这个语句而不是整个事务。
08.1-模拟00060死锁（oracle主动牺牲掉一个变为锁等待） +00054资源被占用
一、会话1：update id=1
SYS@session1> conn / as sysdba
set heading off
grant dba to scott;
set sqlprompt "_user'@'session1> "
conn scott/tiger
SYS@session1> drop table test purge;
create table test (id int, name char(30)) ;
insert into test values(1,'name');
insert into test values(2,'name');
commit;
update test set name = 'name1' where id = 1;
二、会话2：update id=2
SYS@session2> set heading off
set sqlprompt "_user'@'session2> "
conn scott/tiger
SCOTT@session2> update test set name = 'name2' where id = 2;
三、会话1：update id=2，与会话1冲突，hung住
SCOTT@session1> update test set name = 'name2' where id = 2; --hung住
四、会话2：update id=2，出现死锁，两个会话自动牺牲掉一个
SCOTT@session2> update test set name = 'name1' where id = 1; --hung住
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
五、会话3：模拟ORA-00054
SCOTT@session3> set sqlprompt "_user'@'session3> "
conn scott/tiger
drop table test purge;
ORA-00054: resource busy and acquire with NOWAIT specified
08.2-从.trc文件可看出哪个语句造成的阻塞
$ tail -n 1 alert_orcl.log
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/orcl/udump/orcl_ora_3996.trc.
$ grep --color=auto -A1 'Current SQL Statement:' /u01/app/oracle/admin/orcl/udump/orcl_ora_3930.trc
Current SQL Statement:
update test set name = 'name1' where id = 1
$ grep --color=auto -A4 'Rows waited on:' /u01/app/oracle/admin/orcl/udump/orcl_ora_3930.trc
Rows waited on: //行级锁row-level locks
Session 154: obj - rowid = 0000C8E9 - AAAMjpAAEAAAAA/AAA (dictionary objn - 51433, file - 4, block - 63, slot - 0)
Session 144: obj - rowid = 0000C8E9 - AAAMjpAAEAAAAA/AAB (dictionary objn - 51433, file - 4, block - 63, slot - 1)
08.3-手工查找找出谁造成的阻塞（以及详细信息）
一、用v$locked_object查询被锁的对象
SYS@session3> set pagesize 999
select distinct OBJECT_ID from v$locked_object;
51433
二、用v$lock查看谁是受害者
SYS@session3> set linesize 999
select SID, LMODE,TYPE,REQUEST,BLOCK from v$lock where LMODE=0;
154 0 TX 6 0
三、用dba_blockers找出谁是坏蛋
SYS@session3> select * from dba_blockers;
-- 144
四、用v$session找出坏蛋用哪个SQL_ID阻塞的
SYS@session3> select sql_id from v$session where blocking_session in (select * from dba_blockers);
1zjubay7kw9mu
五、用v$sql找出坏蛋用哪个SQL语句阻塞的
SYS@session3> select SQL_FULLTEXT from v$sql where SQL_ID='1zjubay7kw9mu';
update test set name = 'name1' where id = 1
08.4-手动释放锁等待
一、通过dba_blockers表中的HOLDING_SESSION字段可以查询到坏蛋的ID
SYS@session3>select * from dba_blockers; 或 select distinct blocking_session from v$session;
159
二、使用v$session视图获取坏蛋sid和serial#
SYS@session3>select '-- alter system kill session ''' || sid || ',' || serial# || ''';' FROM v$session WHERE sid IN (select * from dba_blockers);
-- alter system kill session '146,49';
补充：把持有锁的和被阻挡的两个会话全部找出，解决resource busy不能执行DDL问题
conn / as sysdba
select '-- alter system kill session ''' || sid || ',' || serial# || ''';' FROM v$session WHERE sid IN (select SID from v$lock where LMODE=0 or block!=0);
-- alter system kill session '143,12';
-- alter system kill session '144,17';
三、kill掉相应的session
SYS@session3> alter system kill session '159,5' immediate;
System altered.
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
第09部分-加锁也可能带来麻烦——了解发生锁等待的各种情况
本帖最后由 zcs0237 于 2013-6-10 10:28 编辑
一、操作同一行数据引发的锁阻塞
Sess#1：获得独占锁select * from scott.dept for update
Sess#2: 申请的独占锁update scott.dept set dname='IT' where deptno=80;
二、实体完整性引发的insert锁阻塞
原因：多个会话试图在具有唯一性约束的列中插入相同的值。
解决方法：用oracle 提供的sequence来取值
Sess#1：insert into scott.dept(deptno,dname) values(60,'IT');
Sess#2：insert into scott.dept(deptno,dname) values(60,'IT');
三、参照完整性引发的锁阻塞
Sess#1：insert into scott.dept(deptno,dname) values(60,'IT');
Sess#2：insert into scott.emp(empno,ename,deptno) values(1234,'Bryan',60);
SQL> select sid, lmode, request from v$lock where REQUEST!=0;
SID LMODE REQUEST
---- ----- ----------
142 0 4
四、外键未加索引引发的锁阻塞
Sess#1：delete from scott.emp where 0=1;
Sess#2：delete from scott.dept where 0=1;
五、锁的排队机制引发的锁阻塞
Sess#1：update scott.emp set sal=5555 where empno=7499;
Sess#2：lock table scott.emp in share mode;
SQL> select sid, request from v$lock where REQUEST!=0;
142 4
六、ITL Slot引发的锁阻塞
若并发事务数量特别多超过ITL的Slot数量(10g中Maxtrans为255不可改)，则会引起阻塞；
七、位图索引带来的阻塞
位图索引适合建在低基数列上面，在数据仓库比较常用，如果是在并发性要求较高的oltp系统就要慎重了，不当的应用，可能会带来严重的阻塞。
Session1> create bitmap index bidx_emp_job on emp(job); --在job字段建立了位图索引
Session1> DELETE EMP WHERE EMPNO=7369;
Session2> DELETE EMP WHERE EMPNO=7876; --hung住
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1795446&page=1#pid21478502>
