Oracle Update Hangs

Question

I'm having trouble with an Oracle update. The call to ExecuteNonQuery hangs indefinitely.

The code:

using (OracleCommand cmd = new OracleCommand(dbData.SqlCommandStr, conn))
{
    foreach (string colName in dbData.Values.Keys)
        cmd.Parameters.Add(colName, dbData.Values[colName]);

    cmd.CommandTimeout = txTimeout;
    int nRowsAffected = cmd.ExecuteNonQuery();
}

CommandTimeout is being set to 5, and the parameters are being set to small integer values.

The query:

UPDATE "BEN"."TABLE03" SET "COLUMN03"=:1,"COLUMN04"=:2 WHERE COLUMN05 > 0

The query runs quickly from sqlplus, and normally runs fast from my code, but every once in a while it hangs forever.

I ran a query on v$locked_object, and there's one record referring to this table, but I think that's the update that isn't completing.

There are two things I would like to know: What might cause the update to hang?

More importantly, why isn't an exception being thrown here? I would expect the call to wait five seconds, and then timeout.

What version of Oracle? Can your run this query: "select * from v$version" and copy out row 1. It should look something like: "Oracle Database 10g Release 10.2.0.4.0 - Production". Also, is this a real table or a view with instead of triggers? Does it have any before/after triggers? — Andrew Martinez, Jul 23 '09 at 15:11

score 24 · Answer 1 · answered Mar 09 '11 at 19:39

24

I'm bumping this due to its page rank in search results.

In my case, it was because I had executed a query in SqlPlus, but I forgot to commit it. In this case, it was as Vincent stated: the row was locked in another session.

Committing the SqlPlus update resolved the issue.

answered Mar 09 '11 at 19:39

Kenneth

241
2
2

1

exactly what happened to me. as soon as I executed `rollback` in SQLPlus, it returned. – DJ' Sep 16 '14 at 13:33
1

lifesaver - 4 years later! – jasonscript May 22 '15 at 02:55
1

In my case I was using the Oracle SQL Developer and after the commit it worked! – Daniel Bonetti Jun 24 '15 at 16:50
I was doing the same thing. Thanks! – Force Feb 17 '16 at 20:09
Mother #)#$, this happened to me too. – Rob May 17 '16 at 16:01
In my case, I left an update statement on a table open w/o commit. Then I was updating the same record through the application and the ExecuteNonQuery never came back. Committing the original update statement in Sql Developer did the trick – dotnetdev_2009 Dec 04 '18 at 19:29
9 years later and this is still a helpful answer... Just saved me a lot of head scratching! – WSC Jan 22 '20 at 12:30

score 7 · Answer 2 · answered Jul 23 '09 at 15:19

When a simple update hangs it often means that you are blocked by another session. Oracle won't allow more than one transaction to update a row. Until a transaction has commited or rolled back its modifications it will lock the rows it has updated/deleted. This means that other session will have to wait if they want to modify the same rows.

You should SELECT ... FOR UPDATE NOWAIT before you UPDATE if you don't want to hang indefinetely.

score 4 · Answer 3 · answered Jul 12 '13 at 23:07

I was having a similar issue that was being caused by a Sql command that hadn't been committed - I'm guessing the program crashed in the middle of one at some point.

Here is how I fixed my issue:

First, open SqlPlus and do a commit to fix the issue.

Next, change the code to commit the transaction or rollback if an exception occurrs. That will keep the problem from occurring again.

You could change your code to something like this:

using (OracleTransaction transaction = conn.BeginTransaction())
{
    using (OracleCommand cmd = new OracleCommand(dbData.SqlCommandStr, conn))
    {
        foreach (string colName in dbData.Values.Keys)
            cmd.Parameters.Add(colName, dbData.Values[colName]);

        cmd.CommandTimeout = txTimeout;

        try
        {
            int nRowsAffected = cmd.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
        }
    }
}

score 2 · Answer 4 · answered Jul 23 '09 at 17:06

You can see what event your session is waiting on by querying V$SESSION_WAIT (after identifying the SID of the session, probably by looking at V$SESSION). If the event is something like "enqueue", you are waiting on a lock held by another session, which does seem like a likely explanation in this case.

score 1 · Answer 5 · answered Nov 09 '11 at 16:35

I have been running into this problem frequently, and with more than just update queries (notably "INSERT INTO...SELECT FROM" queries). This is on Oracle 9i.

I found the the solution, so decided to find this related SO topic: In the connections string, set:

Pooling=False

in the connection string. A full, working connection string might look like:

DATA SOURCE=k19.MYDOMAIN.com/plldb;PERSIST SECURITY INFO=True;Pooling=False;USER ID=IT;Password=SECRET

Caveats: Setting pooling to false will require your query secure a new connection every time it is run. Queries that are run very frequently may experience a performance decrease compared to what they would have if ODP.NET were reliable. Considering the problem though, running a little slower is much better than hanging.

score 0 · Answer 6 · answered Jul 23 '09 at 15:22

0

seems like the database is waiting for a commit/rollback so it locks the row. I would suggest adding

int nRowsAffected = cmd.ExecuteNonQuery();
cmd.Commit();

answered Jul 23 '09 at 15:22

northpole

10,244
7
35
58

Oracle Update Hangs

6 Answers6

Linked