带有OracleDependency的OracleCommand永远等待 [英] OracleCommand with OracleDependency waiting forever
问题描述
注意:有关这里没有解决办法
请记住，我不是Oracle专家，也不是针对Oracle的编程专家.这是我的测试环境.我在架构STVM中只有一个表，称为STVM_NOTIFICATION.看起来是这样的:
Keep in mind that I am no expert on Oracle or programming against Oracle. This is my test environment. I have a single table in the schema STVM called STVM_NOTIFICATION. This is what it looks like:
CREATE TABLE STVM_NOTIFICATION
(
"ID" NUMBER NOT NULL,
"PROPERTYNAME" VARCHAR2(16 BYTE) NOT NULL,
"PROPERTYVALUE" VARCHAR2(16 BYTE) NOT NULL,
"ACTION" VARCHAR2(32 BYTE) NOT NULL,
"POSTDATE" TIMESTAMP (6) NOT NULL,
"SENT" CHAR(1 BYTE) NOT NULL,
ADD CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
)
我创建了以下序列，并触发了每一行的唯一标识:
I have created the following sequence and trigger to create a unique identity for each row:
CREATE SEQUENCE STVM_NOTIF_SEQ
START WITH 1
INCREMENT BY 1
CACHE 100;
CREATE OR REPLACE TRIGGER STVM_NOTIF_ID_TRG BEFORE INSERT ON STVM_NOTIFICATION
FOR EACH ROW
BEGIN
:NEW.ID := STVM_NOTIF_SEQ.NEXTVAL;
END;
然后我为STVM设置以下授予:
I then set the following grants for STVM:
GRANT CREATE SESSION TO STVM;
GRANT CREATE TABLE TO STVM;
GRANT CREATE VIEW TO STVM;
GRANT CREATE ANY TRIGGER TO STVM;
GRANT CREATE ANY PROCEDURE TO STVM;
GRANT CREATE SEQUENCE TO STVM;
GRANT CREATE SYNONYM TO STVM;
GRANT CHANGE NOTIFICATION TO STVM;
插入表中就可以了.以下是由Oracle文档提供的有关OracleDependency的简单测试应用程序(进行了微小的修改)，我正在使用该应用程序来测试通知:
Inserting into the table works just fine. Below is the simple test app provided by the Oracle documentation on OracleDependency (with tiny modifications) that I'm using to test notifications:
namespace SqlDependencyTest
{
class Program
{
private static string oraConnectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.164)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));User Id=STVM;Password=STVM;";
private static string oraQuery = "SELECT ID FROM STVM_NOTIFICATION";
private static OracleDependency oraDependency;
static void Main(string[] args)
{
using (OracleConnection oraConnection = new OracleConnection(oraConnectionString))
{
try
{
// Open the connection
oraConnection.Open();
// Create the Select command retrieving all data from the STVM_NOTIFICATION table.
OracleCommand selectCommand = new OracleCommand(oraQuery, oraConnection);
// Create an OracleDependency object and set it to track the result set returned by selectCommand.
oraDependency = new OracleDependency(selectCommand);
// Setting object-based change notification registration
oraDependency.QueryBasedNotification = false;
// When the IsNotifiedOnce property is true, only the first change
// of the traced result set will generate a notification.
// Otherwise, notifications will be sent on each change
// during the selectCommand.Notification.Timeout period.
selectCommand.Notification.IsNotifiedOnce = true;
// Set the event handler to the OnChange event.
oraDependency.OnChange += new OnChangeEventHandler(OnChange);
// When the select command is executed at the first time, a notification
// on changes of the corresponding result set is registered on the server.
//selectCommand.CommandTimeout = 5;
OracleDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);
// Set and execute an insert command. The Dept table data will be changed,
// and a notification will be sent, causing the OnChange event of the 'dependency' object.
OracleCommand insertCommand = new OracleCommand
("INSERT INTO STVM_NOTIFICATION (PROPERTYNAME, PROPERTYVALUE, ACTION, POSTDATE, SENT) VALUES ('Heartbeat', 'NOK', 'REFRESH', SYSDATE, 'N')", oraConnection);
insertCommand.ExecuteNonQuery();
// Pause the current thread to process the event.
Console.Read();
}
catch (Exception e)
{
Console.WriteLine("Exception encountered: {0}", e.Message);
}
// Always try to both remove the notification registration
// oraConnection.Close() is autimatically called by .Dispose at the end of our 'using' statement
finally
{
try
{
oraDependency.RemoveRegistration(oraConnection);
}
catch (Exception e)
{
Console.WriteLine("Exception encountered: {0}", e.Message);
}
}
}
}
// A simple event handler to handle the OnChange event.
// Prints the change notification details.
private static void OnChange(Object sender, OracleNotificationEventArgs args)
{
DataTable dt = args.Details;
Console.WriteLine("The following database objects were changed:");
foreach (string resource in args.ResourceNames)
{
Console.WriteLine(resource);
}
Console.WriteLine("\n Details:");
Console.Write(new string('*', 80));
for (int rows = 0; rows < dt.Rows.Count; rows++)
{
Console.WriteLine("Resource name: " + dt.Rows[rows].ItemArray[0]);
string type = Enum.GetName(typeof(OracleNotificationInfo), dt.Rows[rows].ItemArray[1]);
Console.WriteLine("Change type: " + type);
Console.Write(new string('*', 80));
}
}
}
}
这实际上有效！但是:仅直到另一个进程在同一张表上执行插入操作，至少，这是我的观察.我从SQL Developer执行了多次插入，此问题是可重现的，并且已经执行了10多次了.
This actually works! However: only until another process performs an insert on the same table, at least, that is my observation. I performed the insert multiple times from SQL Developer, the issue is recreatable and I have done so for over 10 times.
一旦另一个进程执行了上述插入操作，我的应用程序就会挂在以下语句上:
OracleDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);
As soon as another process performs said insert, my application hangs on the following statement:
OracleDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);
我可以清楚地看到通知回调已在 DBA_CHANGE_NOTIFICATION_REGS 中注册:
net8://(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.226)(PORT=64268))?PR=0
I can clearly see the notification callback being registered in DBA_CHANGE_NOTIFICATION_REGS:
net8://(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.226)(PORT=64268))?PR=0
连接在Oracle服务器上保持打开状态30分钟，直到超时:
The connection remains open on the Oracle server for 30 minutes until it times out:
SELECT USERNAME, PROGRAM, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKING_SESSION, EVENT FROM V$SESSION
WHERE USERNAME = 'STVM'
USERNAME PROGRAM BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION EVENT
STVM SQL Developer NO HOLDER (null) (null) SQL*Net message from client
STVM OracleDependencyTest.vshost.exe VALID 1 50 enq: TM - contention
无论何时发生这种情况，唯一的解决方案是终止会话，撤消更改通知并再次授予它.此后，我的应用程序将再次运行，直到另一个进程执行插入操作为止.
Whenever this happens, the only solution is to kill the sessions, to REVOKE CHANGE NOTIFICATION and to GRANT it again. After this, my application will work again, until another process performs an insert.
我唯一的线索是事件 enq:TM-争用，但是大多数人将其称为表中未索引外键的指示符.考虑到我目前只有一个表用于测试，因此这里没有很多外键.
The only clue I have is the event enq: TM - contention, but most people refer to this as an indicator of non-indexed foreign keys in the table. Considering that I currently only have a single table for testing purposes, there aren't a whole lot of foreign keys to go around here.
有人对 enq:TM-竞争的相关性有任何想法吗?
Does anyone have any ideas as to the relevance of enq: TM - contention?
我正在使用:
(服务器端)
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Oracle.DataAccess
(客户端)
Oracle Call Interface (OCI) 11.2.0.1.0
Oracle Client 11.2.0.1.0
Oracle Data Provider for .NET 11.2.0.1.0
Oracle JDBC/OCI Instant Client 11.2.0.1.0
Oracle JDBC/THIN Interfaces 11.2.0.1.0
Oracle SQL Developer 11.2.0.1.0
SQL*Plus 11.2.0.1.0
SQL*Plus Files for Instant Client 11.2.0.1.0
更新:经过数天试图找出问题所在却没有发现问题的原因，我决定继续前进，寻找另一种技术.克里斯蒂安(Christian)的好建议没有产生任何结果，不幸的是，像贾斯汀(Justin)所建议的那样，解决了有疑问的交易也没有使我有任何进一步的收获.我知道几个Oracle DBA在工作中最初愿意提供帮助，但是一旦我提到.NET，他们很快就把我拒之门外.
UPDATE: After days of trying to find out what the problem is and not finding the issue, I've decided to move on and look for a different technique. The great suggestion by Christian did not yield any results, and resolving the in-doubt transaction like Justin suggested didn't get me any further either unfortunately. I know a couple of Oracle DBA's over at work who were initially willing to help, but they quickly shoo'd me away as soon as I mentioned .NET.
推荐答案
您正在使用的数据库版本中存在一个错误，该错误可能与该错误有关.您可以通过执行以下SYSDBA来检查是否是这种情况: 更改系统设置事件永远的1867跟踪名称上下文，级别1"；
There's a bug in the version of the database that you are using that may be related. You can check to see if this is the case by executing the following as SYSDBA: alter system set events '10867 trace name context forever, level 1';
这将持续到关机.如果问题消失了，那您就找到了错误.
This will last until shutdown. If the problem goes away you are hitting the bug.
您不应将此功能保持打开状态，而应升级数据库并修补ODP.NET.
You should not leave this turned on, rather you should upgrade the database and and also patch ODP.NET.
这篇关于带有OracleDependency的OracleCommand永远等待的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
