?
Solved

Commit, rollback --- part II

Posted on 2003-03-12
11
Medium Priority
?
743 Views
Last Modified: 2010-04-01
I am posting this message after I go through this question posted below.

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20538874.html

Well, i think chainreaction finally statisfy with the help offered by Object and ken. But I have one big and serious question on this. Hope somebody can answer me.

According to the q, chain is asking the proper coding for him to  handle this commit() and rollback() issues on AS400..... via WEB-BASED system.

If he is previously running on RPG legacy system, no doubt he can test the commit() and rollback() issues. But how he is going to test either this commint() and rollback() is functioning or not?

What are we talking about about is Web- based application. Can this web-base program commit and rollback? I am doubt. Once user press the submit button, how will him(chainreaction) go to test his program either is it working or not??

He is only commiting or rollback if there is a PROGRAMMING ERROR. And this is not the proper way to test right? What happend if the power failure, in the server orrrrrrrrrrr in the client side? Can this code will function?

Please see this,

con.setAutoCommit(false);
try {

PreparedStatement updateSales = con.prepareStatement("UPDATE Book SET SALES = ? WHERE Title LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "ABC");
updateSales.executeUpdate();

PreparedStatement updateInvoice = con.prepareStatement("UPDATE Bill SET Amount = ? WHERE Title LIKE ?");
updateInvoice.setInt(1, 2500);
updateInvoice.setString(2, "LMN");
updateInvoice.executeUpdate();

con.commit();
con.setAutoCommit(true);
}
catch (SQLException e) {
   con.rollback();
   System.out.print(e.printStackTrace());
}




He is only catching the error if there is a programming problem. Ha.... Is that what commit() and rollback() for.

So, I am wondering, is that something call rollback() and commit() in WEBASED system. If yes, what would be the best senario to let this command to function. And anohter Q is, this 2 command support client side commit() and rolback??

TQ.
0
Comment
Question by:poweranger77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:allahabad
ID: 8124908
He is only catching the error if there is a programming problem. Ha.... Is that what commit() and rollback() for.

So, I am wondering, is that something call rollback() and commit() in WEBASED system. If yes, what would be the best senario to let this command to function.

-->He is only catching the error if there is a programming problem. Ha.... Is that what commit() and rollback() for.

Here, in explantion, i have taken Oracle Database.

I do'nt know do you mean by programming problem,
if there is any programming error, first compiler will catch it. If there is any RunTimeException , It can be caught by Exception class.

This program runs on server. When user submits a form , request will go server, here job of this program starts, if inserts/updates is successful, then changes will be made permanent inside database.

1.Now, if power failure happens on the webserver(suppose webserver and database server on different machine), since changes has not gone to database. Database will not have any action to do on this transction, and client will see some unexpected error on the browser, because connection between cliect and webserver is lost due to power
failure.
2. Suppose that power failure happens on the database server, Since changes have gone to database before commit and rollback, in the oracle during the instance recovery these changes can be recovered from the redolog file.In all these events, there is bidirection signal flow from client->webserver-->database, so with exception propagation any errors could be displayed to client.

Now , commit to make the changes permament into database so that otherusers can see it. If you fire update statments ot delete statements, you are locking the row , if do not issue commit and rollback ,you are holding the record.

Rollback is issued to tell the database,
something wrong has happened, please rollback whatever has been inserted and release the lock.
If you do not give signal to database after DML operation,
database server will be holding the lock( this when autocommit is false), until you close the connection.

If you close the connection, oracle commits any successful
transction of the logical trasaction and rollback any
unsucessfule DML operation.

When autocommit is true, Database server will commit each successful transctaion and rollback any unsuccessful transction.



--->this 2 command support client side commit() and rolback??
There is no client side commit() and rolback() concept.
If  you mean client side by browser.
For database server(for any server), each request is client request. This request may come from the JDBC call,
or direct sqlplus client call, or Pro*c, Pro*COBOL call,
or call from any client that can communicate to Oracle Database.

con.setAutoCommit(false); is kept when more than transaction are invloved to form a logical transaction. So that you commit it when one logical transction is successful, otherwise your transction is not vaild, you will rollback it.

Why i have written, sometimes you may not see commit and rollback in JDBC code, because default autoCommit is true. So for each sucessful transction it will commit and rollback unsuccessful transaction. Whenever you close the connection, the effect will be same , means commit successful transaction, and rollback unsucessful transction.




0
 
LVL 14

Accepted Solution

by:
kennethxu earned 1000 total points
ID: 8125006
>> Well, i think chainreaction finally statisfy with the help offered by Object and ken
I'm not sure about this, he had never accepted the answer. :)

>> But how he is going to test either this commint() and rollback() is functioning or not?
first, I think the commit is always been tested. for the rollback, he can put a delay between 2 updates. Thread.sleep( 10000 ), then he can easily simulate a failure during that 10 second by, just name some, disconnect the network, shutdown the database, kill the web server, rename the table that 2nd update use, .....

>> He is only catching the error if there is a programming problem. Ha.... Is that what commit() and rollback() for.
an exception is NOT just programming problem. IOException could be cause by HDD hardware problem, SQLException can cause by network and/or database failure.

>> So, I am wondering, is that something call rollback() and commit() in WEBASED system
from transcation point of view, web based application is no different from any other application. web based banking system account transfer need transaction.

withdraw account A
deposit account B
commit
when error rollback

without transaction, I don't think you'll be happy when banks webserver crashs right after withdraw of your account is executed.

>> And anohter Q is, this 2 command support client side commit() and rolback??
commit and rallback is used to manager database transaction, whatever application connected to database is the client of the database. if I didn't get your question right, tell us which client you are referring to?
0
 
LVL 92

Expert Comment

by:objects
ID: 8125084
> i think chainreaction finally statisfy with the help offered by Object and ken.

Yes and I didn't get any credit.
Perhaps you are more appreciative :-)

> But how he is going to test either this commint() and rollback() is functioning or not?

simply throw an exception at appropriate time.

> What are we talking about about is Web- based
> application. Can this web-base program commit and
> rollback? I am doubt. Once user press the submit button,
> how will him(chainreaction) go to test his program
> either is it working or not??

whether it is webbased or not makes no difference.

> He is only commiting or rollback if there is a
> PROGRAMMING ERROR. And this is not the proper way to
> test right? What happend if the power failure, in the
> server orrrrrrrrrrr in the client side? Can this code
> will function?

If the power fails mid-transaction then the transaction will not be committed so it's fine.

Also your code is a little flawed, in that autocommit will be left off in the case of an exception. You also only check for SQLException, if another exception type occurs you fail to rollback the exception.

con.setAutoCommit(false);
try {

PreparedStatement updateSales = con.prepareStatement("UPDATE Book SET SALES = ? WHERE Title LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "ABC");
updateSales.executeUpdate();

PreparedStatement updateInvoice = con.prepareStatement("UPDATE Bill SET Amount = ? WHERE Title LIKE ?");
updateInvoice.setInt(1, 2500);
updateInvoice.setString(2, "LMN");
updateInvoice.executeUpdate();

con.commit();
}
catch (Throwable e) {
  con.rollback();
  System.out.print(e.printStackTrace());
}
finally
{
  con.setAutoCommit(true);
}


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:poweranger77
ID: 8125558
Your respond time is remendous.
That's why it's make me come back to this web-sites more often. (But somebody will be more prefer if I visit the sponsor adver)>

OK, get back to the question.

allah, ken and object,

SQLException, IOException and any other exception on Java API base, is they include the power failure in?

For example, power failure, change of table, disconnect the network, shutdown the database, kill the web server, rename the table that 2nd update use, .....

Is this all example listed above can be caught by SQLException which catch the runtime exception?  And is all the above can consider runtime exception?
(Runtime Exception -- problem that occur when the program is executing. For example, calculating of 2 variable and maybe divided by 0)...

Ken, the client is the browser that I am referring. Because all the while in my mind the clien-server support commit() and rollback() very well. Trust me, I have simutale some problem , end up it's still didn't commit or rollback. For example,I have try to "kill" the browser when it's updating or executing some sql statement. But I haven't try your method, by putting Thread.sleep( 10000 ), to hold the transaction and do some "bad" thing on the server....

But friend, how can I do this, is it this thread.sleep() at the end of the 3 statement or before the statement?

And finally I hope chainreaction can log in and share us his ideas...



0
 
LVL 92

Expert Comment

by:objects
ID: 8125650
> is they include the power failure in?

Of course they don't :)
If the power goes your application stops.

> And is all the above can consider runtime exception?

No none of the above are RuntimeExceptions.
RuntimeExceptions are exception that (funnily enough) extend RuntimeException :)

You point about Exceptions is that in your example you need to cover all possible instances, which you weren't. Plus you were not turning on autocommit after an exception occurred.

> , is it this thread.sleep() at the end of the 3
> statement or before the statement?

Easier just to simply throw an exception.

> I have try to "kill" the browser when it's updating or
> executing some sql statement.

Unless this is an applet the browser really does not have much to do with it, the code is getting executed on the server.



0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8128643
>> I'm not sure about this, he had never accepted the answer. :)
I take it back, chain PAQed the qestion right after I posted those.

>> Yes and I didn't get any credit.
>> Perhaps you are more appreciative :-)
objects, chain said in his post that he's one of your supporter :-)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8129060
>> SQLException, IOException and any other exception on Java API base, is they include the power failure in?
yes or no.
yes - if the power failure is on Database Server.
yes - if the power failure is only on one of the HDD you are accessing.
yes - if the power failure is on a file server.
no - if the power failure is on your webapp server, the program stops at where it is.

>> Runtime Exception -- problem that occur when the program is executing.
in java RuntimeExceptin has special meaning, they means exceptions extends from RuntimeException, those exceptions are not been checked (grammar check) by compiler.

>> Because all the while in my mind the clien-server support commit() and rollback() very well.
transaction must be supported in database level, there is no differece between client-server or web based (in this case, your app server is the client of your database)

>>  have try to "kill" the browser when it's updating or executing some sql statement
browser never update/execute any sql statement, it's your application server that's executing those statement. browser only submit a request to the server and simply waiting for the response from server. kill the browser doesn't trigger any event, unless you actively check the socket connection in your servlet and programmatically rollback your transaction when socket connection is lost.

>> how can I do this, is it this thread.sleep() at the end of the 3 statement or before the statement?
you'd better put it in between update statements, so the update after sleep will fail.

IMPORTANT:
once you disable the autocommit, your update will "hold" on database until you issue a con.commit(), as long as you don't commit the transaction, it will be automatically rollback by database when a failure is occur.

for example, database will automatically rollback any uncommitted transaction when:
connection to database is closed;
database failure occur;
network connection lost;
client crashed;
and whatever failure before commit is executed.
0
 
LVL 92

Expert Comment

by:objects
ID: 8132672
Didn't I just basically say most of that :)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8132960
>> Didn't I just basically say most of that :)
maybe we got different dictionaries have different explaination of word most :) let's trust poweranger77's dictionary.
0
 

Author Comment

by:poweranger77
ID: 8148376
Guys, thanks for your cooperations...

>>objects, chain said in his post that he's one of your supporter :-)

Ken, this time I make me yours...

Ha....

Object, thanks for your help....
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8148569
thanks poweranger, I appreciate it :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
New style of hardware planning for Microsoft Exchange server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question