0 Replies Latest reply on Apr 28, 2014 7:03 PM by jhaasbeek

    Stored Procedure Transaction Deadlocks with Connection Pooling

    jhaasbeek

      I have a stored proc in Oracle 11g that contains it's own transaction:

       

      create or replace

      PROCEDURE p_delete_foo

      (

          v_id IN NUMBER

      )

      AS

          v_sql_error_code  NUMBER(10);

          v_sql_error_msg   VARCHAR2(512);

       

      BEGIN

          DELETE FROM bar1 WHERE fk_id = v_id;

          DELETE FROM bar2 WHERE fk_id = v_id;

          DELETE FROM bar3 WHERE fk_id = v_id;

          DELETE FROM foo WHERE pk_id = v_id;


          COMMIT;

       

      EXCEPTION

          -- There is no special handling for different errors.  Anything goes wrong, we bail out.

          WHEN OTHERS THEN

              ROLLBACK;

              v_sql_error_code := SQLCODE;

              v_sql_error_msg  := SQLERRM;

              RAISE_APPLICATION_ERROR(-20001, 'Exception occurred deleting foo.  Error code ' || v_sql_error_code || ': ' || v_sql_error_msg);

      END;

       

      Using ODP.NET managed provider version 4.121.1.0 I call this proc from an Entity Framework app (EF 5) - note that I am NOT using an explicit EF transaction around this call:

       

      int result = myDb.p_delete_foo(myFooId);

       

      I originally had the ODP.NET connection set up to NOT use pooling and this worked fine, very occasionally I would see resource deadlocks on the bar1, bar2, and bar3 tables (maybe five times a year).  Recently I changed the connection string to use pooling and now under moderate load I am seeing massive amounts of unreleased locks and resource deadlocks that all seem to be caused by this call to p_delete_foo.

       

      Additional info - there are other pages in my web app that insert and delete records in the bar1, bar2, and bar3 tables but for the most part each transaction should take at most a few seconds (generally less than a thousand records are modified in a transaction).

       

      Any ideas why connection pooling would cause this scenario to fail so catastrophically?

       

      Thanks,

       

      John H.