ORA-08177：无法序列化此事务的访问 [英] ORA-08177: can't serialize access for this transaction

问题描述

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

  Dim connection As OracleConnection = Nothing 
 Dim transaction As OracleTransaction = $ none 
 
尝试
 connection =新的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 
 
尝试
 inputStream.Read（input，0，fileLength）
最后
如果inputStream IsNot Nothing然后inputStream.Close（）
结束尝试
 
 Dim deleteSql As String =DELETE FROM TABLE1 WHERE Version ='v1'
 
 Dim cmd作为新的OracleCommand（deleteSql，连接，事务）
 cmd.ExecuteNonQuery（）
 
 Dim i nsertQuery 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 
如果事务IsNot Nothing然后transaction.Rollback（）
投掷
最后
如果事务IsNot Nothing Then transaction.Dispose（）
如果连接IsNot Nothing AndAlso connection.State<> ConnectionState.Closed然后connection.Close（）
结束尝试
  

要注意的重要事项（（我不知道他们是否连接），但是如果我从我的机器上卸载最新的Windows更新，我不会面临这个问题。

有没有人面对这个或有任何线索这里发生了什么？

编辑： -

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

其他详细信息（不知道是否有所作为）

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

如果我卸载最新的Windows更新，我没有看到这个问题。 （KB963027，KB967190，KB959426，KB960225，KB960803，KB952004，KB956572，KB958687，KB958690，KB958481，KB958483，KB943729）

解决方案

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

交易不会回滚但是提交，您将收到此错误。

该场景似乎如下：

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

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

   Bob 的事务等待，因为 Alice code> Version ='v1'

   
   
   
   
   

   • Alice 提交交易

   
   

   • Bob 的交易失败，无法序列化访问

   
   

要解决这个问题，设置 TRANSACTION ISOLATION LEVEL 至 READ COMMITTED ：

  transaction = connection.BeginTransaction（IsolationLevel.ReadCommitted）
  

在这种情况下， Bob 的查询将在 Alice 提交更改后重新发布，如 Bob 的交易在 Alice 的提交之后开始。

更新

请问您能否发布连结？

为此，连接后的这个命令：

 （新的OracleCommand（ALTER SESSION SET SQL_TRACE = TRUE，连接，事务））ExecuteNonQuery （）; 
  

，然后查看 $ ORACLE_HOME\admin\udump 新鲜的 *。trc 文件

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)

解决方案

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_HOME\admin\udump for a fresh *.trc file

这篇关于ORA-08177：无法序列化此事务的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！