NHibernate hangs on INSERT queries.

ctacoh · **Joined:** Wed Aug 02, 2006 6:22 am **Posts:** 3

Good day.
This example code

Code:

...
User newUser = new User();
newUser.Id = "joe_cool";
newUser.UserName = "Joseph Cool";
newUser.Password = "abc123";
newUser.EmailAddress = "joe@cool.com";
newUser.LastLogon = DateTime.Now;

// Tell NHibernate that this object should be saved
session.Save(newUser);
// commit all of the changes to the DB and close the ISession
transaction.Commit();
session.Close();
...

hangs on "transaction.Commit()" string.
I take source code of NHibernate and find that thread hangs in
BatcherImpl.cd

Code:

      public int ExecuteNonQuery( IDbCommand cmd )
      {
         int rowsAffected = 0;

         CheckReaders();

         Prepare( cmd );
            //cmd.CommandText = "INSERT INTO star VALUES(1, '2')";
            //cmd.ExecuteScalar();

         rowsAffected = cmd.ExecuteNonQuery();
         return rowsAffected;
      }

on
"rowsAffected = cmd.ExecuteNonQuery();" string.
Did anybode see this behavior? Maybe anyone know, how to avoid this.

sergey · **Posted:** Wed Aug 02, 2006 6:49 am

This could mean that the transaction is waiting on a lock.

ctacoh · **Joined:** Wed Aug 02, 2006 6:22 am **Posts:** 3

Thank you for your answer.
And what can i do with it?
DBMS is oracle, i try to insert row in an empty table and nobody use it in this time, so how transaction can be locked?

ctacoh · **Joined:** Wed Aug 02, 2006 6:22 am **Posts:** 3

All troubles was in oracle client, i changed it and every thing become perfect.

bbhiber · **Joined:** Fri Oct 06, 2006 5:02 am **Posts:** 1

I have the same problem. What did you change in Oracle Client?

guymahieu · **Joined:** Sat Sep 09, 2006 5:55 am **Posts:** 23

We have had this as well, I believe it was with the microsoft oracle driver. I couldn't find a way to reproduce it consistantly, so we switched to the oracle ODP driver and we haven't had the problem since then (although we changed a lot since then, so it might not even be related).

shindigo · **Joined:** Thu May 03, 2007 9:06 am **Posts:** 2

I am also having this problem. The program is a simple test that fills in the attributes of an object and attempts to save one row to the (Oracle) database. The save completes, but the commit hangs indefinitely.

When I break into the debugger and look at the stack trace I can see that it is waiting at cmd.ExecuteNonQuery() as described in the original message of this thread.

I have tried a few different things in hibernate.cfg.xml:

1. I removed <property name="connection.driver_class"> and set <property name="dialect">NHibernate.Dialect.OracleDialect

2. I removed <property name="connection.driver_class"> and set <property name="dialect">NHibernate.Dialect.Oracle9Dialect

3. I tried <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver

4. I tried <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver

5. I tried <property name="connection.driver_class">NHibernate.Driver.OleDbDriver

These all exhibit the same behavior (hanging on the commit).

Can anyone suggest something else to try?

Thanks,

shindigo

guymahieu · **Joined:** Sat Sep 09, 2006 5:55 am **Posts:** 23

Like sergey mentioned above, most of the times this means there is a lock on the DB that blocks the commit.

You should really check the DB for locks on the relevant tables/records. We have had this problem when somebody had an open TOAD, SQL Developer, ... instance on the database. Killing that session will allow your code to execute further.

Hope this helps.

shindigo · **Joined:** Thu May 03, 2007 9:06 am **Posts:** 2

guymahieu --

Thanks for the quick response.

I think its unlikely that this is a locking situation.

1. I am the only user of the db.
2. I just came in this morning and ran the program and it hung. Any old sessions would have timed out overnight.
3. This is an INSERT operation and there would have to be a table-level lock to prevent the insert from happening.
4. After the program hangs, I kill it, go into TOAD and I can INSERT the row manually and commit it without a problem. In fact at any time I can manually insert the row successfully.

I have also tried using an assigned ID instead of a sequence:
<id name="ID">
<column name="ID" not-null="true"/>
<generator class="NHibernate.Id.Assigned">
</generator>
</id>

thinking that allocating the sequence might be a problem, but I still see the same behaviour: the save works, but the commit hangs.