Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

transactions in jsp

I have a jsp page which contains a number of sql statements say 2 inserts,
1 delete and 1 update. They are a part of one transaction. So I want either none is executed or all of them r executed. How do I make sure that this happens. Do i need to do something in the jsp code to ensure or will my database(mysql) handle this.
pz suggest
0
will_789
Asked:
will_789
  • 7
  • 4
  • 2
2 Solutions
 
TimYatesCommented:
<%
   connection.setAutoCommit( false ) ;
   try
   {
     // do an update

     // do an insert
 
     // do a delete

     connection.commit() ;
   }
   catch( Exception ex )
   {
      ex.printStackTrace() ;
      connection.rollback() ;
   }
%>

Assuming your database allows commit and rollback :-)
0
 
JNicCommented:
You should use InnoDB tables which is supported by mySQL from version 3.23.34a.

Read more about them here: http://www.mysql.com/doc/en/InnoDB.html

Then you will be able to do things like the following:

// assuming you have a connection
 connection.setAutoCommit(false);
 connection.setTransactionIsolation(4);
 statement = connection.createStatement();
  for (int i=0; i<10; i++){
      String update="Insert into numbers (number) values (i)";
      statement.executeUpdate(update);
   }
   boolean doIt=false;
   // Then do some testing to see if you want the changes or not.
  if (doIt){
      statement.commit(); // this executes all the updates
  }
  else{
     statement.rollback(); // this eleminates all the updates
  }
}

If my memory serves me right, a SQLException will do a rollback automatically. - Other Exceptions will not.

Hope it helped!

Regards,

Nic


 

0
 
JNicCommented:
Oh - I'm too slow ;-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TimYatesCommented:
Hehehe ;-)

Not sure if he's using MySQL either...

Databases like Postgres or Oracle can rollback anyway (no special table type needed)

And you have to watch out if you are using something like MS Access, as turning auto-commit off will have a HUGE performance hit

(as it will lock an entire db page till you commit or rollback, not a single row as with the others)

Tim.
0
 
JNicCommented:
Quote from TimYates: Not sure if he's using MySQL either...
Quote from question: "my database(mysql)"

MUAHAHAHAHAHAHAHAAAAA!!!!
;-)
0
 
TimYatesCommented:
Whoops! >_<

hehehehehehehe ;-)
0
 
will_789Author Commented:
Could u plz tell me what's the purpose of

connection.setTransactionIsolation(4);
0
 
TimYatesCommented:
I think the 4 is:

Connection.TRANSACTION_SERIALIZABLE

Which means:

--------

A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented. This level includes the prohibitions in TRANSACTION_REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.
0
 
TimYatesCommented:
Whoops!

Connection.TRANSACTION_REPEATABLE_READ == 4 ;

A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur. This level prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read").
0
 
TimYatesCommented:
it means that other people reading the database will not see different data until the transaction has been committed
0
 
JNicCommented:
Oops I actually did not mean to write that line:

connection.setTransactionIsolation(4);

like that.

This method has four different settings:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

Read more about what the settings mean here:

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html

Look under FIELD DETAIL
0
 
will_789Author Commented:
thanks a lot
0
 
TimYatesCommented:

TRANSACTION_READ_COMMITTED

or

TRANSACTION_REPEATABLE_READ

Are usually the best ones to use...  It's a matter of personal choice, and whether you want repeatable reads...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now