[ http://issues.apache.org/jira/browse/DERBY-133?page=comments#action_58076 ] Jeremy Boynes commented on DERBY-133: -------------------------------------
I'm not sure exactly what Anil's application is doing but looking at the Java code it is not creating a constraint on the table at all. If I tweak the code so that it does, then I get an exception thrown from the insert into salary with empid = 200 but the previous two inserts are present and get committed correctly. There is not threading involved - perhaps he is running two concurrent transactions and seeing some overlap. > 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.0.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 - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
