Is there a need for tx.commit after just reading from DB?

petr82 · **Joined:** Tue Jan 24, 2006 9:55 pm **Posts:** 7

Hibernate version: 3.1

If I want to just read from a database, so something like this:

Code:

        Session session = HibernateUtil.currentSession();
        Transaction tx = null;

        tx = session.beginTransaction();
            
        org.hibernate.Query query = session.createQuery("from Document");
        query.list(); // just symbolically

        tx.commit();

Is there a need for commiting this transaction? If i just read and don't change anything then do i need to commit? If I should, what would happen if I don't use the commit?

Is there any need for a transaction at all? I tried this:

Code:

        Session session = HibernateUtil.currentSession();
        org.hibernate.Query query = session.createQuery("from Document");
        query.list(); // just symbolically

But sometimes I got an error saying "Couldn't obtain connection" (although sometimes it was ok).

Thanks for advice.

david · **Posted:** Sun Mar 12, 2006 9:07 pm

Always use transactions as the database may be implemented with read locks etc and needs to know when it has to release them.

petr82 · **Joined:** Tue Jan 24, 2006 9:55 pm **Posts:** 7

Thanks for your reply. I would just like to ask again about the commiting if I use a transaction. Is it necessary? If i just read and don't change anything then do i need to commit? If I should, what would happen if I don't use the commit?

david · **Posted:** Mon Mar 13, 2006 6:59 pm

You need to end the tx so thats either Commit or Rollback. Either will work in some cases/databases its faster to Commit others its faster to Rollback. The differences are minor thus I personally always commit as the database implementation is more likely to be optimised for the operation that occurs more often.

jt_1000 · **Posted:** Mon Mar 13, 2006 7:15 pm

david wrote:

Always use transactions as the database may be implemented with read locks etc and needs to know when it has to release them.

David - why are you recommending to "Always..." Should this be based strictly on your desired isolation levels for that class/table? I can't imagine a database locking by default because someone queried the database. Please enlighten us.

Always would mean that you always are sending two database i/o one for the query and 2nd for the "commit". Why always burden the system this way...?

jt_1000 · **Posted:** Tue Mar 14, 2006 2:23 pm

David,

If you wouldn't mind elaborating on your explanation a bit... I'd really appreciate it. I also noticed that named Queries require a transaction w/commit to release the cursor.... In this other posting I could not get it to close the cursor w/o the trx/commit.

Please look at:
http://forum.hibernate.org/viewtopic.php?p=2296098#2296098

Quote:

I tried closing the ScrollableResults directly and that was not sufficient to close the cursor.

I also tried Hibernate.close(itr);
got an exception (IllegalArgumentException: not a Hibernate iterator)... but I couldn't create a hibernate iterator, since that is created from (Query q) q.iterate(); and that throws an UnsupportedOperationException "SQL queries do not currently support iteration". So apparently that "q.iterator()" only works with HQL queries; and not named Queries ...

david · **Posted:** Tue Mar 14, 2006 9:02 pm

The databases goal is the maintain data integrity while multiple sources are accessing it concurrently. To be able to do this efficiently and guarantee the goals requires mechanisms to (relative to the isolation level) achieve this. Those mechanisms usually involve some form of locking (or replicating the data view to minimise locking).

So the database needs to know when an access starts and when it ends so that the mechanisms can co-ordinate the actions requested. There appears to be no confusion that Transactions (hence locking etc) are required when you updating the data. Reading also requires transactions because read locks (or replicated view) are required because someone else might be trying to aquire a write lock.

The locking co-ordinates resource requests to maintain the approrpiate isolation level. The commit / rollback is a indication that the mechanism used (locks or replicated views) now requires to be cleaned up (with a possible permanent change made). The confusion is reasonable (but frustrating) for Reads as many tools and even the JDBC driver standard (can) hide the transaction from you as they employ automatic transactions. The result is many more fine grained transactions rather than a single operation wrapping the use-case. This is dangerous as you could get partial updates and then need to manually wind back the changes.

So in practice you are using Transactions in the back ground even if you think you are not. In the case that you access the system with manual transactions enabled you are then leaving it up to the database to choose what to do when the connection is closed. Now this is NOT defined behaviour and indeed different databases do different things (some rollback some commit) this can lead to very hard bugs to find when you are supporting multiple databases for your product. The moral of the story - always use transactions becasue they are there and your going to find (sooner or later) that your much better off controlling it yourself.

Hope this helps.