Oracle Session and Enlist option

[transex]

I'm using EF3.5 with dotConnect for Oracle version 6.50.
I have 2 problems (or questions).

1. Can I use Enlist=false to prevent increasing session count?
2. If I use Enlist=false, 2 transactions in a TransactionScope works as "all-or-nothing"? (commit all or nothing)


For problem 1)

My problem is oracle session count increases every time when DB connection occurs. For example, following code increase session count by 6.

Code: Select all

using (CIMEntities entities = new CIMEntities())
{
    // entities.Connection.Open();
    object o1 = SELECT_OBJECT(entities, "ID-1"); // select 1
    object o2 = SELECT_OBJECT(entities, "ID-2"); // select 2
    object o3 = SELECT_OBJECT(entities, "ID-3"); // select 3
    object o4 = SELECT_OBJECT(entities, "ID-4"); // select 4
    object o5 = SELECT_OBJECT(entities, "ID-5"); // select 5
    object o6 = SELECT_OBJECT(entities, "ID-6"); // select 6
}

I know that if I open connection before SELECT_OBJECT (remarked in code) session count is not increased. But I found with Enlist=false in connection string (default true) session count is not increased also.

For problem 2)
I set Enlist=false in my connection string.
I read http://www.devart.com/dotconnect/oracle ... tions.html.
And I thought following code will commt partially if UPDATE "ID-2" throws exception. But it works as "all-or-nothing".
Did I misunderstand aboue "Using Transaction" or something else aboule local/distributed transaction?

Code: Select all

using (CIMEntities entities1 = new CIMEntities())
using (CIMEntities entities2 = new CIMEntities())
using (TransactionScope txn = new TransactionScope())
{
    try
    {
        entities1.Connection.Open();
        entities2.Connection.Open();

        UPDATE_OBJECT(entities1, "ID-1");
        UPDATE_OBJECT(entities2, "ID-2");

        entities1.Connection.Close();
        entities2.Connection.Close();

        txn.Complete();
    }
    catch (Exception ex)
    {
        entities1.Connection.Close();
        entities2.Connection.Close();
    }
}

Thanks.

[Shalex]

For problem 1)

My problem is oracle session count increases every time when DB connection occurs. For example, following code increase session count by 6.

Code: Select all

using (CIMEntities entities = new CIMEntities())
{
    // entities.Connection.Open();
    object o1 = SELECT_OBJECT(entities, "ID-1"); // select 1
    object o2 = SELECT_OBJECT(entities, "ID-2"); // select 2
    object o3 = SELECT_OBJECT(entities, "ID-3"); // select 3
    object o4 = SELECT_OBJECT(entities, "ID-4"); // select 4
    object o5 = SELECT_OBJECT(entities, "ID-5"); // select 5
    object o6 = SELECT_OBJECT(entities, "ID-6"); // select 6
}

This is a designed behaviour if there is an ambient global transaction which wraps the mentioned code. In this case a connection does return to pool but waits finishing TransactionScope to commit/rollback its session. We recommend you not to put SELECTs into TransactionScope.

And I thought following code will commt partially if UPDATE "ID-2" throws exception. But it works as "all-or-nothing".
Did I misunderstand aboue "Using Transaction" or something else aboule local/distributed transaction?

Code: Select all

using (CIMEntities entities1 = new CIMEntities())
using (CIMEntities entities2 = new CIMEntities())
using (TransactionScope txn = new TransactionScope())
{
    try
    {
        entities1.Connection.Open();
        entities2.Connection.Open();

        UPDATE_OBJECT(entities1, "ID-1");
        UPDATE_OBJECT(entities2, "ID-2");

        entities1.Connection.Close();
        entities2.Connection.Close();

        txn.Complete();
    }
    catch (Exception ex)
    {
        entities1.Connection.Close();
        entities2.Connection.Close();
    }
}

Entity Framework ignores connection string settings and enlists its transaction into global transaction. As a solution, please wrap this line of code:

Code: Select all

    using (new TransactionScope(TransactionScopeOption.Suppress)) {
        UPDATE_OBJECT(entities1, "ID-1");
    }