Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Stored Procedure Transaction Deadlocks with Connection Pooling

jhaasbeekApr 28 2014

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.

Comments

user111989
Answer

Try to put and check if it reduces time. also check if it works for all the combination because we need target block for this to work

SET FRMLBOTTOMUP ON;

https://docs.oracle.com/cd/E57185_01/ESBTR/set_frmlbottomup.html

Marked as Answer by Zut Alors · Sep 27 2020
Jeo123

If you go to the logs when the calc is run, it'll tell you a bunch of useful things.  In particular, are you seeing notifications about calc mode being set to cell mode or something similar in there?

More importantly though, why not just agg the  geography dimension and then reference the total instead of doing a sum of the dimension on every entity?  Is there something in your app design that prevents that?

Zut Alors

@Jeo123

that's correct, aggregating a section of the dim would have helped, except there is some additional one-off calculation that need to occur

thanks,

cl

Zut Alors

that reduced the calc time by 80% - thank you!

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 26 2014
Added on Apr 28 2014
0 comments
801 views