Hello Suresh, Here is what it says in the Developer Guide Page 38: Transactions when the Auto-Commit is Disabled When auto-commit is disabled, you can use a connection object's commit and rollback methods to commit or rollback a transaction. The commit method makes permanent the changes resulting from the transaction and releases locks. The rollback method undoes all the changes resulting from the transaction and releases locks. A transaction encompasses all the SQL statements executed against a single connection object since the last commit or rollback. I will also get you the information from ANSI. Regards Anil
________________________________ From: Anil Venkobarao Sent: Tue 1/25/2005 4:15 PM To: Suresh Thalamati (JIRA) Subject: RE: [jira] Commented: (DERBY-133) Autocommit turned false and rollbacks Hello Suresh, Thanks for the information. Here is what oracle does almost all other databases including HSQL does the same. The information under is from the oracle manuals. I will get to you more information on the requirements of ANSI SQL for the same. The problem is not Foriegn key enforcability but of transaction control and rollback. Consistency of data is determined by this. Regards Anil Transaction Control Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two UPDATE statements might credit one bank account and debit another. Simultaneously, Oracle makes permanent or undoes all database changes made by a transaction. If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction. Thus, the database is restored to its former state automatically. You use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands to control transactions. COMMIT makes permanent any database changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction. Used with ROLLBACK, SAVEPOINT undoes part of a transaction. SET TRANSACTION establishes a read-only transaction. ________________________________ From: Suresh Thalamati (JIRA) [mailto:[EMAIL PROTECTED] Sent: Tue 1/25/2005 1:34 PM To: Anil Venkobarao Subject: [jira] Commented: (DERBY-133) Autocommit turned false and rollbacks [ http://issues.apache.org/jira/browse/DERBY-133?page=comments#action_58066 ] Suresh Thalamati commented on DERBY-133: ---------------------------------------- I don't think whole transaction should be rolled back , if an insert fails with foreign key constraint violation only the work of that statement should be rolled back. Only time whole transaction should be rolled back is if constraints checking is deferred to commit time, in which case whole transaction might be rolled back. I don't have other databases installed on my machine to double check this behaviour, this what I found in SQL92 Standard doc: SQL92 Standard : When a constraint is effectively checked, if the constraint is not satisfied, then an exception condition is raised: integrity constraint violation. If this exception condition is raised as a result of executing a <commit statement>, then SQLSTATE is not set to integrity constraint violation, but is set to transaction rollback-integrity constraint violation (see the General Rules of Subclause 14.3, "<commit statement>"). -- > 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
