Oracle 更新挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1172378/
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
Oracle Update Hangs
提问by TimK
I'm having trouble with an Oracle update. The call to ExecuteNonQuery hangs indefinitely.
我在进行 Oracle 更新时遇到问题。对 ExecuteNonQuery 的调用无限期挂起。
The code:
编码：
using (OracleCommand cmd = new OracleCommand(dbData.SqlCommandStr, conn))
{
foreach (string colName in dbData.Values.Keys)
cmd.Parameters.Add(colName, dbData.Values[colName]);
cmd.CommandTimeout = txTimeout;
int nRowsAffected = cmd.ExecuteNonQuery();
}
CommandTimeout is being set to 5, and the parameters are being set to small integer values.
CommandTimeout 设置为 5，参数设置为小整数值。
The query:
查询：
UPDATE "BEN"."TABLE03" SET "COLUMN03"=:1,"COLUMN04"=:2 WHERE COLUMN05 > 0
The query runs quickly from sqlplus, and normally runs fast from my code, but every once in a while it hangs forever.
查询从 sqlplus 运行得很快，通常从我的代码中运行得很快，但每隔一段时间它就会永远挂起。
I ran a query on v$locked_object, and there's one record referring to this table, but I think that's the update that isn't completing.
我在 v$locked_object 上运行了一个查询，有一条记录引用了这个表，但我认为那是没有完成的更新。
There are two things I would like to know: What might cause the update to hang?
我想知道两件事： 什么可能导致更新挂起？
More importantly, why isn't an exception being thrown here? I would expect the call to wait five seconds, and then timeout.
更重要的是，为什么这里没有抛出异常？我希望呼叫等待五秒钟，然后超时。
回答by Kenneth
I'm bumping this due to its page rank in search results.
由于其在搜索结果中的页面排名，我对此提出异议。
In my case, it was because I had executed a query in SqlPlus, but I forgot to commit it. In this case, it was as Vincent stated: the row was locked in another session.
就我而言，那是因为我在 SqlPlus 中执行了一个查询，但我忘记提交了。在这种情况下，正如文森特所说：该行在另一个会话中被锁定。
Committing the SqlPlus update resolved the issue.
提交 SqlPlus 更新解决了该问题。
回答by Vincent Malgrat
When a simple update hangs it often means that you are blocked by another session. Oracle won't allow more than one transaction to update a row. Until a transaction has commited or rolled back its modifications it will lock the rows it has updated/deleted. This means that other session will have to wait if they want to modify the same rows.
当一个简单的更新挂起时，通常意味着您被另一个会话阻止。Oracle 不允许多个事务更新一行。在事务提交或回滚其修改之前，它将锁定已更新/删除的行。这意味着如果其他会话想要修改相同的行，他们将不得不等待。
You should SELECT ... FOR UPDATE NOWAIT before you UPDATE if you don't want to hang indefinetely.
如果您不想无限挂起，您应该在更新之前选择 ... FOR UPDATE NOWAIT。
回答by wlemond
I was having a similar issue that was being caused by a Sql command that hadn't been committed - I'm guessing the program crashed in the middle of one at some point.
我遇到了一个类似的问题，该问题是由尚未提交的 Sql 命令引起的 - 我猜该程序在某个时候崩溃了。
Here is how I fixed my issue:
这是我解决问题的方法：
First, open SqlPlus and do a commit to fix the issue.
首先，打开 SqlPlus 并提交以解决问题。
Next, change the code to commit the transaction or rollback if an exception occurrs. That will keep the problem from occurring again.
接下来，更改代码以在发生异常时提交事务或回滚。这将防止问题再次发生。
You could change your code to something like this:
您可以将代码更改为如下所示：
using (OracleTransaction transaction = conn.BeginTransaction())
{
using (OracleCommand cmd = new OracleCommand(dbData.SqlCommandStr, conn))
{
foreach (string colName in dbData.Values.Keys)
cmd.Parameters.Add(colName, dbData.Values[colName]);
cmd.CommandTimeout = txTimeout;
try
{
int nRowsAffected = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
}
回答by Dave Costa
You can see what event your session is waiting on by querying V$SESSION_WAIT (after identifying the SID of the session, probably by looking at V$SESSION). If the event is something like "enqueue", you are waiting on a lock held by another session, which does seem like a likely explanation in this case.
您可以通过查询 V$SESSION_WAIT（在识别会话的 SID 之后，可能通过查看 V$SESSION）来查看您的会话正在等待什么事件。如果事件类似于“入队”，则您正在等待另一个会话持有的锁，在这种情况下这似乎是一个可能的解释。
回答by Charles Burns
I have been running into this problem frequently, and with more than just update queries (notably "INSERT INTO...SELECT FROM" queries). This is on Oracle 9i.
我经常遇到这个问题，而且不仅仅是更新查询（特别是“INSERT INTO...SELECT FROM”查询）。这是在 Oracle 9i 上。
I found the the solution, so decided to find this related SO topic: In the connections string, set:
我找到了解决方案，所以决定找到这个相关的 SO 主题：在连接字符串中，设置：
Pooling=False
in the connection string. A full, working connection string might look like:
在连接字符串中。一个完整的、有效的连接字符串可能如下所示：
DATA SOURCE=k19.MYDOMAIN.com/plldb;PERSIST SECURITY INFO=True;Pooling=False;USER ID=IT;Password=SECRET
Caveats: Setting pooling to false will require your query secure a new connection every time it is run. Queries that are run very frequently may experience a performance decrease compared to what they would have if ODP.NET were reliable. Considering the problem though, running a little slower is much better than hanging.
注意事项：将 pooling 设置为 false 将要求您的查询在每次运行时保护新连接。与 ODP.NET 可靠的情况相比，非常频繁运行的查询可能会遇到性能下降。不过考虑到这个问题，跑得慢一点总比挂着要好得多。
回答by northpole
seems like the database is waiting for a commit/rollback so it locks the row. I would suggest adding
似乎数据库正在等待提交/回滚，因此它锁定了该行。我建议添加
int nRowsAffected = cmd.ExecuteNonQuery();
cmd.Commit();
