安装及环境配置
我创建了一个控制台应用程序,目标框架为.NET 4.7.2,并安装了NuGet包Oracle.ManagedDataAccess 版本12.2.1100(最新/当前版本),该包设置了两个查询的依赖项,以监视对 MYTABLE
的更改,这两个查询具有不同的WHERE子句。
我连接的Oracle数据库服务器运行在本地主机上,版本为12.2.0.1.0。
除了监听通知的控制台应用程序外,我还使用Oracle SQL Developer(v18.1.0.095)实际插入或更新“MYTABLE”中的记录,以强制发出通知。
代码
using (var connection = new OracleConnection("Data Source=//localhost:1521/ORCL;Persist Security Info=True;User ID=SYSTEM;Password=password"))
{
OracleDependency.Port = 3005;
var dependency = new OracleDependency();
dependency.OnChange += (sender, eventArgs) =>
{
Console.WriteLine($"Change count: {eventArgs.Details.Rows.Count}");
// Columns in row: string ResourceName, int Info, string Rowid, long QueryId
foreach (DataRow row in eventArgs.Details.Rows)
{
var resourceName = (string)row["ResourceName"];
var info = (OracleNotificationInfo)row["Info"];
var rowId = (string)row["rowid"];
var queryId = (long)row["QueryId"];
Console.WriteLine($"{queryId} {info} {rowId} {resourceName}");
}
};
connection.Open();
var command1 = new OracleCommand("SELECT * FROM MYTABLE WHERE NAME = 'N1'", connection);
dependency.AddCommandDependency(command1);
command1.Notification.IsNotifiedOnce = false;
command1.AddRowid = true;
command1.ExecuteNonQuery();
var command2 = new OracleCommand("SELECT * FROM MYTABLE WHERE NAME = 'N2'", connection);
dependency.AddCommandDependency(command2);
command2.Notification.IsNotifiedOnce = false;
command2.AddRowid = true;
command2.ExecuteNonQuery();
Console.ReadKey();
}
行为
一旦我在一个事务中触发多个命令依赖项,我会在通知中获得两倍于预期的事件行,并且无法区分触发它的查询ID。
在一个事务中运行此查询:
INSERT INTO MYTABLE (NAME) VALUES ('N1');
输出:
Change count: 1
63 Insert AAAR6CAABAAALohABJ SYSTEM.MYTABLE
在一个事务中运行此查询:
INSERT INTO MYTABLE (NAME) VALUES ('N2');
输出:
Change count: 1
64 Insert AAAR6CAABAAALohABK SYSTEM.MYTABLE
但是,当在一个事务中运行此查询时:
INSERT INTO MYTABLE (NAME) VALUES ('N1');
INSERT INTO MYTABLE (NAME) VALUES ('N2');
我得到了这个输出:
Change count: 4
63 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
63 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
我期望:
Change count: 2
63 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
此外,当我执行一个
INSERT
触发第一个命令和一个UPDATE
触发第二个命令时,对于第一个QueryId(63),我得到了4行INSERT
+UPDATE
,对于第二个QueryId(64),我也得到了4行INSERT
+UPDATE
,所以现在我无法区分它们。将命令依赖关系分离到多个
OracleDependency
类或甚至多个连接中会导致相同的结果。我想知道是否有人知道这里发生了什么?