Background Info: Designer: Version 2.0.0.0 Final, March 21st, 2007 Runtime: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.0.07.0129, SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.0.07.0424 .Net 2.0, Self Servicing General, C#

SQL Server 2005

I have a question regarding how transactions are used in self servicing when the entities are fetched via a collection.

We ran into an issue where we fetch an entity collection in a transaction, dispose of the transaction, and then the entities in that collection still reference the transaction that was used to fetch them. This has led to some unexpected behavior where the fetched entity is saved in the wrong transaction which creates a deadlock.

Should the entities in a collection still have a reference to the collection's transaction? Other than looping through the collection and setting each entity's Transaction property to null, is there a way to clear the entity's transaction?

Here's a NUnit test which demonostrates "the problem." The test fails on the last Assert.

       [NUnit.Framework.Test]
        public void LLBLGenTransactionTest()
        {
            AddressCollection addresses = new AddressCollection();

            using (Transaction tran = new Transaction(System.Data.IsolationLevel.ReadCommitted, "test"))
            {
                addresses.Transaction = tran;

                addresses.GetMulti(null, 10);
                addresses.Transaction = null;
            }

            Assert.IsNull(addresses.Transaction);
            Assert.Greater(addresses.Count, 0);

            foreach (AddressEntity address in addresses)
                Assert.IsNull(address.Transaction);
        }

Does 2.5 in self servicing function in the same manner? (I don't have a 2.5 project I can test it with.)

Thanks, -Ryan Casey

Instead of setting the transaction property of the collection, add the collection to the transaction object participants using transaction.Add() method, as shown in the manual.

    [NUnit.Framework.Test]
        public void LLBLGenTransactionTest()
        {
            AddressCollection addresses = new AddressCollection();

            using (Transaction tran = new Transaction(System.Data.IsolationLevel.ReadCommitted, "test"))
            {
                tran.Add(addresses);

                addresses.GetMulti(null, 10);
            }

            Assert.IsNull(addresses.Transaction);
            Assert.Greater(addresses.Count, 0);

            foreach (AddressEntity address in addresses)
                Assert.IsNull(address.Transaction);
        }

Anyway it's not recommended to fecth entities inside a transaction, this surely can lead to deadLocks.

Transactions should be only used for Saves (Update & Insert) & Deletes. And Reads should always be executed outside transactions.

Also transactions should be used to commit or Rollback actions and in your case I don't know how this can help in a fetch routine.

Walaa wrote:

Instead of setting the transaction property of the collection, add the collection to the transaction object participants using transaction.Add() method, as shown in the manual.

I changed my test code to use tran.Add(collection) and the behavior is the same.

Walaa wrote:

Anyway it's not recommended to fecth entities inside a transaction, this surely can lead to deadLocks.

Transactions should be only used for Saves (Update & Insert) & Deletes. And Reads should always be executed outside transactions.

Actually, with all due respect, there are times when it makes sense to fetch a collection in a transaction.

So, just to confirm, the behavior we are seeing is by design?

Do you know if 2.5 behaves the same way? (I don't have a self servicing 2.5 project to test with.)

Thanks, -Ryan Casey

Actually, with all due respect, there are times when it makes sense to fetch a collection in a transaction.

Could you give an example?

If you have any human interaction or long running code while that transaction is held, you've got to be seriously affecting other things running against those tables.

So, just to confirm, the behavior we are seeing is by design?

I believe it is.

Just a note here, I've run a little test project trying to reproduce the behavior that you described above, and found out that in fact the collection's transaction remains not null but in a different state (i.e. the PhysicalTransaction and the ConnectionToUse properties of the SD.LLBLGen.Pro.ORMSupportClasses.TransactionBase became null after the original transaction object was disposed).

caseyry wrote:

Background Info: Designer: Version 2.0.0.0 Final, March 21st, 2007 Runtime: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.0.07.0129, SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.0.07.0424 .Net 2.0, Self Servicing General, C#

SQL Server 2005

I have a question regarding how transactions are used in self servicing when the entities are fetched via a collection.

We ran into an issue where we fetch an entity collection in a transaction, dispose of the transaction, and then the entities in that collection still reference the transaction that was used to fetch them. This has led to some unexpected behavior where the fetched entity is saved in the wrong transaction which creates a deadlock.

Fetching in a transaction is OK, as long as you ADD the collection to the transaction. However fetching always places read-locks on the rows affected. WRITES to those rows then have to wait till the locks are lifted, that's how SQLServer works. We don't place locks, we leave that to the RDBMS.

Disposing a transaction like you do isn't the way it should work: you either have to commit or rollback the transaction.

You also shouldn't set the Transaction property, as described above, but you should add the collection to the transaction. This way, when it's rolled back or committed, it will notify all entities involved and these will then clean up their internals.

You describe you've done so, however try it with a commit or rollback.

You don't have to use a transaction as you've shown btw, as selfservicing can open its own connections.

Otis wrote:

Fetching in a transaction is OK, as long as you ADD the collection to the transaction. However fetching always places read-locks on the rows affected. WRITES to those rows then have to wait till the locks are lifted, that's how SQLServer works. We don't place locks, we leave that to the RDBMS.

Disposing a transaction like you do isn't the way it should work: you either have to commit or rollback the transaction.

Ahh...That's what I was missing. I didn't think about committing the transaction. That explains it.

Otis wrote:

You don't have to use a transaction as you've shown btw, as selfservicing can open its own connections.

Yeah, I know. We were using a transaction because we had already fetched data in a different transaction and wanted to do this fetch in this transaction to prevent a reader/writer from the other transaction from blocking this fetch.

Thanks!