?
Solved

Commit(), Rollback(), Record Locking

Posted on 2003-03-04
23
Medium Priority
?
1,366 Views
Last Modified: 2007-12-19
I am having some problems with this, actually just fell confuse and need some advice.

I have read some book regarding all this rollback and commit issues.

This is the sample programs that I read.

...
...
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());
}

...
...


How is this work? So far I know, con.setAutoCommit() will set autocommit to false first. After that, it'll commit the statement after the 2 prepared Statement. And if the exception thrown, it'll rollback, con.rollback().
 

Here is the question.

1. How this program will know where to rollback and how it'll know which one can be recover back and up to which level it'll help me to rollback? Aren't should I set something like a "mark" to tell them until where they should start and when should stop?

2. Is it these 2 method, commit() and rollback() is including the Record Locking problems? For example, if User A just right after press the "Submit" button, what happend if another user, User B trying to press the sample program's "submit" button, will this cause the Record Locking? When at the same time 2 user is accessing the same file at the same time. Actually I have this concet because my legacy system is running RPG and RPG do take this in concern seriously. But so far I know, Java can handle multithread quite well. SO, just want to confirm any potential problem for me to hanbdle this record locking?

3. Any other recommandation reading material for me to read? I have tried the www.javaworld.com to find som eresources but ended with dissapointment. Please share some URL on this rollback, commit and recordlocking issues with me.

Thank You.

0
Comment
Question by:chainreaction
[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
  • 10
  • 7
  • 5
  • +1
23 Comments
 
LVL 92

Expert Comment

by:objects
ID: 8069999
1. it will rollback to the last commit.

2. think thats handled at the database level.

3. will see what i can find.
0
 

Author Comment

by:chainreaction
ID: 8070140
objects, what happend to you? Busy is it...

Please explain more details...

1. Is it necessary that I should put the commit() statement in each and individual PreparedStatement??
Use this as a model.

Original :
==========

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());
}


After
=====
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();

con.commit();

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());
}


Correct....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 92

Expert Comment

by:objects
ID: 8070199
> 1. it will rollback to the last commit.

Should have said "... last commit or rollback.".

> Busy is it...

Yes, we've just released a new site for doing online surveys and evaluations at www.evalu8.com.au. That and a few other projects have been dominating my time.
(Not to mention the birth our first child :-) ).

> Is it necessary that I should put the commit() statement
> in each and individual PreparedStatement??

Why not just use autoCommit?


0
 

Author Comment

by:chainreaction
ID: 8070293
That's sound good object. Because recently it's seldom see you actively involve in the forum.

I am one of your supporter because you have help me a lot.

Friend, my code, the commit casuing me problem.

I have put in this,

*************
************

     if (conn==null) {  getConnection(); }
//Added this
       conn.setAutoCommit(false);
       System.out.println("Auto Commit is Off");
//before that          
          try {
               
     PreparedStatement updatejwpf10 = conn.prepareStatement("INSERT INTO PLLIB.ABC" +
                      " 
     updatejwpf10.execute();
     System.out.println("JWF10 Finished INSERT");
     
     PreparedStatement updatejrpf20 = conn.prepareStatement("UPDATE
datejrpf20.executeUpdate();
     System.out.println("JRPF20 Finished UPDATE");    
                   
     PreparedStatement updatewaslog = conn.prepareStatement("INSERT INTO " +
                      " foonglib.     updatewaslog.execute();
     System.out.println("WASLOG Finished INSERT");

//Added this    
     conn.commit();                  
     conn.setAutoCommit(true);
     System.out.println("Auto Commit Set-on");
                       
     }
        catch (SQLException sqle) {
             System.out.println("ExecuteQuesty() Got Error "+ sqle);
             System.out.println("Error Happend, Rolling Back");
//Added This
             conn.rollback();
             success = false;
     }


After I added this, //Added this, all my prepared statement doesn't run.
And I received this error message,
com.ibm.db2.jdbc.app.DB2DBException: ABC in PLLIB not valid for operation


And if I REM off all  the commit "things", I can insert, update and insert.

What is happening?
0
 
LVL 92

Expert Comment

by:objects
ID: 8070494
One of your updates is failing, causing an exception being thrown. Which rollsback all the updates.

What is it u are trying to achieve?
0
 

Author Comment

by:chainreaction
ID: 8070685
Object, no. The updating statement and the inserting statement is fine. This can be proved that if I rem off these,

conn.setAutoCommit(false);
conn.setAutoCommit(true);
conn.commit();                  
conn.setAutoCommit(true);
conn.rollback();

then all the statement working fine.

Object, I am suspecting the AS400 DB2 is causing me problem......

Sorry to distrub you....

What I want to achive is to test out how this commit and rollback stuff works.

And why I need to setAutoCommit(false) is beacsue this link that you suggested to me ask me to do so... and other reference book as well...

:-)

http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html 


0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8072963
looks like your have disabled transaction in db2 or jdbc driver. have you every specified setTransactionIsolation(Connection.TRANSACTION_NONE)? this will disable the trasaction and you'll sure get the error if you do.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8072982
in addition, have you enabled journalling for the file in the DB2 library?  Commitment control requires journalling.
0
 
LVL 92

Expert Comment

by:objects
ID: 8074761
> is beacsue this link that you suggested to me ask me to
> do so... and other reference book as well...

Only if autocommit is not suitable for you.
eg. if you only want two updates to occur, if both are successful.
0
 

Author Comment

by:chainreaction
ID: 8076788
kennethxu, I agree with you on the journling. But I am not agree on the TRANSACTION_NONE. because I didn't code that.

I am looking into this AS400 journaling....
TQ.

Object, i must cater in case there is 4 or more prepareStatement is required.
:-0
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8076863
>> kennethxu, I agree with you on the journling. But I am not agree on the TRANSACTION_NONE. because I didn't code that.

I mean either one will cause the problem. I try to mention all the possibilities as I can think of, so you can check out to see which is relevant to you.
0
 

Author Comment

by:chainreaction
ID: 8077159
ken, problem with this journaling.... find all the ibm redbook i can... useless..... :-)

ANy ideas?
0
 

Author Comment

by:chainreaction
ID: 8078522
ken, manage to commit and rollback.
SOVLVED.

CRTJRNRCV - create journal receiver
CRTJRN  - create journal
STRJRNPF - attach the PF(db) that which to journal

Run the SQL.
Purposely generate a error problems.
rollback();

Any potential issues on the record locking ah?
Not sure....

Thank you for both of you, ken, object....

Any extra information to teach me... (scare the record locking...) maybe like what object said, it'll handle by the Database Driver Manager.....
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8081242
it is sure that once you commit or rollback, all lock will be released.
the records that you have updated and inserted before commit or rollback are locked, any update action on those records from other connection is blocked.
reading of locked records is depend on database isolation level.
0
 

Author Comment

by:chainreaction
ID: 8086706
Meaning to say it's all up to the database manager to handle all this stuffs right? (Record Locking)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8088602
record locking is not just one thing, there are read lock and write lock, exclusive lock and shared lock...

I have explained write lock in my previous comment, which should apply to most database configuration.
for read lock, depend on isolation level supported by  datatbase and configured by DBA.

So, what's your real concern?
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8089194
actually, I don't see any problem as long as there no long delays between your first update to commit. records are only locked during this period.
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 200 total points
ID: 8089233
with autocommit:

// no lock
update1.executeUpdate(); // set (A) of record(s) locked during update.
// lock released
update2.executeUpdate(); // set (B) of record(s) locked during update.
// lock released


without autocommit:
with autocommit:

// no lock
update1.executeUpdate(); // set (A) of record(s) locked.
// record set A is still locked
update2.executeUpdate(); // set (B) of record(s) locked.
// record set A and B are locked
commit();
// all locks released
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8089241
>> without autocommit:
>> with autocommit:
sorry, meant "without autocommit:"
0
 

Expert Comment

by:wjh7554
ID: 8107461
kenn, how about if I am running on SQL Server or mySql. Do i need to set anything extral like this journaling issues in AS400?

0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8107547
no journaling on SQL Server and mySql. but there are other settings in those database to enable/disable transaction.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

What's worse than having your data encrypted by ransomware? Getting attacked by a so-called "wiper," which simply destroys the data and offers you no hope of ever seeing it again.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

752 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