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

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

Why the connection default autoCommit mode is false?

hi all,
I'm using JBoss 3.2.5 as my application server. I had developed a Stateless Session Bean. In my Session Bean, I try to create a connection to my MSSQL Database via following code :

InitialContext initialcontext = new InitialContext();
DataSource dataSource = (DataSource)initialcontext.lookup("jdbc/myDatabase");
Connection conn = dataSource.getConnection();
System.out.println("Auto Commit Mode : " + conn.getAutoCommit());
initialcontext.close();

what's strange is the auto commit mode is defaulted to false. But if i use the same code in servlet, the default auto commit mode is true. Can someone please explain to me why it is like that??

thanks.
0
pleasure
Asked:
pleasure
  • 8
  • 7
1 Solution
 
rama_krishna580Commented:
Hi,

Transaction management with JDBC takes place via the Connectionobject. By default, new connections start out in auto-commit mode. This means that every SQL statement is executed as an individual transaction that is immediately committed to the database. To control commitment yourself, thereby allowing you to group SQL statements into transactions, you call setAutoCommit(false) on the Connection object. You can check the status of auto-commit with the getAutoCommit() method. Once you have completed all of your SQL statements, you call commit() to permanently record the transaction in the database. Or, if you encountered an error, you call rollback() to undo it.

For more info. refer this with examples....
http://pubwww.fhzh.ch/~ainci/public/verteiltesysteme/database/db4.html

R.K
0
 
pleasureAuthor Commented:
hi R.K,
Basically I understand who Transaction Management work, as you mentioned in your comment, By default, new connection start out in auto commit mode.

My question is, when i create a connection in a Stateless Session Bean, the connection is defaulted to non-autocommit mode, why it's happen? is there any seeting we need to configure in JBoss? The weird thing is, when i create the connection object from a servlet. The connection is defaulted to autocommit mode... :(
0
 
DaveyEssCommented:
When you are running inside a session bean in JBoss, JBoss is controlling the transactions and therefore sets the autocommit to false.  When you are running in a servlet, JBoss is not controlling the transaction and therefore autocommit is set to true.

The stateless session bean is transactional, whereas servlets are not transactional.  If you look at the deployment descriptor for your session bean, you will see that the transactional attribute is set to "Container" - the container manages the transaction.

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
pleasureAuthor Commented:
hi DaveyEss,
yupe, it's true that the <transaction-type> is set to Container it my Session Bean deployment descriptor.

Is it possible for us to change the value? if yes, what is the implication??


thanks.
0
 
DaveyEssCommented:
You can set the session bean to use bean managed transactions, in which case the container will do nothing transactionally.  If you do this however, you are loosing a great benefit and there isn't really any benefit in using a session bean over a servlet.

When using Container Managed Transactions, the container handles everything transactionally for you so you don't need to worry about transactions.  Everything that runs within one method in the session bean runs within one transaction.
0
 
pleasureAuthor Commented:
For Container Managed Transaction, is this mean that the container will automatically close all database connections created in the session bean when i exited from session bean?

0
 
DaveyEssCommented:
No, you still need to close prepared statements, connections, recordsets etc., but the container will handle all aspects of transactions for you.  With CMT, you can effectively forget about transactions.  All code run within one method in a session bean will be run within one transaction.
0
 
DaveyEssCommented:
Sorry, I forgot to mention.

If you are using CMT with JBoss and you forget to close a connection, JBoss will warn you and then close it for you.  I'm not sure if it will do this if you are using bean managed transactions.

This isn't portable however and you shouldn't rely on this behaviour.
0
 
pleasureAuthor Commented:
Sorry, I'm a bit confused.

1.With CMT, does it mean that we still need to close the database connection in order to return it to connection pool?

2.When we said "container will handle all aspects of transaction". Is this mean that the container will  commit the transaction if everything goes smoothly and rollback the transaction if something is wrong? But at the end, we still need to close the connection on our own?
0
 
DaveyEssCommented:
Yes, sorry.  You still need to close the connection to the database.  The container will commit or rollback the transaction depending on whether your method succeeded or failed.  If your method reached the end without throwing an exception, the transaction will commit, otherwise it will rollback.

During the method however, you still need to call conn.close() to close the connection to the database.  Its best to put this inside the finally block of your code so it is always closed.

e.g.

Connection conn = .....
try {
// Do work
}
catch (Exception e} {
// Transaction will be rolled back
}
finally {
// Close connection to database and resultsets, statements etc.
}

// Method now exits and transaction is committed.
0
 
pleasureAuthor Commented:
Yes, i realize that.
Actually this is what we planned to do.
In our practise, whenever we create a database connection, we wil close the connection after we have finished with the connection.
By doing so, we will be able to return the conenction back to the connection pool. Before we close a connection, it's our habit to examine whether the connection is commited, if not, we are commit the connection.
The problem arise here, when i try to commit a connection which is created in a session bean, i will hit an error saying that i'm not allowed to do so. That's why i'm asking, if CMT will close the connection for me, then it should be ok if i didn't close the connection on my own, just let the container closes the connection. But it's seem like the container won't close for me. Have to think a workaround to tackle this issue...:(
0
 
DaveyEssCommented:
You need to close the connection, but you don't need to worry about commiting the transaction.  If you have submitted SQL to the database, the container will automatically commit it (or roll it back) as necessary.  You shouldn't have to worry about commits at all.

Carry on as you are, create a connection, do stuff with the database and then close the connection.  That should be all you need to do.  Remember, the container is managing transactions so you don't need to.

0
 
pleasureAuthor Commented:
What if before I close a connection, I wished to manually invoke 'connection.commit()' to commit any uncommited connection, can I do so?
0
 
DaveyEssCommented:
No you can't do that with CMT.  You are forbidden from calling commit().  There should be no reason to call this as the container will call it for you.  The container will commit all uncomitted work at the end of its method invocation.
0
 
pleasureAuthor Commented:
If that is the case, i need to find a workaround to handle this issue...

thanks a lot for all the suggestion..:)
0
 
DaveyEssCommented:
No problem.  Good luck
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.

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