0

I was trying to run an insert query on table in my Oracle database. below is the test i'm doing.

  1. First lock the table on which i want to do an insert from a SQL Developer session

    lock table RANDOM_TABLE in exclusive mode nowait
  2. Try running an insert using DbCommand.ExecuteNonQuery
  3. I've had the CommandTimeout property set to 60 seconds

Even after completion of 60 seconds the control did not return but when i unlocked the table, it is doing the insert and also throwing an exception.

I figured that, .NET is properly throwing an exception when the command timeout expires but control was not returning from native code until the table is unlocked. How to avoid indefinite wait in this situation?

 Public Shared Function ExecuteNonQuery(ByVal ConnectionString As String, ByVal factory As DbProviderFactory, ByVal sql As String) As Integer

    Dim rowsAffected As Integer = -1

    Using conn As DbConnection = factory.CreateConnection
        conn.ConnectionString = ConnectionString
        conn.Open()
        If conn.State.Equals(ConnectionState.Open) Then

            Try
                'This works
                Dim command As DbCommand = conn.CreateCommand
                command.CommandText = sql
                command.CommandType = CommandType.Text
                command.CommandTimeout = 60

                '**********This blocks and never returns.
                rowsAffected = command.ExecuteNonQuery()
            Catch ex As Exception
                Debug.Print(ex.Message)
            End Try

        End If
    End Using
    Return rowsAffected
End Function
Tulasiram
  • 71
  • 8
  • _1.First lock the table on which i want to do an insert_. Is it possible to [edit](http://stackoverflow.com/posts/41510536/edit) your question to include the code for this? Also _it is doing the insert and also throwing an exception._ what is the exception? Does it hang when you execute the command directly on the database rather than through code? – Bugs Jan 06 '17 at 17:13
  • 1
    If you lock your table from somewhere else, no wonder it is not working. It is waiting for lock to release. You probably don't need any lock before insert. But if you do, lock it in the same transaction as insert. Why do you need lock? – T.S. Jan 06 '17 at 17:26
  • Either pass the transaction to your function and execute the insert on it, or "lock" the table from within your function as part of the same transaction – soohoonigan Jan 06 '17 at 17:42
  • Jinx88909, 1. I was locking the table from a different session, added some more details to my question. 2. Yes, the moment i unlocked the table it is doing the insert but at the same time .NET throwing an exception that says user cancelled the operation. 3. No at least from what i've tested from SQL Developer with two different sessions – Tulasiram Jan 06 '17 at 17:53
  • @T.S. there is one such use case in our system, some down stream applications do something with that table and it's likely that it can get locked at times. we want to be robust in those scenarios – Tulasiram Jan 06 '17 at 19:14
  • Well, you want to have 2 things that can't happen together with `exclusive` lock. There are different locks out there. Try `SHARE` lock. Refer to this http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_9015.htm I am not going to dip into your design. You've go your answer as far as I am concerned. – T.S. Jan 06 '17 at 19:20
  • @ T.S. I understand why it is hanging, the problem here is how much time does it wait? shouldn't we expect it to quit waiting after the specified command timeout expires? – Tulasiram Jan 06 '17 at 19:31
  • Looks like it does quit, since you get the error. Your table is locked and after 60 seconds your command object throws an error because it can't perform the actual command, because it has no access to the table or row. – T.S. Jan 06 '17 at 19:33
  • Did you try the same exact code? I don't see it quitting after the command timeout until i unlock the table – Tulasiram Jan 06 '17 at 21:00

0 Answers0