Setup
I've created a Console application targeting .NET 4.7.2 and installed the NuGet package Oracle.ManagedDataAccess version 12.2.1100 (latest/current) that sets up a dependency with 2 queries to monitor changes made to MYTABLE, both queries have a different WHERE-clause.
The Oracle database server I'm connecting to is running on localhost and has version 12.2.0.1.0.
Next to the Console application that listens for notifications, I'm using Oracle SQL Developer (v18.1.0.095) to actually insert or update records to MYTABLE in order to force a notification.
Code
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();
}
Behavior
As soon as I trigger multiple command dependencies in one transaction, I get twice as much event-rows in the notification and can no longer distinguish the queryid that triggers it.
Running this query in one transaction:
INSERT INTO MYTABLE (NAME) VALUES ('N1');
outputs:
Change count: 1
63 Insert AAAR6CAABAAALohABJ SYSTEM.MYTABLE
Running this query in one transaction:
INSERT INTO MYTABLE (NAME) VALUES ('N2');
outputs:
Change count: 1
64 Insert AAAR6CAABAAALohABK SYSTEM.MYTABLE
But, when running this query in one transaction:
INSERT INTO MYTABLE (NAME) VALUES ('N1');
INSERT INTO MYTABLE (NAME) VALUES ('N2');
I get this output:
Change count: 4
63 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
63 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
While I would expect:
Change count: 2
63 Insert AAAR6CAABAAALohABH SYSTEM.MYTABLE
64 Insert AAAR6CAABAAALohABI SYSTEM.MYTABLE
Also, when I do an INSERT that triggers the first command and an UPDATE that triggers the second command, I get 4 rows with INSERT + UPDATE for first QueryId (63) and INSERT + UPDATE for second QueryId (64), so now way I can tell them apart.
Separating the command dependencies over multiple OracleDependency classes or even multiple connections leads to the same outcome.
I wonder if someone knows what is going on here?
