TransactionScope and Error: ORA-02049

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.

So it sounds like you must have two transactions competing for a row lock.

Just brainstorming here, but if dbValueLookup.Count = 0, then you will call addData (which sounds like it does an INSERT?), but you will not call scope.Complete() to commit your transaction.

I'm not sure if the End Using will always commit the transaction or not.

Do you really need to create the TransactionScope on every iteration of the loop? Why not create one transaction, do all your updates/inserts, then commit once?