[ http://issues.apache.org/jira/browse/DERBY-133?page=all ]
Mike Matrigali closed DERBY-133:
--------------------------------
Resolution: Cannot Reproduce
We never could get enough info to reproduce the problem.
> Autocommit turned false and rollbacks
> -------------------------------------
>
> Key: DERBY-133
> URL: http://issues.apache.org/jira/browse/DERBY-133
> Project: Derby
> Type: Improvement
> Components: Store
> Versions: 10.1.1.0
> Environment: Windows XP Environment
> Reporter: Anil Rao
>
> I have two tables Employee and Salary. Salary is a child of Employee table
> with a foriegn key with 1 to many relationship between the two tables.
> I have in my java file connection to the database, with set Autocommit being
> false. When I have two connection threads inserting on to employee and salary
> tables. I made a an insert into salary table and then to employee table, with
> that employee not in the employee, the insert went through fine.
> Then I made an insert into employee table and it went fine.
> In the next set of transaction I had the salary table insert going through
> fine and then the employee insert did not go through, and the transaction on
> the employee insert was rolled back but not the salary table insert.
> Can anyone please help me whether any setting I need to do to make this work
> correctly.
> Example of the java code and tables script is as follows.
> Script to create tables.
> Create Employee and Salary tables in any derby database.
> Script is as below.
> CREATE TABLE employee( empid INTEGER NOT NULL,
> full_name VARCHAR(30) NOT NULL,
> salary DECIMAL(10,2) NOT NULL );
> CREATE TABLE salary(
> empid INTEGER NOT NULL,
> pay_date DATE NOT NULL);
> alter table employee add CONSTRAINT emp_pk PRIMARY KEY (empid)
> ALTER TABLE salary ADD CONSTRAINT salary_fk1
> FOREIGN KEY (empid)
> REFERENCES employee(empid)
> ;
> -- Java Code for inserts.
> import java.sql.Connection;
> /*
> * Embedded Connection.
> */
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.util.Properties;
> public class EmConst
> {
> /* the default framework is embedded*/
> public String framework = "embedded";
> public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
> public String protocol = "jdbc:derby:";
> public static void main(String[] args)
> {
> new EmConst().go(args);
> }
> void go(String[] args)
> {
> /* parse the arguments to determine which framework is desired*/
> parseArguments(args);
> System.out.println("SimpleApp starting in " + framework + " mode.");
> try
> {
> /*
> The driver is installed by loading its class.
> In an embedded environment, this will start up Derby, since it
> is not already running.
> */
> Class.forName(driver).newInstance();
> System.out.println("Loaded the appropriate driver.");
> Connection conn = null;
> Properties props = new Properties();
> props.put("user", "");
> props.put("password", "");
> /*
> The connection specifies create=true to cause
> the database to be created. To remove the database,
> remove the directory derbyDB and its contents.
> The directory derbyDB will be created under
> the directory that the system property
> derby.system.home points to, or the current
> directory if derby.system.home is not set.
> */
> conn = DriverManager.getConnection(protocol +
> "Emp;create=true", props);
> System.out.println("Connected to and created database derbyDB");
> conn.setAutoCommit(false);
> /*
> Creating a statement lets us issue commands against
> the connection.
> */
> Statement s = conn.createStatement();
> /*
> We create a table, add a few rows, and update one.
> */
> s.execute("create TABLE employee(empid INTEGER NOT NULL,full_name
> VARCHAR(30) NOT NULL,salary DECIMAL(10,2) NOT NULL )");
> System.out.println("Created table Employee");
> s.execute("create TABLE salary(empid INTEGER NOT NULL,pay_date
> DATE NOT NULL)");
> System.out.println("Created table Salary");
> s.execute("insert into employee values (100,'John',100)");
> System.out.println("Inserted John Record");
> s.execute("insert into salary values (100,'01/01/2003')");
> System.out.println("Inserted John Salary");
> s.execute("insert into salary values (200,'01/01/2003')");
> System.out.println("Inserted Pat Salary");
> s.execute("insert into employee values (200,'Patt','200')");
> System.out.println("Inserted Pat Record");
> s.execute("select count(*) from salary");
> System.out.println("Count of salary");
> s.execute("select count(*) from employee");
> System.out.println("Count of employee");
> /*
> We end the transaction and the connection.
> */
> conn.commit();
> conn.close();
> System.out.println("Committed transaction and closed connection");
> /*
> In embedded mode, an application should shut down Derby.
> If the application fails to shut down Derby explicitly,
> the Derby does not perform a checkpoint when the JVM shuts
> down, which means
> that the next connection will be slower.
> Explicitly shutting down Derby with the URL is preferred.
> This style of shutdown will always throw an "exception".
> */
> boolean gotSQLExc = false;
> if (framework.equals("embedded"))
> {
> try
> {
> DriverManager.getConnection("jdbc:derby:;shutdown=true");
> }
> catch (SQLException se)
> {
> gotSQLExc = true;
> }
> if (!gotSQLExc)
> {
> System.out.println("Database did not shut down normally");
> }
> else
> {
> System.out.println("Database shut down normally");
> }
> }
> }
> catch (Throwable e)
> {
> System.out.println("exception thrown:");
> if (e instanceof SQLException)
> {
> printSQLError((SQLException) e);
> }
> else
> {
> e.printStackTrace();
> }
> }
> System.out.println("SimpleApp finished");
> }
> static void printSQLError(SQLException e)
> {
> while (e != null)
> {
> System.out.println(e.toString());
> e = e.getNextException();
> }
> }
> private void parseArguments(String[] args)
> {
> int length = args.length;
> for (int index = 0; index < length; index++)
> {
> if (args[index].equalsIgnoreCase("jccjdbcclient"))
> {
> framework = "jccjdbc";
> driver = "com.ibm.db2.jcc.DB2Driver";
> protocol = "jdbc:derby:net://localhost:1527/";
> }
> }
> }
> }
> -- Comments
> We have oracle, sqlserver and mysql handle the rollbacks using a seperate
> rollback mechanism say rollback segment in oracle, temp in sqlserver and
> mysql also has some of these functionality. Yes we can handle the exception
> in the application but the problem is in the example we have two tables one
> parent one child, and it is inserting into child without the parent, causing
> inconsistency in database. The Database must take care of this. Derby needs
> to handle this type of issue. The way this needs to be handled is that all
> transactions must be logged in that are not yet committed into a rollback
> file. This can be removed on commit. If there is any failover then the entire
> set of transactions are rolled back, and the file contains the
> transaction.that can be applied after making the right modifications and
> committed.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira