oracle - ORA-08177: can't serialize access for this transaction

Question

I have a very simple code using ADO.NET which throws ORA-08177 exception. I am not sure what's wrong with this. I am trying this on a windows vista machine which has oracle 32 bit client installed. My compile option for visual studio is set to x86 platform.

Dim connection As OracleConnection = Nothing
Dim transaction As OracleTransaction = Nothing

Try
    connection = New OracleConnection("Data Source=ora10;User Id=userid;Password=passwd;")
    connection.Open()

    transaction = connection.BeginTransaction(IsolationLevel.Serializable)

    Dim inputStream As New System.IO.FileStream("Dummy.xls", IO.FileMode.Open)
    Dim fileLength As Integer = CType(inputStream.Length, Integer)
    Dim input(fileLength) As Byte

    Try
        inputStream.Read(input, 0, fileLength)
    Finally
        If inputStream IsNot Nothing Then inputStream.Close()
    End Try

    Dim deleteSql As String = "DELETE FROM TABLE1 WHERE Version = 'v1' "

    Dim cmd As New OracleCommand(deleteSql, connection, transaction)
    cmd.ExecuteNonQuery()

    Dim insertQuery As String = "INSERT INTO TABLE1 (VERSION, DATA) VALUES (:VERSION, :DATA) "
    Dim insertCmd As OracleCommand = New OracleCommand(insertQuery, connection, transaction)
    insertCmd.Parameters.Clear()
    insertCmd.CommandType = Data.CommandType.Text
    insertCmd.Parameters.AddWithValue(":VERSION", "v1")
    insertCmd.Parameters.AddWithValue(":DATA", input)

    insertCmd.ExecuteNonQuery()
    transaction.Commit()

Catch
    If transaction IsNot Nothing Then transaction.Rollback()
    Throw
Finally
    If transaction IsNot Nothing Then transaction.Dispose()
    If connection IsNot Nothing AndAlso connection.State <> ConnectionState.Closed Then connection.Close()
End Try

Important thing to note: (I am not sure if they are connected) but I do not face this problem if I uninstall latest windows updates from my machine.

Has anyone faced this or have any clue about what is going on here?

Edit:-

I have some progress where I have found out that this problem occurs only when we have blob column type in question. for simple columns it works fine.

Other details (not sure if that makes a difference)

I am working on 64 bit windows vista business machine. I have installed 32 bit oracle client for windows vista (since 64 bit oracle client does not work on vista). I am compiling my project for a x86 (32 bit environment) in visual studio. And this is a console application and I know that nobody else is hitting the database at this time. so there cannot be multiple transactions.

And I do not see this problem if I uninstall latest windows update. (KB963027, KB967190, KB959426, KB960225, KB960803, KB952004, KB956572, KB958687, KB958690, KB958481, KB958483, KB943729)

Answer 1

You are using a serializable transaction which waits for some other transaction locking the same table to ROLLBACK.

If this other transaction does not rollback but commits instead, you will get this error.

The scenario seems to be as following:

1. Alice opens her browser session which calls DELETE FROM TABLE1 WHERE Version = 'v1'

   • Bob opens his session which calls DELETE FROM TABLE1 WHERE Version = 'v1' after Alice did it but before she commited.

   Bob's transaction waits since Alice locked the rows with Version = 'v1'

   • Alice commits her transaction

   • Bob's transaction fails with Cannot serialize access

To work around this, set TRANSACTION ISOLATION LEVEL to READ COMMITTED:

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

In this case, Bob's query will be reissued after Alice commits her changes, as if Bob's transaction were started after Alice's one was committed.

Update

Could you please post a trace of your connection?

To do this, issue this command right after connecting:

(New OracleCommand("ALTER SESSION SET SQL_TRACE=TRUE", connection, transaction)).ExecuteNonQuery();

, then look in $ORACLE_HOMEadminudump for a fresh *.trc file