[20130125]DML操作出現交集的情況.txt

lfree發表於2013-01-25
[20130125]DML操作出現交集的情況.txt

昨天同事問了一個問題,如果DML操作出現交叉的情況下,oracle是如何處理的?

我自己還是建立一個測試例子來說明:

1.建立測試環境:
SQL> select * from v$version where rownum<=1;

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 level<=3;
alter table t minimize records_per_block;
--這樣限制每個資料塊3條記錄

insert into t     select rownum id1 ,2 id2 ,'bbbb' name from dual connect by level<=3;
insert into t     select rownum id1 ,3 id2 ,'cccc' name from dual connect by level<=3;
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已經上鎖的行記錄。











來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-753269/,如需轉載,請註明出處,否則將追究法律責任。

相關文章