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.

Answer 1

Hi TimK

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.

Answer 2

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();

Answer 3

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.