1.测试目的
当插入重复数据到有unique索引的表中时，采用何种加锁机制。
2.测试思路
利用10046确定是什么操作导致加锁阻塞了进程；
dump锁定前最近一次操作的块结构来分析加锁机制。
3.测试环境
SQL> select * from v$version where rownum=1;
BANNER
-----------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
4.测试过程
表test2及其索引idx_test2_id对应的object_id。
SQL> select object_id,object_name from dba_objects where object_name in ('TEST2','IDX_TEST2_ID');
OBJECT_ID OBJECT_NAME
---------- ---------------
27667 TEST2
27668 IDX_TEST2_ID
--session 1
SQL> insert into test2 values(1,'a');
已创建 1 行。
此处不提交。
--session 2
SQL> alter system flush buffer_cache;
系统已更改。
SQL > alter session set events ‘10046 trace name context forever,level 8’;
SQL> insert into test2 values(1,'a');
此处一直处于等待状态。
然后将session 1提交，这时session 2报错。
下面dump片断是在sesson 2处于等待状态时的10046跟踪结果：
=====================
PARSING IN CURSOR #2 len=31 dep=0 uid=34 oct=2 lid=34 tim=10705648912 hv=4047255222 ad='277dc964' sqlid='9u2g41msmsdpq'
insert into test2 values(1,'a')
END OF STMT
PARSE #2:c=15625,e=92942,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=10705648905
WAIT #2: nam='db file sequential read' ela= 22644 file#=5 block#=170050 blocks=1 obj#=27667 tim=10705671989
WAIT #2: nam='db file sequential read' ela= 10704 file#=5 block#=170049 blocks=1 obj#=27667 tim=10705682866
WAIT #2: nam='db file sequential read' ela= 299 file#=5 block#=170048 blocks=1 obj#=27667 tim=10705683300
WAIT #2: nam='db file scattered read' ela= 25431 file#=5 block#=170051 blocks=5 obj#=27667 tim=10705708852
WAIT #2: nam='db file sequential read' ela= 13716 file#=3 block#=256 blocks=1 obj#=0 tim=10705722841
WAIT #2: nam='db file sequential read' ela= 15175 file#=3 block#=35109 blocks=1 obj#=0 tim=10705738184
WAIT #2: nam='db file scattered read' ela= 22759 file#=5 block#=170056 blocks=8 obj#=27668 tim=10705775416
WAIT #2: nam='db file sequential read' ela= 14768 file#=3 block#=160 blocks=1 obj#=0 tim=10705790482
*** 2012-06-13 11:15:22.984
WAIT #2: nam='enq: TX - row lock contention' ela= 34452785 name|mode=1415053316 usn<<16 | slot=196626 sequence=906 obj#=0 tim=10740253456
WAIT #2: nam='latch: cache buffers chains' ela= 44613 address=783740924 number=150 tries=0 obj#=0 tim=10740298330
=====================
上面黑体部分，27667是表test2,27668是索引idx_test2_id，这个地方报等待事件“enq: TX - row lock contention”了。
下面这部分片断为session 1提交后，session 2报违反唯一性错误时的10046片断。
=====================
PARSING IN CURSOR #1 len=84 dep=1 uid=0 oct=3 lid=0 tim=10740356283 hv=2686874206 ad='29cece34' sqlid='2skwhauh2cwky'
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #1:c=0,e=916,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=10740356276
EXEC #1:c=0,e=2776,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2024304382,tim=10740359417
WAIT #1: nam='db file sequential read' ela= 8833 file#=1 block#=337 blocks=1 obj#=36 tim=10740368472
WAIT #1: nam='db file sequential read' ela= 18496 file#=1 block#=36489 blocks=1 obj#=36 tim=10740387126
WAIT #1: nam='db file sequential read' ela= 13908 file#=1 block#=35771 blocks=1 obj#=18 tim=10740401184
WAIT #1: nam='db file sequential read' ela= 6505 file#=1 block#=217 blocks=1 obj#=11 tim=10740407986
WAIT #1: nam='db file sequential read' ela= 343 file#=1 block#=210 blocks=1 obj#=22 tim=10740408476
FETCH #1:c=0,e=49001,p=5,cr=5,cu=0,mis=0,r=1,dep=1,og=4,plh=2024304382,tim=10740408587
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=5 pw=0 time=0 us cost=4 size=45 card=1)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=3 pw=0 time=0 us cost=3 size=27 card=1)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=2 pw=0 time=0 us cost=2 size=0 card=1)'
STAT #1 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=0 us cost=1 size=18 card=1)'
STAT #1 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)'
CLOSE #1:c=0,e=341,dep=1,type=0,tim=10740409034
EXEC #2:c=15625,e=34772386,p=26,cr=9,cu=13,mis=0,r=0,dep=0,og=1,plh=0,tim=10740421590
ERROR #2:err=1 tim=10740421776
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=0 us)'
WAIT #2: nam='SQL*Net break/reset to client' ela= 10 driver id=1111838976 break?=1 p3=0 obj#=0 tim=10740422117
WAIT #2: nam='SQL*Net break/reset to client' ela= 311 driver id=1111838976 break?=0 p3=0 obj#=0 tim=10740422539
WAIT #2: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=10740422628
*** 2012-06-13 11:15:34.718
WAIT #2: nam='SQL*Net message from client' ela= 11554229 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=10751976953
CLOSE #2:c=0,e=57,dep=0,type=0,tim=10751977262
=====================
上面黑体部分报错。该部分是在session 1提交后，session 2报违反唯一性错误。
下面dump结果为session2处于等待状态的时候的索引块状态，这个时候也正是读完表和索引后，进程正处于等待状态。那么这个时候的索引块状态足以说明Oracle在插入重复数据到具有unique index索引的表中时是怎么处理的，是将锁加在索引还是表上。
索引块dump出结果分析：
Dump of First Level Bitmap Block
--------------------------------
nbits : 2 nranges: 1 parent dba: 0x01429849 poffset: 0
unformatted: 4 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at 06/12/2012 13:00:29
Last successful Search 06/12/2012 13:00:29
Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 9
Locker xid: : 0x0003.011.000003b0
Inc #: 0 Objd: 27772
HWM Flag: Not Set
Highwater:: 0x0142984c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01429848 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:25-50% free
4:unformatted 5:unformatted 6:unformatted 7:unformatted
--------------------------------------------------------
End dump data blocks tsn: 4 file#: 5 minblk 170056 maxblk 170056
从上面索引块dump出的结果看，在索引块上加了锁。
5. 测试结果分析
由以上测试过程总结：
当插入数据到有unique索引的表并且进程未提交，这时再有其他进程插入重复数据到该表时。 Oracle首先将数据写入到数据块，在维护索引叶子节点时发现已经有相同的数据在索引块中存在，则会在索引块上加锁并阻塞进程。
