Hello --

Basics: LLBLGen: 2.0.0.0, Dec. 6, 2006 Database: SQL Server 2005, SP2 (snapshot isolation is enabled) Runtime: SqlServer DQE: v2.0.50727; ORMSupportClasses - v2.0.50727 .Net 2.0; C# Adapter

I'm a little unclear on how the adapter.TransactionIsolationLevel property functions. Essentially, what I want to do is specify what isolation level SELECTs run under to avoid locks and concurrency issues.

In basic tests that I've done, setting adapter.TransactionIsolationLevel doesn't have an effect unless a new transaction is created. Is that by design? I'd prefer not to have to create a transaction explicitly and instead just have the "connection" or request run under the specified isolation level.

Here's some sample code:

This example throws a time out exception due, which I would expect if the Isolation level was Read Committed:

 [NUnit.Framework.Test()]
public void TestIsolationLevels_UseSnapshot()
{
    DataAccessAdapter adapterWriter;
    DataAccessAdapter adapterReader;

    CountryEntity USandA = new CountryEntity(215);
    CountryEntity USandA2 = new CountryEntity();
    CountryEntity boratHome = new CountryEntity();

    boratHome.CountryAbr2 = "KZ";
    USandA2.CountryAbr2 = "US";

    using (adapterWriter = new DataAccessAdapter())
    {
        adapterWriter.TransactionIsolationLevel = System.Data.IsolationLevel.ReadCommitted;
        Assert.IsTrue(adapterWriter.FetchEntity(USandA));

        USandA.CountryName = "USATemp";

        adapterWriter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "update");

        Assert.IsTrue(adapterWriter.SaveEntity(USandA), "failed to save usa country");

        using (adapterReader = new DataAccessAdapter())
        {
            adapterReader.TransactionIsolationLevel = System.Data.IsolationLevel.Snapshot;

            // now get original country
            adapterReader.FetchEntityUsingUniqueConstraint(USandA2, USandA2.ConstructFilterForUCCountryAbr2());

            Assert.AreEqual("United States", USandA2.CountryName);
        }

        adapterWriter.Rollback();
    }
}

This code example functions properly, as I explicitly open a transaction and specify the isolation level:

 [NUnit.Framework.Test()]
public void TestIsolationLevels_UseExplicitTransaction()
{
    DataAccessAdapter adapterWriter;
    DataAccessAdapter adapterReader;

    CountryEntity USandA = new CountryEntity(215);
    CountryEntity USandA2 = new CountryEntity();

    USandA2.CountryAbr2 = "US";

    using (adapterWriter = new DataAccessAdapter())
    {
        adapterWriter.TransactionIsolationLevel = System.Data.IsolationLevel.ReadCommitted;
        Assert.IsTrue(adapterWriter.FetchEntity(USandA));

        USandA.CountryName = "USATemp";

        adapterWriter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "update");

        Assert.IsTrue(adapterWriter.SaveEntity(USandA), "failed to save usa country");

        using (adapterReader = new DataAccessAdapter())
        {
            adapterReader.StartTransaction(System.Data.IsolationLevel.Snapshot, "reader");

            // now get original country
            adapterReader.FetchEntityUsingUniqueConstraint(USandA2, USandA2.ConstructFilterForUCCountryAbr2());

            Assert.AreEqual("United States", USandA2.CountryName);
        }

        adapterWriter.Rollback();
    }
}

I know that I'm running an older build, and if you think it is a bug, I'd be happy to try with the latest version.

Thanks.

setting adapter.TransactionIsolationLevel doesn't have an effect unless a new transaction is created. Is that by design? I'd prefer not to have to create a transaction explicitly and instead just have the "connection" or request run under the specified isolation level.

That's by design, isolation levels are only useful when using a transaction. That's a by Database Design

Please refer to SQL Server Books Online "Isolation Levels in the Database Engine"

Walaa wrote:

That's by design, isolation levels are only useful when using a transaction.

I don't think that's entirely correct. By default, the transaction level is read committed, even if you don't explicitly start a transaction.

You can change the transaction isolation level (in SQL/query analyzer) for the connection using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and that connection then uses READ UNCOMMITTED for all future statements.

Basically, I was hoping that LLBLGen would change the Isolation level for me. Here's a snippet from profiler of when a connection is opened:

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

I was thinking that LLBLGen could either change the isolation level there, or issue a SQL statement to set the isolation level.

Thanks, -Ryan

Option #1: Create a stored procedue and call it from LLBLGen Pro.

Option #2: Check the following: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7165

Walaa wrote:

Option #1: Create a stored procedue and call it from LLBLGen Pro.

Option #2: Check the following: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7165

Interesting, ok, thanks.

FWIW, I think the other option I will look at is turning READ_COMMITTED_SNAPSHOT to on which would cause READ COMMITTED to not take locks among other things.

Thanks.