db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: [jira] Commented: (DERBY-133) Autocommit turned false and rollbacks
Date Wed, 26 Jan 2005 18:01:04 GMT
Hi, Anil,

Sorry if this turns out to be duplicate reply. Somehow email receipt 
this morning seems delayed on my end.

Could there be a typo in your script and whatever runs the script 
doesn't catch the error?  I cut and pasted your script, then tried to 
insert a row into the salary table -- and that insert succeeded, but 
should not have. Here's what I did:

ij> 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)
;
0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> ERROR 42X01: Syntax error: Encountered "ALTER" at line 2, column 1.
ij> insert into salary values (1, CURRENT_DATE);
1 row inserted/updated/deleted
ij> select * from salary;
EMPID      |PAY_DATE
----------------------
1          |2005-01-26

1 row selected

The first constraint create failed, so I was able to insert a value into 
the salary table.

If I add a statement terminator to the end of the first constraint 
create, things work as I expect:

ij> 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)
;
0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted

ij> insert into salary values (1, CURRENT_DATE);
ERROR 23503: INSERT on table 'SALARY' caused a violation of foreign key 
constraint 'SALARY_FK1' for key (1).  The statement has been rolled back.

regards,

  -jean


Anil Venkobarao wrote:

>Hello Jeremy,
> 
>The constraint is already there.
> 
>ALTER TABLE salary ADD CONSTRAINT salary_fk1 
>FOREIGN KEY (empid) 
>REFERENCES employee(empid) 
>
> 
>Regards
>Anil
>
>
>________________________________
>
>From: Jeremy Boynes (JIRA) [mailto:derby-dev@db.apache.org]
>Sent: Wed 1/26/2005 12:33 AM
>To: Anil Venkobarao
>Subject: [jira] Commented: (DERBY-133) Autocommit turned false and rollbacks
>
>
>
>     [ 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
>
>
>
>  
>


Mime
View raw message