oracle ORA-08177: 无法序列化此事务的访问权限

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

提问by MOZILLA

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.

我有一个使用 ADO.NET 的非常简单的代码，它抛出 ORA-08177 异常。我不确定这有什么问题。我正在安装了 oracle 32 位客户端的 windows vista 机器上尝试这个。我的visual studio 编译选项设置为x86 平台。

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.

需要注意的重要事项：（我不确定它们是否已连接）但如果我从我的机器上卸载最新的 Windows 更新，我不会遇到这个问题。

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.

我有一些进展，我发现只有当我们有问题的 blob 列类型时才会出现这个问题。对于简单的列，它工作正常。

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.

我正在 64 位 Windows Vista 商务机上工作。我已经为 windows vista 安装了 32 位 oracle 客户端（因为 64 位 oracle 客户端在 vista 上不起作用）。我正在 Visual Studio 中为 x86（32 位环境）编译我的项目。这是一个控制台应用程序，我知道此时没有其他人正在访问数据库。所以不能有多个交易。

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)

如果我卸载最新的 Windows 更新，我就看不到这个问题。(KB963027, KB967190, KB959426, KB960225, KB960803, KB952004, KB956572, KB958687, KB958690, KB958481, KB9599483)

回答by Quassnoi

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

您正在使用一个可序列化的事务，它等待其他一些事务将同一个表锁定到ROLLBACK.

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

如果这个其他事务不回滚而是提交，您将收到此错误。

The scenario seems to be as following:

场景似乎如下：

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

   • Bobopens his session which calls DELETE FROM TABLE1 WHERE Version = 'v1'after Alicedid it but before she commited.

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

   • Alicecommits her transaction

   • Bob's transaction fails with Cannot serialize access

1. Alice打开她的浏览器会话，调用 DELETE FROM TABLE1 WHERE Version = 'v1'

   • Bob打开他的会话，DELETE FROM TABLE1 WHERE Version = 'v1'在Alice完成之后但在她提交之前调用。

   Bob的事务等待，因为用Alice锁定了行Version = 'v1'

   • Alice提交她的交易

   • Bob的交易失败 Cannot serialize access

To work around this, set TRANSACTION ISOLATION LEVELto READ COMMITTED:

要解决此问题，请设置TRANSACTION ISOLATION LEVEL为READ COMMITTED：

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

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

在这种情况下，Bob的查询将在Alice提交她的更改后重新发出，就好像Bob的事务是在提交的事务之后启动的Alice。

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_HOME\admin\udumpfor a fresh *.trcfile

，然后寻找$ORACLE_HOME\admin\udump一个新*.trc文件