initrans设置对事务的影响

本实验仅仅针对表上的initrans和maxtrans参数的设置过小，导致阻塞事务的一个证明.
关于initrans和maxtrans参数的详细用法，请参阅相关文档

一个简单的测试
SQL> select rowid,a.* from toms a where depno<5;


ROWID DEPNO EMPNO
------------------ --------------------------------------- ---------------------------------------
AAAIQjAALAAAIqTAAA 1 100
AAAIQjAALAAAIqTAAB 2 200
AAAIQjAALAAAIqTAAC 3 300
AAAIQjAALAAAIqTAAD 4 400


SQL>


第一个session


SQL> desc toms;
名称 是否为空? 类型
----------------------------------------- -------- --------------
DEPNO NUMBER(38)
EMPNO NUMBER(38)


SQL> alter table toms initrans 1 maxtrans 1;


表已更改。


SQL> delete from toms where depno=1;


已删除 1 行。


SQL>


不提交


第二个session
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.


C:Documents and Settingsadmin>sqlplus study/study


SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 4月 18 10:47:56 2006


Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.




连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


SQL> update toms set depno=0 where depno=2;


已更新 1 行。


SQL>


第三个session


Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.


C:Documents and Settingsadmin>sqlplus /nolog


SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 4月 18 11:11:12 2006


Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


SQL> conn study/study
已连接。
SQL> delete from toms where depno=3;


这时该事务被阻塞了。


---


Oracle的某些文档和实际情况也不一定相同啊。实践是检验真理的唯一标准


The documentation says that INITRANS and MAXTRANS can be specified ranging from 1-255 (default 1). Since Oracle9i, however, this is no longer true.


The Oracle9i limits for INITRANS and MAXTRANS are between 2 and 255.
Even if you explicitly create the table with INITRANS 1 and the data dictionary says INITRANS 1, in reality the table has two (2) ITL slots.
This can be proven easily with simple tests.
Identifying ITL Waits One has to ask the question: Which tables or indexes have an INITRANS specification that is too small, so that this led to ITL waits (or some fool (sorry) set MAXTRANS to 1 or 2)




在10g以后的版本中，MAXTRANS parameter has been deprecated.Oracle db now auto allows up to 255 concurrent update transactions of any data block, depending on the available space on the block.