13、管理数据并发――锁@H_404_1@
1、锁机制@H_404_1@
高级数据并发处理@H_404_1@
在事务处理(commit或rollback)结束之前一直持有锁@H_404_1@
@H_404_1@
undo快记录快镜像 block before immage.@H_404_1@
@H_404_1@
DML锁:exclusive(排他)@H_404_1@
table lock表锁 row exclusive 行级锁@H_404_1@
@H_404_1@
2、锁冲突@H_404_1@
悲观锁、乐观锁@H_404_1@
悲观锁: select * from a for update; 自动加锁(for update)@H_404_1@
@H_404_1@
锁冲突原因:未提交更改、长时间运行事务。不必要的高级别锁(悲观锁)@H_404_1@
v$lock@H_404_1@
v$locked_object@H_404_1@
dba_waiters@H_404_1@
dba_blockers;@H_404_1@
v$transaction;@H_404_1@
@H_404_1@
update scott.emp set sal=sal+100 where EMPNO='7369';@H_404_1@
@H_404_1@
提交事务或者kill session@H_404_1@
@H_404_1@
select sid,serial#,username from v$session where sid in (select blocking_session from v$session);@H_404_1@
@H_404_1@
sql> select sid,username from v$session where sid in (select blocking_session from@H_404_1@
v$session);@H_404_1@
SID SERIAL# USERNAME@H_404_1@
---------- ---------- ------------------------------@H_404_1@
21 7 SYS@H_404_1@
@H_404_1@
(2)kill session;@H_404_1@
@H_404_1@
alter system kill session '21,7' immediate;@H_404_1@
@H_404_1@
生产环境:@H_404_1@
@H_404_1@
sql>spool kill_session.sql@H_404_1@
@H_404_1@
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select blocking_session from v$session);@H_404_1@
@H_404_1@
@H_404_1@
4、死锁@H_404_1@
@H_404_1@
select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;@H_404_1@
@H_404_1@
sql> select sid,'TX') orde@H_404_1@
r by 1,2;@H_404_1@
SID TY ID1 ID2 LMODE REQUEST BLOCK@H_404_1@
---------- -- ---------- ---------- ---------- ---------- ----------@H_404_1@
18 TM 73201 0 3 0 0@H_404_1@
18 TX 393218 1952 0 6 0@H_404_1@
148 TM 73201 0 3 0 0@H_404_1@
148 TX 393218 1952 6 0 1@H_404_1@
@H_404_1@
sql> select XIDUSN,XIDSLOT,XIDSQN from v$transaction where XIDSQN=1952;@H_404_1@
@H_404_1@
XIDUSN XIDSLOT XIDSQN@H_404_1@
---------- ---------- ----------@H_404_1@
6 2 1952@H_404_1@
@H_404_1@
sid表示会话ID,TM表示表级锁,TX表示行级锁,XIDSLOT表表示事务槽 ,ID2(1952)表示事务 ,XIDUSN表示回滚段 。@H_404_1@
@H_404_1@
select trunc(393218/65535),mod(393218,65535) from dual;@H_404_1@
@H_404_1@
sql> select trunc(393218/65535),65535) from dual;@H_404_1@
@H_404_1@
TRUNC(393218/65535) MOD(393218,65535)@H_404_1@
------------------- -----------------@H_404_1@
6 8@H_404_1@