I am getting challenge with an Oracle update. The phone call to ExecuteNonQuery dangles 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 has been set to five, and also the parameters are now being set to small integer values.

The query:


The query runs rapidly from sqlplus, and normally runs fast from the code, but every every now and then it dangles forever.

I went a question on v$locked_object, and there is one record mentioning for this table, however i think this is the update that is not finishing.

You will find a couple of things I must know: What could potentially cause the update to hold?

More to the point, why is not the best being tossed here? I'd expect the phone call to hold back five seconds, after which timeout.

Whenever a simple update dangles it frequently means that you're blocked by another session. Oracle will not allow several transaction to update a row. Until a transaction has commited or folded back its modifications it'll lock the rows it's up-to-date/erased. Which means that other session will need to wait if they would like to customize the same rows.

You need to Choose ... FOR UPDATE NOWAIT before you decide to UPDATE if you won't want to hang indefinetely.

You can observe what event your session delays on by querying V$SESSION_WAIT (after determining the SID from the session, most likely by searching at V$SESSION). When the event is one thing like "enqueue", you're waiting on the lock held by another session, which does appear just like a likely explanation within this situation.

appears such as the database delays for any commit/rollback therefore it locks the row. I recommend adding

int nRowsAffected = cmd.ExecuteNonQuery();

I am thumping this because of its page ranking searching results.

During my situation, it had been because I'd performed a question in SqlPlus, however i didn't remember to commit it. Within this situation, it had been as Vincent mentioned: the row was kept in another session.

Carrying out the SqlPlus update resolved the problem.

I've been encountering this issue frequently, and using more than just update queries (particularly "Place INTO...Choose FROM" queries). This really is on Oracle 9i.

I discovered the the answer, so made the decision to locate this related SO subject: Within the connections string, set:


within the connection string. A complete, working connection string might seem like:


Caveats: Setting pooling to false will need your query secure a brand new connection each time it's run. Queries which are run often can experience a performance decrease in comparison as to the they'd have if ODP.Internet were reliable. Thinking about the issue though, managing a little reduced is way better than hanging.