- 浏览: 239677 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
akka_li:
学习了!但是为什么后续的没有了?!
EJB4:RMI和RMI-IIOP -
springaop_springmvc:
apache lucene开源框架demo使用实例教程源代码下 ...
Lucene学习笔记(一)Lucene入门实例 -
qepipnu:
求solr 客户端 jar包
Solr学习笔记(三)Solr客户端开发实例 -
zhangbc:
是这问题,赞!
Oracle Start Up 2 Oracle 框架构件、启动、解决一个问题 -
feilian09:
查询 select hibernate jdbc 那个效率快
Hibernate,JDBC性能探讨
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>
发表评论
-
Oracle - Add Exist Validation Before Create Table
2011-11-07 13:49 1451Usually we need to check the ta ... -
Oracle - An Managing Lob examples
2011-10-30 17:28 1441I met a request: I need to read ... -
PL/SQL Studing Notes
2011-10-20 21:48 1380As an extension of native ... -
一组Linux命令
2011-09-29 13:09 1469今天做测试发现Oracle服务器磁盘使用率达到100%导致Or ... -
Thinking in JDBC
2011-09-22 20:56 1868This blog will beas on a series ... -
SQL Studying Note I - Join
2011-07-02 10:28 1237SQL Join SQL Join are u ... -
Windows批处理脚步实例-创建Oracle数据库用户并向该用户添加数据
2010-12-11 09:25 3440Windows批处理使用方便、 ... -
Oracle starting up 5: Oracle 10g在WINDOWS服务中有5个Oracle服务项及解决与服务项相关的几个问题
2010-10-10 17:25 2336在Windows下安装Oracle 10g,安装完后在WIND ... -
Oracle starting up 5: Oracle数据库基础(续)
2010-10-07 14:15 0通用函数; 通用函数用于任何类型数据(包括空值) ... -
Oracle starting up 4: Oracle 10g 客户端 enterprise manager console 消失问题
2010-10-06 15:20 4314在家装了台服务器,因为家里电脑不行,所以服务器和客 ... -
Oracle Start Up 3:Oracle数据库基础
2010-10-05 20:17 21991. 创建TableSpace、用户及给用户分派权限 ... -
Oracle Start Up 2 Oracle 框架构件、启动、解决一个问题
2010-10-02 14:07 34849Warming Up: 本文 ... -
Oracle Start Up 1: 几个概念和Oracle数据库的物理结构和逻辑结构
2010-09-29 23:31 2043Oracle 基本概念 数据库(Database) ... -
Oracle 连接错误;ORA-27101: shared memory realm does not exist
2010-09-28 14:27 26355XP下安装Oracle10g 昨天下午刚安装完可以连接(Sq ... -
SQL Server 2005 dev 学习(1)
2010-09-26 15:14 1984关键字:SqlServer2005Dev版本安装 SQL Se ... -
Cassandra Dev 3:Cassandra 应用之CassandraAppender
2010-08-13 13:59 2080本文的目的是展示 ... -
Cassandra Dev 2: Cassandra入门(续) - Cassandra Cluster
2010-08-09 13:56 57835. Cassandra CLI 一般数据库服务器都会提供一 ... -
Cassandra Dev 1: Cassandra 入门
2010-08-06 17:55 4607最近, Cassandra 绝对是一个比较前端的话题 ...
相关推荐
为了避免程序中断,可以使用`EXCEPTION WHEN NO_DATA_FOUND THEN`来捕获并处理这个异常。 2. **TOO_MANY_ROWS** (ORA-01422): 当`SELECT INTO`语句返回多于一行数据时,会出现此异常。在处理时,通常需要修改查询以...
### ORACLE SQL Reference 10g 知识点解析 #### 一、Oracle数据库SQL参考手册10g版本概述 《Oracle Database SQL Reference》10g版本是Oracle公司为Oracle数据库10g版本提供的官方SQL语言参考文档。该文档详细介绍...
### Oracle 存储过程 Exception 异常处理详解 #### 1. 异常处理概念 在 Oracle 存储过程中,异常处理是一项非常重要的技术,它能够帮助开发人员处理那些不可预见的情况,确保程序的健壮性和稳定性。异常处理主要...
### Oracle SQL Reference 知识点概述 #### 一、Oracle SQL Reference 概述 - **文档版本**: 该文档为Oracle Database SQL Language Reference 11g Release 2 (11.2)版本,出版日期为2012年12月。 - **版权信息**: ...
Oracle 10G SQL Reference是Oracle数据库系统的重要文档之一,主要涵盖了在Oracle 10G环境中执行SQL查询、数据操作和数据库管理的核心概念和技术。这个参考手册为数据库管理员(DBA)、开发人员以及数据分析师提供了...
Oracle SQL Reference命令参考文档是Oracle公司为数据库管理员和开发人员提供的权威指南,主要涵盖了SQL Plus中的各种命令和语法。这份超过1000页的文档是Oracle 10g版本的一部分,提供了对SQL语言在Oracle环境下的...
- **Oracle Service Technical Reference Manual**:这份文档提供了关于Oracle Service产品的技术参考信息。 #### 标签: - **EBS**:即Enterprise Business Suite(企业业务套件),表明此文档与Oracle EBS系列的...
### Oracle SQL Reference 知识点概述 #### 一、Oracle SQL 快速参考指南概览 - **版本信息**:本文档为Oracle Database SQL快速参考指南的10g Release 2(10.2)版,发布于2005年12月。 - **版权与许可**:此文档...
### 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 10g Release 2 (10.2) 知识点解析 #### 一、Oracle Database Reference 概述 - **版本信息**:本文档为Oracle Database 10g Release 2(10.2)的参考手册,发布日期为2005年6月。 -...
"Oracle Reference Err_Msg"主要涉及的是Oracle 10G版本的DBA参考及错误消息的处理。 "Ora 10G DBA Reference.chm"这个文档可能是Oracle 10G数据库管理员的官方参考手册,它包含了关于数据库安装、配置、性能优化、...
OracleDatabase SQL Reference OracleDatabase SQL Reference OracleDatabase SQL Reference
Oracle Database 11g SQL Language Reference 是一本权威的参考手册,为用户提供关于Oracle数据库11g版本(11.2版本)SQL语言的详细指导。本书是Primary Authors:Diana Lorentz和MaryBeth Roeser领导下,由多位贡献...
Oracle内置包是Oracle数据库系统提供的一系列预定义的PL/SQL包,这些包极大地扩展了数据库的功能,并简化了开发者在数据库层面的操作。Oracle内置包包括了处理数据、管理事务、控制用户访问、监控数据库性能等多个...
Oracle GoldenGate是甲骨文公司(Oracle)推出的一款用于数据集成和数据迁移的软件产品。Oracle GoldenGate支持跨平台的数据同步,常用于异构数据库环境,如从Windows到UNIX平台,或者从Oracle数据库到其他数据库...
### 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及其常用SQL查询和数据操作语句的快速参考指南。作者Jonathan Gennick通过本书帮助读者快速查找特定语言元素的语法。值得注意的是...
Oracle 10G DBA Reference 是一本针对Oracle数据库管理员(DBA)的重要参考资料,主要涵盖了在Oracle 10g版本中的管理和维护技术。Oracle 10g是Oracle公司的一个重大发布,提供了许多新特性和改进,以提升数据库的...