oracle 仅在一个活动会话中随机获得 ORA-08177
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2326750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Randomly getting ORA-08177 with only one active session
提问by Denis K
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.
我正在运行一个创建表格然后插入一些数据的程序。
这是访问数据库的唯一程序。
我随机收到 ORA-08177。
实际代码有些复杂，但我已经编写了一个简单的程序来重现这种行为。
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++;
}
}
}
}
This code runs infinite cycle.
On each iteration it performs the following actions 10 times:
此代码运行无限循环。
在每次迭代中，它执行以下操作 10 次：
Open session
Insert 100 rows with random data
Close session
Displays a message saying that no error occured
打开会话
插入 100 行随机数据
关闭会话
显示一条消息说没有发生错误
If error occurs, the exception is caught and its message is printed and then the next iteration is executed.
如果发生错误，则捕获异常并打印其消息，然后执行下一次迭代。
Here is sample output. As you can see ORA-08177 is interleaving with successful interations randomly.
这是示例输出。如您所见，ORA-08177 随机与成功的交互交错。
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
I'm running Oracle 11.1.0.6.0 and using ODP.NET 2.111.6.20.
Changing isolation level to ReadCommitedfixes the problem, but I really want to run this at Serializablelevel.
Looks like I'm not alonewith this problem, but answer was not given, so I'm asking again.
What am I doing wrong and how could I fix this?
我正在运行 Oracle 11.1.0.6.0 并使用 ODP.NET 2.111.6.20。
更改隔离级别以ReadCommited解决问题，但我真的很想在Serializable级别上运行它。
看起来我不是一个人遇到这个问题，但没有给出答案，所以我再问一次。
我做错了什么，我该如何解决？
edit by APC
APC编辑
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.
为防止其他人发现错误的树，发布的代码示例只是 ORA-8177 错误的生成器。显然实际的代码是不同的；具体来说，删除和重新创建表是一个红鲱鱼。
采纳答案by Denis K
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.
在评论中，用户 Gary 发布了一个线程链接，解释了这种奇怪的行为。很快，有时在索引重构期间撤消数据变得不可用。任何在可序列化隔离级别运行并请求与此索引以某种方式相关的数据的事务都将获得 ORA-08177。这是 Oracle 的一个半缺陷半特性。ROWDEPENDENCIES 减少了出现此错误的机会。对于我的应用程序，我只是切换到 ReadCommited 级别来上传大数据。似乎没有其他方法可以完全摆脱这个问题。
Thanks, Gary, I've upvoted your answer to other question.
谢谢，加里，我赞成你对其他问题的回答。
回答by APC
Total rewrite (having barked up the wrong tree the first time around).
完全重写（第一次叫错了树）。
The SERIALIZABLE isolation level grabs a slot in the Interested Transactions List. If Oracle cannot get a slot then it hurls ORA-8177. The number of available ITL slots is controlled by INITRANS and MAXTRANS. According to the documentation:
SERIALIZABLE 隔离级别在感兴趣的事务列表中占据一个位置。如果 Oracle 无法获得插槽，则会抛出 ORA-8177。可用 ITL 插槽的数量由 INITRANS 和 MAXTRANS 控制。根据文档：
To use serializable mode, INITRANS must be set to at least 3.
要使用可序列化模式，INITRANS 必须至少设置为 3。
This must be set for both the table and its indexes. So, what are your INITRANS settings? Certainly your sample code uses the default value (1 for tables, 2 for indexes).
这必须为表及其索引设置。那么，您的 INITRANS 设置是什么？当然，您的示例代码使用默认值（表为 1，索引为 2）。
