×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

I prepared a simple test case (from the URL you posted)

本文发表在 rolia.net 枫下论坛DROP TABLE CHILD;

DROP TABLE PARENT;

CREATE TABLE PARENT (COL1 NUMBER);

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);

CREATE TABLE CHILD (COL1 NUMBER);

CREATE INDEX CHILD_IX_01 ON CHILD (COL1);

ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;

INSERT INTO PARENT VALUES (1);

INSERT INTO CHILD VALUES (1);

COMMIT;
-----------------------
test 1:
txn 1:

delete parent where col1=1; -- I got an exception, because child data found
-----------------------
test2:

txn1:

delete child where col1=1;

delete parent where col1=1; -- everything is fine.

txn2:

insert child values (1); -- txn2 got stuck here, because txn1 is not finished and oracle doesn't know the parent key is really deleted or not

txn1:
commit; -- txn2 got an exception immediately, because parent key not found

-----------------------
test3:
-- the same as test2, except that we rollback txn1 instead of commit.
-- at this time, insert in txn2 suceeded immediately.

-----------------------
test4:

We create the foreign key on child as ON DELETE CASCADE, then:

txn1:
delete parent where col1=1; -- the child is also deleted.

txn2:
insert child values (1); -- txn2 got stuck here, the same as test2

txn1:
commit or rollback, the behavior is the same as test2 and test3
-----------------------
more tests:

I switched the order of txn1 and txn2: insert the child record in txn2 first, then try to delete parent in txn1. txn1 got stuck when txn2 is not completed.

------------------------
As far as I can see, the behavior is reasonable. But I don't know what happens inside. The bug is about test1: when ANY child record is found, oracle should throw an exception immediately. But what it really does is: it places a row level lock on the parent table, then more locks on the child table, while we expect it to stop at the first row of child table. The reason it's scanning all child records rather than the first record is unknown, but Oracle already bugged it as #5504961.更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / Oracle Developers, What tools do you use? PL/SQL Builder? PL/SQL Editer? or something else?
    • i am learning also.... still think SQL Server is the best DataBase
      • Well, it can't be running on UNIX :(
      • Well.....口的天真大啊。。。。
        • 失礼失礼,正在苦学Oracle,不过觉得PL/SQL确实是不错,支持数组
          • ORACLE的行级锁比其他DB领先,读取回滚段技术使得写操作不会阻塞读操作,也使得它的并发处理能力强出一截
            • 并发更新,一致性会有问题吧。
              • 不是很明白你的意思,你是说不应该有并发更新吗?
            • 不见得,至少现在Oracle不敢再吹自己是最快的数据库。
              • 硬件平台不一样。ORACLE的成绩是一年多前的。
                Server Information CPU Type: IBM POWER5+ 2.3GHz
                Total # of Processors: 32
                Total # of Cores: 64
                Total # of Threads: 128
                Cluster: N

                VS

                Server Information CPU Type: IBM POWER5 - 1.9 GHz
                Total # of Processors: 16
                Total # of Cores: 32
                Total # of Threads: 64
                Cluster: N
                • ORACLE 2005年的水平比DB2在2004年公布的的还差一半,做不到就是做不到,这和硬件,时间没关系。给你最新的硬件,软件上不去,也没办法.
                  • 来看看asktom上的说法
                    64p vs 32p September 07, 2005
                    Reviewer: Gabriel Paulovic from Toronto, Canada
                    How a single number makes the difference ...
                    IBM eServer p5 595 64p
                    IBM eServer p5 595 32p

                    .... besides, we run applications not TCP-C tests here ... :-)

                    Followup:
                    yes, I wonder how many people have read the tpc-c spec and it's somewhat
                    artificial constraints. It is an interesting 7 table or so benchmark (including
                    things like gap free sequence numbers!)

                    首先,32位和64位的差别是巨大的;其次,要求连续的ID对ORACLE是不公平的,这将会导致本来可并行操作的事务变成串行(实际应用中,ORACLE推荐用SEQUENCE产生ID,但无法保证其连续性)


                    it is interesting to download the full disclosures and see what they did in the
                    physical implementation as well.
                    • Tom是很牛,但他应该心里明白
                      Oracle本身无法控制硬件,许多优化做不了理想状态。

                      就比如现在pSeries的顶极机器p5 595,最大是64个CPU, 254个LPAR;如果只设置一个LPAR,如何利用64个CPU得看AIX and Hypervisor的调控。谁知道AIX在测试的时候是否采用SMT技术即是相当与128个CPU? 正因为IBM硬件或者操作系统上有许多关键点(或者隐藏技术)Oracle没有摸清楚,因此在p5 595+AIX平台上跑起来并不见的已经最优化。

                      相反看看DB2,在测试的时候肯定接近最优化,跑出来的成绩好是非常正常的。

                      Oracle在硬件上不占优势,所以只能往功能方面发展。看看最新版本的10G,许多新功能比如ASM,Flashback,增强功能的RMAN等,的确付出了不少努力。但我个人认为象ASM这种更象一种测试市场的反映,因为它的这种存储策略比起传统存储巨头EMC之流还是相距甚远。

                      再看企业合作方面,象新版本SAP已经不愿意和Oracle合作(因为Oracle变成了它的强大竞争对手)而采用DB2为主。

                      个人感觉Oracle今后的发展方向很难说。偶知道它很想抢ERP市场的头把交椅,但是否作的下去还不好说。如果有3个数据库Oracle/DB2/SQL Server给我选择投入精力的话,我估计会选DB2
                      • IBM使出它的独门暗器,ORACLE只有干瞪眼。按这位TOM的说法,目前ORACLE的优势还有:DB2自己在不同平台、版本之间不兼容;事务的读一致性实现得不好(Multi version concurrency control)
                      • 呵呵,刚才跑到asktom问了一贴,他说一个月后再来瞧瞧!看来oracle正在准备测试。他还建议看看历史数据(ORACLE都是后来居上?)
                    • 32位和64位的差别是巨大的??? 工业标准有偏向??? 所以ORACLE 比 DB2 跑得慢?
                      我看了半天,没看出来ORACLE是跑在32位版, 麻烦哪位指点一下. 这也说不过去, ORACLE不知道64位快, 还是要显摆32位跑得慢? 虽败犹荣?

                      工业标准又不是IBM标准,怎么没听微软抱怨?

                      都是借口. 超过DB2才是真本事.
                      • 点击TPC页面上的system栏可看到硬件参数。为什么不跑64版?可能当时还没开发出来吧。为什么微软不抱怨?大概它对自己的成绩已经很满意了。
                        ORACLE在对硬件调优这方面肯定要慢一拍。既然最近会有新结果,就让我们拭目以待。IBM的最新结果也只是实验室数据,System Availability不也要等到2006.12.20吗?
                        • 系统栏写的是处理器数, 内核数,线程数,微软的态度你也瞎猜.我对你这种忽悠的做法有点出离愤怒了!
                          2001年的AIX 5.1就有64位kernel, Oracle2005年有没有64位版, 你可要说清楚.
                          • 老兄看来是个热血青年,请息怒!我充其量不过一O记粉丝,偶尔放放厥词又怎能当真?
                            我对DB2可谓一无所知,不过在网上看到一说法还想请教:听说在一个SQL语句中,DB2不能保证扫描的第一行我最后一行都是同一时间点的数据?换句话说,若想计算账户总余额可能总是得不到正确结果,是真的吗?
                          • 是我看错了,ORCLE没说它是32位版,测试环境是32p即32 processors。报告是IBM提交的。32处理器和64处理器的差别难道不大?
                      • we are running oracle 10g on 64bits Itanium. the performance is definitely much better, but have problems with cluster. I am going to 11g testing with Oracle team, see if 11g will make a difference.
              • NYSE and most of the big banks on Wall.St still use Sybase for most of their mission-critical system, why? 1. fast, 2. light-weight and easy to scale 3. low price
                Oracle and DB2 are powerful, but it costs much more to manage. MS SQL Server, the kernel is bought from Sybase and I will never recommend any database system only running on Windows Server.
                • You forgot to mention that there are a lot of projects on Wall St. that are migrating from Sybase to other bigger vendors. Sybase's market share is shredding.
                  • 这确实是事实。sybase的市场和发展策略部门都很糟糕,市场份额一天不如一天。
                    但纯粹就performance/cost来说,我认为它是最好的。我现在公司应该算是sybase最大客户之一 (logic server > 15000),3年前曾决定migrate到db2,到现在这个计划无疾而终,因为几乎大部分项目组都反对,先不算把海量的isql stored-procedure移植到pl/isql的巨大投入和风险,换平台对大部分application根本没有任何performance gain, license和管理的费用反而高不少.( oracle 更贵,不给我们site license)
            • 这个算不算ORACLE的行级锁的劣势?
              • very interesting. I never notice that before. Anyway Oracle is aware of this behavior and it will be fixed someday.
                • 能不能介绍一下write consistency in Oracle?
                  How does it work if 1st transaction is deleteing parent table in a referential constraint while the 2nd transaction is inserting into a child table when the RI is defined as on delete cascade or on delete no action?
                  • I prepared a simple test case (from the URL you posted)
                    本文发表在 rolia.net 枫下论坛DROP TABLE CHILD;

                    DROP TABLE PARENT;

                    CREATE TABLE PARENT (COL1 NUMBER);

                    ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);

                    CREATE TABLE CHILD (COL1 NUMBER);

                    CREATE INDEX CHILD_IX_01 ON CHILD (COL1);

                    ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;

                    INSERT INTO PARENT VALUES (1);

                    INSERT INTO CHILD VALUES (1);

                    COMMIT;
                    -----------------------
                    test 1:
                    txn 1:

                    delete parent where col1=1; -- I got an exception, because child data found
                    -----------------------
                    test2:

                    txn1:

                    delete child where col1=1;

                    delete parent where col1=1; -- everything is fine.

                    txn2:

                    insert child values (1); -- txn2 got stuck here, because txn1 is not finished and oracle doesn't know the parent key is really deleted or not

                    txn1:
                    commit; -- txn2 got an exception immediately, because parent key not found

                    -----------------------
                    test3:
                    -- the same as test2, except that we rollback txn1 instead of commit.
                    -- at this time, insert in txn2 suceeded immediately.

                    -----------------------
                    test4:

                    We create the foreign key on child as ON DELETE CASCADE, then:

                    txn1:
                    delete parent where col1=1; -- the child is also deleted.

                    txn2:
                    insert child values (1); -- txn2 got stuck here, the same as test2

                    txn1:
                    commit or rollback, the behavior is the same as test2 and test3
                    -----------------------
                    more tests:

                    I switched the order of txn1 and txn2: insert the child record in txn2 first, then try to delete parent in txn1. txn1 got stuck when txn2 is not completed.

                    ------------------------
                    As far as I can see, the behavior is reasonable. But I don't know what happens inside. The bug is about test1: when ANY child record is found, oracle should throw an exception immediately. But what it really does is: it places a row level lock on the parent table, then more locks on the child table, while we expect it to stop at the first row of child table. The reason it's scanning all child records rather than the first record is unknown, but Oracle already bugged it as #5504961.更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • 呵呵,MS的东西还是算了吧...我们公司现在最流行的一句话就是 Microsoft sucks....
    • PLSQL developer
      • Any where can download for free? Thanks
        • It is just a tool. Different company uses a different tool. There are too many similar tools outside. If you try to learn Oracle PL/SQL, you just need SQL*Plus.
          • 我还只用UNIX下的sqlplus,我的同事惊呆了。我说,我不需要换手用鼠标
    • TOAD
      • thanks.. i am using toad, so cool a software for Oracle
      • TOAD是什么?
    • 大家有什么好的PL/SQL编辑工具,介绍一下,Oracle自己带的太难用了
      • 俺以前一直用ULTRAEDIT。。。
      • ultraedit不错,支持多种语言高亮显示。也可以用Quest的产品。
      • ORACLE的免费工具SQL DEVELOPER 很好啊, WHY 太难用了?
    • SQL*PLUS + Notepad
      • SQL*PLUS + vi
    • on windows, toad, dbartisan. on unix, vi, sqsh.
    • ULTRAEDIT is cool for its column mode editing. I use TOAD to find compiler errors, and PL/SQL developer to test a returning cursor from a stored procedure
      • 我喜欢TOAD的另一好处是可以把查询结果存成丰富的格式,包括文本,INSERT SCRIPT, EXCEL等。ORACLE自己开发的免费工具SQL DEVELOPER(RAPTOR)看起来也不错