I am noticing below issues consistently when using ODP.NET, TransactionScope, Multiple-threads and using GTT - Error occurs only on 2nd attempt:
(First attempt completes always but looks like, by using GTT, somehow Connections in ConnectoinPool are still holding on to the older transaction or something, causing issue 2nd time)
Scenario: ASP.NET Web API2 (.NET 4.5.2) with ODP.NET version 12.1.021)
1. Make sure Connection Pool usage is enabled [default option ]
2. Open TransactionScope block
3. Trigger multiple tasks in parallel - up to 40 or so to easily see the issue
4. In each task, just insert some data into GTT
5. Wait for all tasks to complete
6. Call TrasactionScope.complete() to commit
7. Expose this code as simple Web API (Ex: /api/test)
8. Host in IIS
Observation:
1. All works on first run (Ex: http://localhost/App/api/test)
2. Just retry the same 2nd time and you get "ORA-14450 “attempt to access a transactional temporary table alreadyin use”
Quick workaround1:
1. If you call OracleConnection.ClearAllPool() before doing anything, code works any number of times
Workaround2: Not to use GTT
1. If we update solution not to use GTT and use permanent table - all works great - Multi-thread inside TS and use Connection Pool
I am suspecting that somehow GTT, ConnectoinPool combination with multi-thread - with TransactionScope scenarios is causing this issue.
Are there any known issues in this area that are fixed in newer versions and is this a bug or is there some config setting we need to turn on etc.
Appreciate your quick response.
JTBS