Related to this post: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12661&StartAtMessage=0&#70931

I'm having the same problem. I'm getting ORA-01013.

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
                FullSenario sen = new FullSenario();
                sen.Run();
                throw new Exception("Lets fail");
                ts.Complete();
}

The FullSenario.Run() runs a big senario where I start to delete about 50 entries from about 20 tables. Then I insert about 50 entries into the 20 tables. I use UnitOfWork2 to handle the inserts / deletes.

The FullSenario.Run() does the following: void Run() { DeleteStuffTableGroupA(); // Using UOW DeleteStuffTableGroupB(); // Using UOW InsertStuffTableGroupA(); // Using UOW InsertStuffTableGroupB(); // Using UOW }

This actually runs fine once. The on the second run it times out with the ORA-01013. If the Exception is removed then this works fine on every run, it is just on subsequent run after the exception is thrown that the problems start.

I'm sure that this is some oracle transaction issue.

If anyone has any ideas they will be appriciated.

Regards, Ásgeir

First of all I think you should be creating a Transaction to add the LLBLGen Pro objects to.

Then I think you shouldn't throw an exception without first issuing an Abort/Rollback on the TransactionScope.

So it's better to use a Try/Catch block where you catch the exception and issue the Abort then re-throw the exception.

Walaa wrote:

First of all I think you should be creating a Transaction to add the LLBLGen Pro objects to. ...

That is not an option since this the client which call the Run function will eventually do that through WCF. Anyway, this is a perfectly OK way to do this accouring to recommendations for using Transactions in .Net.

Walaa wrote:

So it's better to use a Try/Catch block where you catch the exception and issue the Abort then re-throw the exception

Then I think you shouldn't throw an exception without first issuing an Abort/Rollback on the TransactionScope. .

No this is precisly how the TransactionScope behaves. If an Exception in throw within the scope then an automatic rollback is issued. Their is actuall no Rollback function for the TransactionScope, only SetComplete.

This has something to do with using more than one UnitOfWork2.Commits in a single Batch.

Regards, ASgeir

The first time, is the exception then also thrown? If yes, the first transaction never completes.

Otis wrote:

The first time, is the exception then also thrown? If yes, the first transaction never completes.

Yes. This work as it should the first time. The transaction in corrected rolled back. If I however try to run this again I get: ORMQueryExecutionException: ORA-01013

If I wait a few minutes then I can run this again normally (rollback works).

I think that the UnitOfWork is not returning some locks on the DB or something.

Any ideas ?

First of all I think you should be creating a Transaction to add the LLBLGen Pro objects to. ...

That is not an option since this the client which call the Run function will eventually do that through WCF. Anyway, this is a perfectly OK way to do this accouring to recommendations for using Transactions in .Net.

Ok, but I still think you should use a transaction inside the WCF servuce method, to wrap all database calls.

asgeirgs wrote:

Otis wrote:

The first time, is the exception then also thrown? If yes, the first transaction never completes.

Yes. This work as it should the first time. The transaction in corrected rolled back. If I however try to run this again I get: ORMQueryExecutionException: ORA-01013

If I wait a few minutes then I can run this again normally (rollback works).

I think that the UnitOfWork is not returning some locks on the DB or something.

Any ideas ?

TransactionScope is supported in ODP.NET starting with the 11g provider, if I'm not mistaken, please check the ODP.NET docs (oracle was pretty vague about this). It could be the transaction is 'rolledback' but actually not completely rolled back inside Oracle due to MTS/DTC related stuff. The timeout is likely caused by a locked row you're trying to update which isn't possible as the row is locked by another transaction (although I'm not sure what isolation level is set as the default in your oracle machine (although I think it's likely writers block writers in your case)).

Could you verify that after the rollback the first time, the rows are indeed not locked anymore and no transactions are running inside oracle ?

Otis wrote:

..... TransactionScope is supported in ODP.NET starting with the 11g provider, if I'm not mistaken, please check the ODP.NET docs (oracle was pretty vague about this). It could be the transaction is 'rolledback' but actually not completely rolled back inside Oracle due to MTS/DTC related stuff. The timeout is likely caused by a locked row you're trying to update which isn't possible as the row is locked by another transaction (although I'm not sure what isolation level is set as the default in your oracle machine (although I think it's likely writers block writers in your case)).

Could you verify that after the rollback the first time, the rows are indeed not locked anymore and no transactions are running inside oracle ?

Thanks for the tip. You are right the Oracle docs are vague about this. I need to research that further.

However:

I think that I may found why this happends.

If an Exception is thrown within a TransactionScope and subsequently the program exits (crash or graceful exit) then it seams to leave the db in strange state, ie. locks are not released.

Considder the following code:

        void Run()
        {
            RunTest(true);
            RunTest(false);
        }

        private void RunTest(bool bFail)
        {
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    FullSenario sen = new FullSenario();
                    sen.Run();
                    if (bFail)
                       throw new Exception("Lets fail");

                    ts.Complete();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: {0}", e.Message);
            }
        }

The code about work fine. I can run it from a console app again and again and it works as excepted. The transaction is properly rolledback and everything is fine.

However if I change the Run() like this:

        void Run()
        {
            RunTest(false);
            RunTest(true);
        }

If I run Run() from a console app and exit then I cannot run it again without running into ORA 01013. I have to wait a few minutes until Oracle cleans up then I can run it again. Strange.

I hope that this post may help someone who is going to run into this.

Asgeir

Hmm. Sounds to me like ODP.NET's TransactionScope support code apparently simply sits there and waits till a timeout occurs and it rolls back the transaction it initially started if an exception occurs and the app crashes.

TransactionScope support isn't easy for Oracle, as it's mainly designed for SqlServer (at least that's my idea of it as it requires close coupling with DTC). They have a service, an MTS service for DTC related material, but as transactionscope uses elevation of transactions to be lightweight first and then propagated automatically to DTC supported transactions, I think they missed a beat somewhere.

If you have a support contract with Oracle, could you notify them about this? Perhaps they have a bugfix for this problem.

I will report back if I get any meaning full response Oracle or any resource.

Thanks for the help.

Asgeir

Hello All,

I just tried ODP.Net 2.111.6.20 (ODP 11.1) and the issue I was facing with ODP 10 are NOT present in 11.

I did not realize that you could use ODP 11 against Oracle 10g but that works just fine (as you probably know).

So I really don't see any point in using ODP.net 10.

Thanks for you assistance.

Until next time,

Ásgeir

Thanks for the info yes, 11g's ODP.NET can be used on 10g without a problem, and I have the feeling Oracle is just using the newest versions to add bugfixes, leaving the older ones unpatched...