Using transactions for read-only operations

nbhatia · **Joined:** Sun Sep 25, 2005 11:35 pm **Posts:** 57

For read-only operations such as loading objects and queries

1) If I do not begin a transaction, is one started implicitly anyway?
2) Is it more efficient to use the session object only, and NOT start a transaction?

Thanks.
Naresh

nbhatia · **Joined:** Sun Sep 25, 2005 11:35 pm **Posts:** 57

Anyone? Think this should be an easy one for the committers!

luxspes · **Joined:** Fri Jul 29, 2005 9:46 am **Posts:** 101

Have you read: http://www.hibernate.org/42.html ?

nbhatia · **Joined:** Sun Sep 25, 2005 11:35 pm **Posts:** 57

Thanks, I read it. The closest it comes to answering my question is the following paragraph:

Quote:

Transactions also group data access operations, in fact, every SQL statement, be it queries or DML, has to execute inside a database transaction. There can be no communication with a database outside of a database transaction. (Note that there are such things as read-only transactions, that can be used to improve cleanup time in a database engine if it is not smart enough to optimize its own operations.)

This implicitly implies that Hibernate will create a transaction even if the calling code doesn't. But I am still curious:

1) Which way is more efficient?
2) Is the answer the same for NHibernate (which is the platform for my original question)? I read http://www.hibernate.org/hib_docs/nhibernate/html/transactions.html but it does not address the issue.

So to clarify my question, which of the following two options is the preferred way to execute read-only transactions in NHibernate? Are they logically the same?

Option 1

Code:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();
try
{
    IList objects = session.CreateQuery("...").List();
    tx.Commit();
}
catch (Exception)
{
    tx.Rollback();
    throw;
}
finally
{
    session.Close();
}

Option 2

Code:

ISession session = sessionFactory.OpenSession();
try
{
    IList objects = session.CreateQuery("...").List();
}
finally
{
    session.Close();
}

luxspes · **Joined:** Fri Jul 29, 2005 9:46 am **Posts:** 101

I have to admit, you got me curious too... so I started makeing some tests... and the results puzzled me... but the surprising thing came with "write" transactions... (this is with NHibernate 1.02)
If I do this (Session is in FlushMode.Never):

Code:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();
try
{
    SomeClass someObject = new SomeClass();
    session.SaveOrUpdate(someObject);  //this inserts in the database (really sends SQL to the database, I saw it in SQL2005Profiler)
    tx.Commit(); // this commits the transaction
    session.Flush() //this does nothing with the database
}
catch (Exception)
{
    tx.Rollback();
    throw;
}
finally
{
    session.Close();
}

So, the SQL is sent to the database on SaveOrUpdate on new objects... but... if I update a previously persisted object:

Code:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction(); //this begins the transaction
try
{
    SomeClass someObject = session.Get(typeof (SomeClass ), id);
    someObject.SomeProperty="Some Different Value";
    session.SaveOrUpdate(someObject);  //this does nothing!
    tx.Commit(); // this commits the transaction! (with NOTHING!)
    session.Flush() //this sends the UPDATE to the database... without transaction!!!!
}
catch (Exception)
{
    tx.Rollback();
    throw;
}
finally
{
    session.Close();
}

So... I guess the Flush isnt warranted to be transactional? or it doestn start a transaction on flush... because it is just one object? should I do the flush inside transaction scope? how can I be sure that my flush is transactional? can anyone confirm this?

luxspes · **Joined:** Fri Jul 29, 2005 9:46 am **Posts:** 101

Okey... I realize this is offtopic but I am completly puzzled, I tried it with two different objects (and FlushMode.Never):

Code:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction(); //this begins the transaction
try
{
    SomeClass someObject = session.Get(typeof (SomeClass ), id);
    SomeClass someOtherObject = session.Get(typeof (SomeClass ), id);
    someObject.SomeProperty="Some Different Value";
    session.SaveOrUpdate(someObject);  //this does NOTHING!
    session.SaveOrUpdate(someOtherObject );  //this does NOTHING!
    tx.Commit(); // this commits the transaction! (with NOTHING!)
    session.Flush() //this sends the two UPDATES to the database... without transaction!!!!
}
catch (Exception)
{
    tx.Rollback();
    throw;
}
finally
{
    session.Close();
}

Is this the intended behavior? if the server is turned off (ligths go out) between the two updates... there is a possibility that only one of them is commited?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Correct - flush in itself isn't transactional. If you have flushmode set to commit then flush will automatically be called before the transaction is committed, but if it's set to never then it will never be called unless you explicitly call it. If that's outside a transaction then it won't be transactional.

When you save a new object it will be sent directly to the DB only if you have specified in your mapping to use DB assigned identifiers. It needs to do this to get the id. If you do your save or update in a transaction then the db action will be part of the transaction and will be rolled back if you roll back the tx.

Cheers,

Symon.

luxspes · **Joined:** Fri Jul 29, 2005 9:46 am **Posts:** 101

Quote:

Correct - flush in itself isn't transactional. If you have flushmode set to commit then flush will automatically be called before the transaction is committed, but if it's set to never then it will never be called unless you explicitly call it. If that's outside a transaction then it won't be transactional.

Thanks... wouldn't you have... by any chance... a link to some place in nhiberante documentation of faqs that says that "this is fine"? (and the reasons...)

Quote:

When you save a new object it will be sent directly to the DB only if you have specified in your mapping to use DB assigned identifiers. It needs to do this to get the id. If you do your save or update in a transaction then the db action will be part of the transaction and will be rolled back if you roll back the tx.

And is that really needed? couldn't the id be feched when the transaction is commited? do you know why it has to be done this way?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Actually, I don't remember where I found this info, but I'm pretty sure it's correct.

I think that the idea is that an object that is considered "persistent" (a member of a session) is supposed to have an identifier so that the session can identify the objects in it. If you use any other system of generating Ids they'll be assigned immediately when the object is saved to the session, so I think the reasoning is that this paradigm needs to be followed even if you are using database assigned identifiers. As a result the row is inserted immediately and the identity is harvested so that the object has an Id that will never change during the lifetime of the object.

I've often wondered why the Hibernate designers opted to choose the method names Save and Update since this tends to lead beginners to think that the data is going into the database at the time these are executed, rather than being made members of the session and *possibly* persisted later (depending on your flush mode and, if there's a transaction whether you commit it). But that's another story...

I guess in the end that the idea is that you start a session, begin a transaction and then start adding objects to the session (loading, saving or updating). At the end you commit, and all changes were flushed to the DB at any time during the lifetime of the session are committed, or you roll back and all changes are rolled back. In the meantime, the default is that there's a database lock on any changed/added rows so no other thread can access them during the transaction. This does mean that you can't have a long running transaction if you add any new objects where the identifiers are database assigned since the database transaction will be aborted if you disconnect the session from the database connection...*sigh*...but that's life!

Hope that clarifies things a little. (!?!)

Cheers,

Symon.

sergey · **Posted:** Thu May 11, 2006 5:40 am

Quote:

Thanks... wouldn't you have... by any chance... a link to some place in nhiberante documentation of faqs that says that "this is fine"? (and the reasons...)

That would be http://www.hibernate.org/hib_docs/nhibernate/html/manipulatingdata.html#manipulatingdata-flushing.

nbhatia · **Joined:** Sun Sep 25, 2005 11:35 pm **Posts:** 57

BTW, anyone have an answer to my original question, which was for read-only operations (no save or update involved)? I also sent two alternative ways to code this use case. Are both ways as effiecient/essentially the same?

Thanks.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

As I understand read transactions they will ensure repeatable reads during the transaction, so no other thread can change the data you already read until you're finished.

Think of it this way:

If you have two queries which read data and the same row might be returned in both queries there is a possibility that someone might execute an update on that row between your two queries. As a result the value you get in query 1 may be different that that from query 2.

If you wanted to ensure that no one else was able to make that change between query 1 and query 2 then you'd wrap both of queries in a transaction. I don't think there's any particular need to commit the transaction at the end if it was only for read operations, but it probably won't hurt either.

For your particular examples you're only executing a single query, so a transaction wouldn't be needed.

That's my take on the matter anyway - anyone who's more of an expert please correct me if I'm wrong!

Cheers,

Symon.

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

merge_s.rottem wrote:

If you have two queries which read data and the same row might be returned in both queries there is a possibility that someone might execute an update on that row between your two queries. As a result the value you get in query 1 may be different that that from query 2.

This is not true if transaction isolation level is "Read committed". For "Repetable read" or higher isolation level the statement is true.

Gert

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Well there you go! Learn something new every day... :)

Symon.

Nels_P_Olsen · **Posted:** Mon May 22, 2006 4:54 pm

We still don't have an answer to the original question. I'd like to know one way or the other as well, since my boss is not happy that I implemented our base ASP page class to have an NHibernate transaction rather than just a session. He says that read-only transactions are a very bad idea and should NEVER be used.

The Hibernate documentation says that there are some performance benefits to readonly transactions. This is something he might listen to. Can someone clarify?