OracleCommand with OracleDependency waiting foreve

Note: Relevant question over here has no solution

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;

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;

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));
            }
        } 
    }
}

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.

As soon as another process performs said insert, my application hangs on the following statement: OracleDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);

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

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.

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.

Does anyone have any ideas as to the relevance of enq: TM - contention?

I am using:

(Server side)

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

(Client Side)

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

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.

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.

You should not leave this turned on, rather you should upgrade the database and and also patch ODP.NET.