How to do Row Locking

[DaGoat]

Ok,
To summarize, I am trying to Lock a record so I can get the value of a column and then edit that value without other users grabbing the same value. To make this happen I would use the “FOR UPDATE clause in my Select Statement to lock the record (Using System.Data.OracleClient namespace).

Now I have converted the project to use your dotConnect for Oracle. How can I do the same with your product? I tried to use the “FOR UPDATE” clause and I get and error “ORA-01002: fetch out of sequence”.

Steps I Need:
1. Get the Value of the column in a table and lock it so no one else can obtain the same record or value.
2. Update the value.
3. Basically done. Release the lock and go on about our merry way.

Code I would like to Run: (VB.NET)

Dim sqlText As String = _
"Select VALUETOCHANGE, PRIMARYCOLUMN " & _
"From MYTABLE " & _
"Where PRIMARYCOLUMN = 'SomeValue' FOR UPDATE "

Dim updateText As String = _
"Update MYTABLE Set " & _
"VALUETOCHANGE= (VALUETOCHANGE +1) " & _
"Where PRIMARYCOLUMN = 'SomeValue'"

Dim value As Integer
Dim dt As New DataTable

Try
Using cn As New OracleConnection(myConnectionString)
cn.Open()
Using cm As New OracleCommand(sqlText, cn)
Using da As New OracleDataAdapter(cm)
da.Fill(dt)
value = CInt(dt.Rows(0)(2))
cm.CommandText = updateText
cm.ExecuteNonQuery()
End Using
End Using
End Using
Catch ex As Exception
Throw
End Try

Return value

[AndreyR]

Thank you for the report. We have reproduced the error and now we are investigating it.

[DaGoat]

Ok, So is there another way of locking a record so others have to wait until it is released? Or is the FOR UPDATE the only way?

[Shalex]

It is necessary to use transaction - begin it before performing the steps you have mentioned, and commit the transaction after. Transaction usage is needed because a commit will occur after every execution of the command, but lock by the FOR UPDATE clause will be valid only for the next commit.
So, please try using this code.

Code: Select all

      Dim sqlText As String = _
      "Select VALUETOCHANGE, PRIMARYCOLUMN " & _
      "From MYTABLE " & _
      "Where PRIMARYCOLUMN = 'SomeValue' FOR UPDATE "

      Dim updateText As String = _
      "Update MYTABLE Set " & _
      "VALUETOCHANGE= (VALUETOCHANGE +1) " & _
      "Where PRIMARYCOLUMN = 'SomeValue'"

      Dim value As Integer
      Dim dt As New DataTable
      Dim transaction As OracleTransaction 
      transaction = Nothing

      Try
          Using cn As New OracleConnection(myConnectionString)
              cn.Open()
              Using cm As New OracleCommand(sqlText, cn)
                  transaction = cn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
                  cm.Transaction = transaction
                  Using da As New OracleDataAdapter(cm)
                      da.Fill(dt)
                      value = CInt(dt.Rows(0)(2))
                      cm.CommandText = updateText
                      cm.ExecuteNonQuery()
                      transaction.Commit()
                  End Using
              End Using
          End Using
      Catch ex As Exception
          If Not (transaction Is Nothing) Then transaction.Rollback()
          Throw
      End Try

[DaGoat]

Good Deal, that did the trick.

Appreciated it.