I have the following routine:
For j = 1 To NumItems
dbValueLookup.Load(j)
Using scope As New TransactionScope()
For i = firstIndex To lastIndex
'dbValueLookup is basically just a Dictionary of items already in the DB
If dbValueLookup.ContainsKey(i) Then
'updateData is a subroutine that updates this row with new data
updateData(j,i)
rowsUpdated = rowsUpdated + 1
dbValueLookup.Remove(i)
Else
'updateData is a subroutine that adds a new row to DB
addData(j,i)
rowsAdded = rowsAdded + 1
End If
Next
If dbValueLookup.Count = 0 Then
'This commits the transaction - records will be updated when End Using is reached
scope.Complete()
If rowsAdded + rowsUpdated > 0 Then
ShowMessage("Records Updated: " + rowsUpdated.ToString() + " Records Added: " + rowsAdded.ToString())
End If
Else
'We are left with data from the database that was not updated. This is a problem, so we don't "Complete" the scope.
'This will result in a rollback.
ShowWarningMessage("Incomplete Data for " + i.ToString())
End If
End Using
Next
Running this against both our production and test Oracle 11g database sporadically (or if there is a pattern, I haven't found it yet) generates the Oracle Error: ORA-02049: timeout: distributed transaction waiting for lock
Since this is the only process running against the test database, there shouldn't be any issue with different users competing for a lock.
Any ideas what might be causing this error?
Thanks in advance.