`
kylinsoong
  • 浏览: 239677 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle Reference Exception Gallery

阅读更多

The Following Exception is really Frequency When we Using Oracle.

1. ORA-02292: integrity constraint (IPCUSER.STUDENT_STUDENTCARD_FK) violated - child record found

      If you delete a row from a table, and one of this row's columns referenced by another table, and then this Exception been throwed.

the stacktrace info:

java.sql.SQLException: ORA-02292: integrity constraint (IPCUSER.STUDENT_STUDENTCARD_FK) violated - child record found
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:961)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
	at com.tibco.ipc.utils.TESTClient.main(TESTClient.java:48)

 2. ORA-02291: integrity constraint (IPCUSER.STUDENT_STUDENTCARD_FK) violated - parent key not found

      If you insert a row to a table(table_a), and this row has a foreign key refered to another table(table_b), and the foreign key's value can not be found from another table(table_b), then this Exception been thowed.

the stacktrace info:

java.sql.SQLException: ORA-02291: integrity constraint (IPCUSER.STUDENT_STUDENTCARD_FK) violated - parent key not found
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:961)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
	at com.tibco.ipc.utils.TESTClient.main(TESTClient.java:48)

 3. ORA-00001: unique constraint (IPCUSER.STUDENTCARD_HID_PK) violated

      If you insert a row to a table, one of this row's column is primary key, and if the primary key you want to insert already existed in table, this Exception been throwed.

The StackTrace info:

java.sql.SQLException: ORA-00001: unique constraint (IPCUSER.STUDENTCARD_HID_PK) violated
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:961)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
	at com.tibco.ipc.utils.TESTClient.main(TESTClient.java:46)

 4.ORA-01591: lock held by in-doubt distributed transaction 8.12.9742

      The stacktrace information:

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)

 Refering to this Exception, We should learn this fllowing issues firstly, before we discussing this point:

1> Understanding 'Oracle two-phase commit mechanism':

      Two-phase commit is a mechanism that guarantees a distributed transaction either commits on all involved nodes or rollback on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase. The Objective of the two-phase commit mechanism is that that all nodes either commit or perform a rollback together, to maintain the data's integrity and consistency.

2> Oracle distribution Transaction

To make this issue more explicity and clearly we providing the following experiments:

 As figure depicted, 2 JDBC Connection in different JVM connected to the same databse, and update the same table's same row. Assuming the table name is 'HOMETEST', the row is 'Row 1',  'HOMETEST' is very simple, the create SQL statements:

CREATE TABLE HOMETEST(
HID NUMBER(19,0) CONSTRAINT HOMETEST_HID_NN NOT NULL,
NAME VARCHAR2(255),
CONSTRAINT HOMETEST_HID_PK PRIMARY KEY(HID)
)

 Two JVM update table's procedure is very samilary, like the following figure depicted:

 continuely assuming: JVM I and JVM II update row 1 cosistantly and simultaneously, JVM I,eachtime change row 1's column 'NAME''s value add 1 and without stopping; Oppositely; JVM II, eachtime reset row 1's column 'NAME''s value, and exist without commit in the 10th time. reserence Java Code:

JVM I:

int i = 1;
Connection conn = getConnection();
Statement s = null;
while(true) {
	String upSql = "UPDATE HOMETEST SET HOMETEST.NAME = 'kylinsoong-" + i + "' WHERE HOMETEST.HID = 1";
	try {
		conn.setAutoCommit(false);
		s = conn.createStatement();
		s.executeUpdate(upSql);
		int sleep = new Random().nextInt(100);
		System.out.println("Update Thread Sleep " + sleep + " milliseconds");
		Thread.sleep(sleep);
		i ++ ;
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		close(null, s, null);
}

   JVM II Sides:

int index = 1;
while(true) {
         try {
	conn.setAutoCommit(false);
	s = conn.createStatement();
	s.executeUpdate(rmSql);
	s.executeUpdate(adSql);
	int sleep = new Random().nextInt(100);
	System.out.println("Insert Thread Sleep " + sleep + " milliseconds -> " + index);
	Thread.sleep(sleep);
	if(index ++ == 10) {
	Thread.sleep(60000);
	break;
	}
	conn.commit();
	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	close(null, s, null);
	}
}

 The Experment Results:

When JVM II executed the 10th times update, sleeping 1 minute, the JVM I also in stucked state;

The finally value in row 1 Name column is always kylinsoong-10, even after JVM I stopped. That's because JVM I and JVM II update row 1 are totally involved, JVM II could not update row 1 if JVM I transacted row 1.

The Conclusion: The Oracle distribution Transaction is not real distributed, All involved Node always in a order list, execute order list base on FIFO, in other words,  Oracle distribution Transaction just like run multiple-thread application in single process mechine.

 3>

  • 大小: 7.5 KB
  • 大小: 3.2 KB
0
2
分享到:
评论

相关推荐

    Oracle Exception汇总(自定义Oracle异常)

    为了避免程序中断,可以使用`EXCEPTION WHEN NO_DATA_FOUND THEN`来捕获并处理这个异常。 2. **TOO_MANY_ROWS** (ORA-01422): 当`SELECT INTO`语句返回多于一行数据时,会出现此异常。在处理时,通常需要修改查询以...

    ORACLE SQL Reference 10g

    ### ORACLE SQL Reference 10g 知识点解析 #### 一、Oracle数据库SQL参考手册10g版本概述 《Oracle Database SQL Reference》10g版本是Oracle公司为Oracle数据库10g版本提供的官方SQL语言参考文档。该文档详细介绍...

    Oracle_存储过程exception异常处理大全及实例经典最终.docx

    ### Oracle 存储过程 Exception 异常处理详解 #### 1. 异常处理概念 在 Oracle 存储过程中,异常处理是一项非常重要的技术,它能够帮助开发人员处理那些不可预见的情况,确保程序的健壮性和稳定性。异常处理主要...

    Oracle SQL Reference

    ### Oracle SQL Reference 知识点概述 #### 一、Oracle SQL Reference 概述 - **文档版本**: 该文档为Oracle Database SQL Language Reference 11g Release 2 (11.2)版本,出版日期为2012年12月。 - **版权信息**: ...

    Oracle 10G 参考手册 Oracle SQL Reference pdf

    Oracle 10G SQL Reference是Oracle数据库系统的重要文档之一,主要涵盖了在Oracle 10G环境中执行SQL查询、数据操作和数据库管理的核心概念和技术。这个参考手册为数据库管理员(DBA)、开发人员以及数据分析师提供了...

    Oracle SQL Reference命令参考文档

    Oracle SQL Reference命令参考文档是Oracle公司为数据库管理员和开发人员提供的权威指南,主要涵盖了SQL Plus中的各种命令和语法。这份超过1000页的文档是Oracle 10g版本的一部分,提供了对SQL语言在Oracle环境下的...

    Oracle Service Technical Reference Manual

    - **Oracle Service Technical Reference Manual**:这份文档提供了关于Oracle Service产品的技术参考信息。 #### 标签: - **EBS**:即Enterprise Business Suite(企业业务套件),表明此文档与Oracle EBS系列的...

    Oracle_SQL_Reference

    ### Oracle SQL Reference 知识点概述 #### 一、Oracle SQL 快速参考指南概览 - **版本信息**:本文档为Oracle Database SQL快速参考指南的10g Release 2(10.2)版,发布于2005年12月。 - **版权与许可**:此文档...

    Oracle Database Reference 10.2.pdf

    ### Oracle Database Reference 10g Release 2 (10.2) 知识点解析 #### 一、Oracle Database 10g Release 2 (10.2) 概览 Oracle Database 10g Release 2 (10.2) 是由 Oracle 公司发布的一款数据库管理系统,它在...

    Oracle Database Reference

    ### Oracle Database Reference 10g Release 2 (10.2) 知识点解析 #### 一、Oracle Database Reference 概述 - **版本信息**:本文档为Oracle Database 10g Release 2(10.2)的参考手册,发布日期为2005年6月。 -...

    Oracle Reference Err_Msg

    "Oracle Reference Err_Msg"主要涉及的是Oracle 10G版本的DBA参考及错误消息的处理。 "Ora 10G DBA Reference.chm"这个文档可能是Oracle 10G数据库管理员的官方参考手册,它包含了关于数据库安装、配置、性能优化、...

    Oracle Database SQL Reference

    OracleDatabase SQL Reference OracleDatabase SQL Reference OracleDatabase SQL Reference

    ORACLE11G SQL Language Reference

    Oracle Database 11g SQL Language Reference 是一本权威的参考手册,为用户提供关于Oracle数据库11g版本(11.2版本)SQL语言的详细指导。本书是Primary Authors:Diana Lorentz和MaryBeth Roeser领导下,由多位贡献...

    Oracle内置包_reference

    Oracle内置包是Oracle数据库系统提供的一系列预定义的PL/SQL包,这些包极大地扩展了数据库的功能,并简化了开发者在数据库层面的操作。Oracle内置包包括了处理数据、管理事务、控制用户访问、监控数据库性能等多个...

    《Reference for Oracle GoldenGate for Windows and UNIX》-官方版

    Oracle GoldenGate是甲骨文公司(Oracle)推出的一款用于数据集成和数据迁移的软件产品。Oracle GoldenGate支持跨平台的数据同步,常用于异构数据库环境,如从Windows到UNIX平台,或者从Oracle数据库到其他数据库...

    Oracle8i SQL Reference

    ### Oracle 8i SQL Reference 知识点概览 #### 一、Oracle 8i SQL Reference 概述 - **版本信息**:本文档为Oracle 8i SQL Reference的第3版(8.1.7),发布于2000年9月。 - **版权信息**:版权所有 © 1996-2000 ...

    Oracle SQL*Plus Pocket Reference, 2nd Edition

    本书《Oracle SQL*Plus Pocket Reference, 2nd Edition》是一部针对Oracle SQL*Plus及其常用SQL查询和数据操作语句的快速参考指南。作者Jonathan Gennick通过本书帮助读者快速查找特定语言元素的语法。值得注意的是...

    Oracle 10G DBA Reference.chm

    Oracle 10G DBA Reference 是一本针对Oracle数据库管理员(DBA)的重要参考资料,主要涵盖了在Oracle 10g版本中的管理和维护技术。Oracle 10g是Oracle公司的一个重大发布,提供了许多新特性和改进,以提升数据库的...

Global site tag (gtag.js) - Google Analytics