Oracle ExecuteNonQuery hangs on "Select for update nowait"

Question

I've been stuck on this for a few days now, and still can't figure out why :

I need to update a record in my Oracle database, before executing the Update query, I want to lock the data with a "SELECT x FOR UPDATE NOWAIT", but when calling the ExecuteNonQuery, my webservice hangs.

Executing the sql statement directly against the database works instantly. Here is the code :

                    OracleTransaction transact = OraDCS.BeginTransaction();
                    OracleCommand cmd = OraDCS.CreateCommand();
                    cmd.CommandText = string.Format("UPDATE FLIGHT_MANIFEST_PASSENGER SET ETAT_PASSAGER = '{2}',GATE_BOARDING = {0} WHERE REF_DOSSIER = '{1}'", ConfigurationManager.AppSettings["CodeMoyen"].ToString(), ref_dossier, etatPax);

                    OracleCommand blq = OraDCS.CreateCommand();
                    blq.CommandText = string.Format("SELECT * FROM FLIGHT_MANIFEST_PASSENGER WHERE REF_DOSSIER = '{0}' FOR UPDATE NOWAIT", ref_dossier);

                    bool blqOK = false;
                    try
                    {
                        blq.ExecuteNonQuery();
                        blqOK = true;
                    }
                    catch (Exception ex)
                    {
                        LogThreadSafe.Instance.Info("LanceSQL Error : " + blq.CommandText + " " + ex.Message);
                    }

                    if (blqOK)
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                            transact.Commit();
                        }
                        catch (Exception ex)
                        {
                            transact.Rollback();
                            LogThreadSafe.Instance.Info("LanceSQL Error : " + blq.CommandText + " " + ex.Message);
                        }

                    }
                    else
                        transact.Rollback();

The lock is actually performed on the databse, but the program still hangs at this line :

blq.ExecuteNonQuery();

EDIT : Even after linking the transaction to the command I still experience the freeze. However, when I deploy my webservice on the server, and test it "live" rather than in debug on my dev machine, it works, any ideas?

Not the answer to your question, but note that you have a copy/paste typo in your second catch-block - you are issuing the exception message from the *blq* command instead of *cmd*. — Eight-Bit Guru, May 12 '14 at 13:49

score 0 · Answer 1 · answered May 12 '14 at 13:09

i usually do if i must use a transaction:

OracleCommand cmd = OraDCS.CreateCommand();
OracleTransaction transact=cmd.Connection.BeginTransaction();
cmd.Transaction=transact;
cmd.CommandText = string.Format("UPDATE FLIGHT_MANIFEST_PASSENGER SET ETAT_PASSAGER = '{2}',GATE_BOARDING {0}WHERE REF_DOSSIER = '{1}'", ConfigurationManager.AppSettings["CodeMoyen"].ToString(), ref_dossier, etatPax);

maybe your transaction doesnt have a connection

Rik · Accepted Answer · 2014-05-12T13:18:13.157

0

You need to associate the commands with the transaction:

cmd.Transaction = transact;
blq.Transaction = transact;

I also highly recommend you don't build your query text by string concatenation, but use parameters instead in order to prevent SQL injection. For example:

blq.CommandText = "SELECT * FROM FLIGHT_MANIFEST_PASSENGER WHERE REF_DOSSIER = :pDossier FOR UPDATE NOWAIT";
blq.Parameters.Add(new OracleParameter("pDossier", ref_dossier));

edited May 12 '14 at 13:18

answered May 12 '14 at 13:09

Rik

28,507
14
48
67

I still have the problem even after associating the command with the transaction, but I don't think the problem comes from the code. When I deploy my webservice on the server, and test it "live" rather than in debug on my dev machine, it works and I don't experience the freeze. Do you have any clues why? Thanks for your input btw. – JuFra May 12 '14 at 13:39
@JuFra Maybe there's another transaction (not specifying `NOWAIT`) blocking said rows? – Rik May 12 '14 at 14:28
No there isn't, I have a software to monitor locks on my database and there are none. I think maybe it could be related to the Oracle Data Access provider ? Or my instant client, some parameter somewhere? I'll mark your answer as the best answer anyway since it was useful and my problem isn't code related as I thought it was. Thanks – JuFra May 13 '14 at 07:05

Oracle ExecuteNonQuery hangs on "Select for update nowait"

2 Answers2