仅通过一个活动会话随机获得ORA-08177 [英] Randomly getting ORA-08177 with only one active session
问题描述
我正在运行一个程序，该程序创建一个表，然后插入一些数据.
这是唯一访问数据库的程序.
我随机得到ORA-08177.
实际的代码有些复杂，但是我编写了一个简单的程序来重现此行为.
I'm running a program that creates a table and then inserts some data.
This is the only program that accesses the database.
I'm getting ORA-08177 randomly.
Actual code is somewhat complex, but I've written a simple program that reproduces this behavior.
using System;
using System.Data;
using Oracle.DataAccess.Client;
namespace orabug
{
class Program
{
private const string ConnectionString = ""; // Valid connection string here
// Recreates the table
private static void Recreate()
{
using (var connection = new OracleConnection(ConnectionString)) {
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = @"
declare
table_count binary_integer;
begin
select count(*) into table_count from sys.user_tables where table_name = 'TESTTABLE';
if table_count > 0 then
execute immediate 'drop table TestTable purge';
end if;
execute immediate 'create table TestTable(id nvarchar2(32) primary key)';
end;";
command.ExecuteNonQuery();
}
connection.Close();
}
}
// Opens session sessionCount times, inserts insertCount rows in each session.
private static void Insert(int sessionCount, int insertCount)
{
for (int sessionNumber = 0; sessionNumber < sessionCount; sessionNumber++)
using (var connection = new OracleConnection(ConnectionString)) {
connection.Open();
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable)) {
for (int insertNumber = 0; insertNumber < insertCount; insertNumber++)
using (var command = connection.CreateCommand()) {
command.BindByName = true;
command.CommandText = "insert into TestTable (id) values(:id)";
var id = Guid.NewGuid().ToString("N");
var parameter = new OracleParameter("id", OracleDbType.NVarchar2) {Value = id};
command.Parameters.Add(parameter);
command.Transaction = transaction;
command.ExecuteNonQuery();
}
transaction.Commit();
}
connection.Close();
}
}
static void Main(string[] args)
{
int iteration = 0;
while (true) {
Console.WriteLine("Running iteration: {0}", iteration);
try {
Recreate();
Insert(10, 100);
Console.WriteLine("No error");
}
catch (Exception exception) {
Console.WriteLine(exception.Message);
}
iteration++;
}
}
}
}
此代码无限循环运行.
在每次迭代中，它都会执行10次以下操作:
This code runs infinite cycle.
On each iteration it performs the following actions 10 times:
-
打开会话
Open session
使用随机数据插入100行
Insert 100 rows with random data
关闭会话
显示一条消息，指出没有发生错误
Displays a message saying that no error occured
如果发生错误，则捕获异常并打印其消息，然后 然后执行下一个迭代.
If error occurs, the exception is caught and its message is printed and then the next iteration is executed.
这是示例输出.如您所见，ORA-08177随机交织成功的交际.
Here is sample output. As you can see ORA-08177 is interleaving with successful interations randomly.
Running iteration: 1
No error
Running iteration: 2
ORA-08177: can't serialize access for this transaction
Running iteration: 3
ORA-08177: can't serialize access for this transaction
Running iteration: 4
ORA-08177: can't serialize access for this transaction
Running iteration: 5
ORA-08177: can't serialize access for this transaction
Running iteration: 6
ORA-08177: can't serialize access for this transaction
Running iteration: 7
No error
Running iteration: 8
No error
Running iteration: 9
ORA-08177: can't serialize access for this transaction
Running iteration: 10
ORA-08177: can't serialize access for this transaction
Running iteration: 11
ORA-08177: can't serialize access for this transaction
Running iteration: 12
ORA-08177: can't serialize access for this transaction
Running iteration: 13
ORA-08177: can't serialize access for this transaction
Running iteration: 14
ORA-08177: can't serialize access for this transaction
Running iteration: 15
ORA-08177: can't serialize access for this transaction
Running iteration: 16
ORA-08177: can't serialize access for this transaction
Running iteration: 17
No error
Running iteration: 18
No error
Running iteration: 19
ORA-08177: can't serialize access for this transaction
Running iteration: 20
No error
我正在运行Oracle 11.1.0.6.0并使用ODP.NET 2.111.6.20.
将隔离级别更改为ReadCommited可以解决此问题，但是我真的想在Serializable级别上运行它.
看起来像我并不孤单
这个问题，但是没有给出答案，所以我再次询问.
我在做什么错，我该如何解决?
I'm running Oracle 11.1.0.6.0 and using ODP.NET 2.111.6.20.
Changing isolation level to ReadCommited fixes the problem, but I really want to run this at Serializable level.
Looks like I'm not alone
with this problem, but answer was not given, so I'm asking again.
What am I doing wrong and how could I fix this?
通过APC编辑
为防止其他人吠叫错误的树，发布的代码示例仅是ORA-8177错误的生成器.显然，实际的代码是不同的.具体地说，删除和重新创建表是一个红色的鲱鱼.
To prevent anybody else barking up the wrong tree, the posted code sample is just a generator of ORA-8177 errors. Apparently the actual code is different; specifically, the dropping and recreating of tables is a red herring.
推荐答案
用户Gary在评论中发布了指向线程的链接，该链接解释了这种奇怪的行为. 很快，有时在索引重组期间，撤消数据变得不可用. 以可序列化隔离级别运行并请求与该索引某种程度上相关的数据的任何事务都将获得ORA-08177.这是Oracle的一个半bug半功能. ROWDEPENDENCIES减少了出现此错误的机会. 对于我的应用程序，我只是切换到ReadCommited级别以上传大数据. 似乎没有其他方法可以完全解决此问题.
In comments user Gary posted a link to thread that explains this strange behavior. Shortly, sometimes during index restructurization undo data becomes unavailable. Any transaction that runs at serializable isolation level and requests the data that is somehow related with this index will get ORA-08177. This is a half-bug half-feature of Oracle. ROWDEPENDENCIES reduces the chance of getting this error. For my application I've simply switched to ReadCommited level for large data uploads. It seems that there is no other way to escape this problem completely.
谢谢你，加里，我对你对其他问题的回答表示赞成.
Thanks, Gary, I've upvoted your answer to other question.
这篇关于仅通过一个活动会话随机获得ORA-08177的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
