Using ODP.NET managed provider version 18.104.22.168 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?