[20130125]DML操作出现交集的情况.txt
昨天同事问了一个问题，如果DML操作出现交叉的情况下，oracle是如何处理的？
我自己还是建立一个测试例子来说明:
1.建立测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
CREATE TABLESPACE aaa DATAFILE
'/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
--建立表空间mssm主要目的是演示可以重复，保持插入的数据顺序与显示的信息一致。
create table t tablespace aaa as select rownum id1 ,1 id2 ,'aaaa' name from dual connect by levelalter table t minimize records_per_block;
--这样限制每个数据块3条记录
insert into t select rownum id1 ,2 id2 ,'bbbb' name from dual connect by level
insert into t select rownum id1 ,3 id2 ,'cccc' name from dual connect by level
commit ;
SQL1> select rowid,a.* from t a;
ROWID ID1 ID2 NAME
------------------ ---------- ---------- --------------------------------------------------
AABBqUAALAAAACBAAA 1 1 aaaa
AABBqUAALAAAACBAAB 2 1 aaaa
AABBqUAALAAAACBAAC 3 1 aaaa
AABBqUAALAAAACCAAA 1 2 bbbb
AABBqUAALAAAACCAAB 2 2 bbbb
AABBqUAALAAAACCAAC 3 2 bbbb
AABBqUAALAAAACDAAA 1 3 cccc
AABBqUAALAAAACDAAB 2 3 cccc
AABBqUAALAAAACDAAC 3 3 cccc
9 rows selected.
--注意我表空间是mssm,显示的数据与插入的数据一致。
--从rowid也可以看出数据分布在3个块中。
2.开始测试:
--打开回话1,修改数据不提交：
SQL1> update t set name='3333' where id2=3;
SQL1> select dbms_transaction.local_transaction_id z10 from dual;
Z10
----------
7.31.9750
--打开回话2,修改数据不提交：
SQL2> update t set name='1111' where id1=1;
--这样第2个回话也会修改了id1=1,id2=3的行记录，出现阻塞，回话挂起。
--打开回话3执行如下：
SQL3> select addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec,status, start_time, start_scnb, start_scnw, ses_addr from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW SES_ADDR
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ----------------
00000000B5633B00 6 11 10614 3 211 5109 36 ACTIVE 01/25/13 08:45:31 3221927474 0 00000000BF192EF8
00000000B75BADE8 7 31 9750 3 3582 5076 16 ACTIVE 01/25/13 08:45:05 3221927242 0 00000000B51B7E98
--从显示可以看出，第2行信息与回话1的信息相符合。第1行自然与第2个回话有关。
--这个时候如果在第3个回话执行update t set name='xxxx' where id1=1 and id2=1;可行吗？会挂起吗？
SQL3> update t set name='xxxx' where id1=1 and id2=1;
--可以发现一样会挂起!
3.全部rollback，再做另外的测试:
--回话1执行如下,不提交：
SQL1> update t set name='1111' where id2=1;
SQL1> select dbms_transaction.local_transaction_id z10 from dual;
Z10
----------
9.20.10218
--回话2执行如下,修改数据不提交：
SQL2> update t set name='XXXX' where id1=1;
--这样第2个回话也会修改了id1=1,id2=1的行记录，出现阻塞，回话挂起。
--回话3执行如下：
SQL3> select addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec,status, start_time, start_scnb, start_scnw, ses_addr from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW SES_ADDR
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ----------------
00000000B75BADE8 9 20 10218 3 3099 5252 6 ACTIVE 01/25/13 09:02:37 3221927691 0 00000000B51B7E98
--从显示可以看出仅仅1行，信息与回话1的信息相符合.而没有第2行信息，
--这个时候如果在第3个回话执行update t set name='xxxx' where id1=1 and id2=3;可行吗？会挂起吗？从视图v$transaction一定不
--会出现阻塞。
SQL3> update t set name='yyyy' where id1=1 and id2=3;
1 row updated.
--确实可以！可以发现可以执行，并没有挂起。这次全部提交。
--提交按照如下顺序 回话3=>回话1=>回话2.
SQL1> select rowid,a.* from t a;
ROWID ID1 ID2 NAME
------------------ ---------- ---------- -------
AABBqUAALAAAACBAAA 1 1 XXXX
AABBqUAALAAAACBAAB 2 1 1111
AABBqUAALAAAACBAAC 3 1 1111
AABBqUAALAAAACCAAA 1 2 XXXX
AABBqUAALAAAACCAAB 2 2 bbbb
AABBqUAALAAAACCAAC 3 2 bbbb
AABBqUAALAAAACDAAA 1 3 XXXX
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AABBqUAALAAAACDAAB 2 3 cccc
AABBqUAALAAAACDAAC 3 3 cccc
9 rows selected.
--注意~线显示的信息：id1=1 id2=3的name='XXXX',而不是name='yyyy',
--这个也非常像我们应用系统中以前遇到的一个bug，一旦应用中出现阻塞，整个业务流程出现一些奇怪的问题。
总结：
1.可以说明oracle仅仅进入块中才知道行记录是否存在锁。
2.oracle遇到行记录没上锁，DML执行后，再上锁。遇到阻塞，回话挂起，等待解除。这样别的回话不能DML已经上锁的行记录。