Why does this update lock the row in Oracle 10?

Question

Here is the code (with server/passwords etc removed)

  public int SetUploadedInESIDatabase(string ID)
        {
            using (var oOracleConn = new OracleConnection())
            {
                oOracleConn.ConnectionString =
                    @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip>)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<dbname>)));User Id=<user>;Password=<password>;";
                var cmd =
                    new OracleCommand(
                        "UPDATE FOO_ACCESS SET PIMAGE ='-1' WHERE CODE= '" + ID + "'", oOracleConn);

                oOracleConn.Open();



                return  cmd.ExecuteNonQuery();

            }
        }

The effect of this code is it never gets past the return statement. No error is returned (even overnight)

I am not a database expert but our hard pressed DBA says that the connection was being locked (or the row possibly...) he killed the locking connection but still when I run the code it locks up.

Am I doing it wrong(tm) with regards to asking Oracle to update a row?

I realise I should be using a parametrised query but I had an issue with that and needed simple things! If I copy the built command out of the cmd. with the debugger and run it using SQL Developer then it works (though sometimes it locks up too)

I can select from the database at will.

I am not sure if this is a normal thing or something to do with our environment, so any help is gladly accepted!

Answer 1

• Find out what exactly is locked (table, row, etc)

• Can you execute the statement from sqlplus or sqldeveloper using the same credentials?

• Are any triggers attached to the table FOO_ACCESS?

Answer 2

Are you sure that the parameter studentID is truly just an ID? What if it was a malformed bit of SQL that someone tries to inject?

It's entirely possible that som unsanitized input has made it's way into studentID - and causes your query to do something other than what you expect.

For instance, if studentID = "'; DROP ALL TABLES; --" you may have a problem...

Using string concatentation as a means to create a SQL statement is an unsafe practice - and one that is entirely unecessary. It is quite easy to use parameters in SQL commands in .NET, which makes the SQL less susceptible to injection attacks and also improves their performance (by reducing the need to perform statement parsing).

Here's an example that uses parameters:

var cmd = new OracleCommand( 
       "UPDATE FOO_ACCESS SET PIMAGE = '-1' WHERE CODE = :code", oracleConn );
cmd.Parametes.Add( ":code", studentID );

cmd.ExecuteNonQuery();

Beyond that, you can investigate what is causing your query to perform poorly by using the V$XXX tables in Oracle to explore what is going on. If you think you have a lock, you can query the v$lock table to see which tables are locked by which sessions.

Answer 3

If one session issues this update and doesn't commit or roll back, the row is locked by that session until it does (commit or roll back). Could that be what is happening to you?

Answer 4

Have you tried opening the connection before creating the command object?

Answer 5

Oracle will lock a row if multiple writers try to touch it at the same time. Is some other code trying to modify the row or the table at the same time? Have you perhaps executed a SELECT FOR UPDATE statement in another open connection?

Answer 6

Have you tried specifying the isolation mode by creating a transaction e.g.

using(OracleTransaction transaction = oOracleConn.BeginTransaction(IsolationLevel.RepeatableRead)
{

cmd.Transaction = transaction

return  cmd.ExecuteNonQuery();
}

Answer 7

I'm curious about one thing. Can you try changing your return statement so that it's outside the using block?

i.e. instead of:

using (..snip...) {

  return  cmd.ExecuteNonQuery();
}

try

int rv;
using () {

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

Answer 8

Combining @Tony's and @Panagiotis answers, and expanding:

Where do you commit the UPDATE done in this code? Are you sure it's getting done?

Could this be your scenario:

1. You issue the above UPDATE, but never commit.
2. You don't see your changes, so you try again. Now it hangs.

The row(s) affected by an UPDATE are locked against further updates until committed or rolled back. If you never explicitly commit or rollback, subsequent updates will hang.

If you want to avoid the hang in your code, then execute a

SELECT... FOR UPDATE NOWAIT;

before you do the UPDATE. If the record is locked, the select will return an error, which you can catch and process.

Have your DBA try this query (Tom Kyte be thanked):

select
      (select username from v$session where sid=a.sid) blocker,
       a.sid,
      ' is blocking ',
       (select username from v$session where sid=b.sid) blockee,
           b.sid
  from v$lock a join v$lock b on (a.id1 = b.id1 and a.id2 = b.id2)
 where a.block = 1
   and b.request > 0;

To see all the queued up blocks on sessions.

Answer 9

It seems that the problem was me not quite understanding my tools.

I believe that SQL Developer was creating the lock at some point, and then when I ran my web application to update the same row it was being locked. In my frustration and ignorance I force closed SQL Developer leaving a lock stuck in the database, and I was unable to clear it without DBA super powers.

Having now had the lock cleared and closed all running copies of SQL Developer cleanly the code as I first posted it now works. (phew!)

Thanks for all your help, especially the idea of SELECT FOR UPDATE to see if my update is going to be refused before I issue it :)