|
|
下面是一段Jonathan Lewis关于itl的表述,, 我针对着做了一个测试..
做了一个测试, Jonathan 并不需要知道内情就可以得到这个结论..
测试过程.
第一步, 准备相关数据..
create table t (id number,name varchar2(20)) initrans 2 maxtrans 2; insert into t values (1,'value 1'); insert into t values (2,'value 2'); insert into t values (3,'value 3'); commit; select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t; DBMS_ROWID.ROWID_BLOCK_NUMBER(
18406
18406
18406
确保3条记录在同一个block中..
第二步, 测试.
在session 9 执行,
update t set name = 'value 11' where id = 1;
在session 11 执行
update t set name = 'value 12' where id = 2;
在session 12执行
update t set name = 'value 13' where id = 3;
下面是session 12执行后的相关enqueue trace信息..
ela 是ms, 转化成秒就是3 + 2 = 5秒..
之后这个enqueue就会一直等待在session 11上, 哪怕我们将session 9提交之后, enqueue仍然没有结束,
直到我将session 11也提交之后, 才能观察到session 12不再等待enqueue
WAIT #1: nam='enqueue' ela= 3000344 p1=1415053316 p2=524296 p3=2174 WAIT #1: nam='enqueue' ela= 2000377 p1=1415053316 p2=524296 p3=2174 WAIT #1: nam='enqueue' ela= 3000528 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000498 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000528 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000498 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 2999893 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000529 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000176 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 2999692 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000567 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000354 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000072 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000285 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000092 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000391 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000151 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000502 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000399 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000494 p1=1415053316 p2=327696 p3=2264 WAIT #1: nam='enqueue' ela= 3000508 p1=1415053316 p2=327696 p3=2264
-----Original Message-----
From: jame.tongjw [mailto:jame.tongjw@alibaba-inc.com]
Sent: Wednesday, June 28, 2006 12:43 PM
To: dba@alibaba-inc.com
Subject: a short description of itl By Jonathan Lewis.
http://www.freelists.org/archives/oracle-l/02-2004/msg01473.html
Re: How do commits release row level locks?
* From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
* To: <oracle-l@xxxxxxxxxxxxx>
* Date: Mon, 16 Feb 2004 16:29:54 -0000
Session 1 grabs an ITL slot
Session 2 grabs an ITL slot
Session three see all slots full, so picks and ITL slot 'at random' and (in version 9) sits on the
transaction table entry (in the Undo segment header) for 5 seconds - if nothing happens in this
time, the transaction goes round each slot in turn, just once, waiting for 5 seconds on each, hoping
to find an empty slot. (This is v9 behaviour only, v8 would just stick on the first one).
After trying every slot for 5 seconds, the transaction sits on the either the first or last one it
tried (I can't remember which off-hand), and waits indefinitely for it to commit or rollback.
If one of the other transactions rolls back or commits, then an ITL slot is free, but the waiting
transaction does not see it. The waiting transaction sets a call-back to itself every three seconds
(possibly to allow an moment for a deadlock detection message to be delivered).
An even stranger effect than the one you've described - if the transaction that yours is waiting on
rolls back to a savepoint (leaving some data still changed) that frees the ITL in the block that is
causing the problem, your transaction will not see it, because it's no longer looking at the ITL
list, it's looking at the transaction table. So it will still wait.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html |
|
楼主: xyf_tck
IP卡
狗仔卡
发表于 2006-6-30 00:24
显身卡
