SaveEntityCollection with TransactionScope

Posts   
 
    
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 25-Jun-2009 13:30:47   

Is there any chance that in below code:

                adapter.SaveEntityCollection(collection, false, true);

will rollback and subsequent call:

                adapter.SaveEntityCollection(queuedInvoices, false, false);

will not? This is what seems to be happening in my system every now and then.

        using (TransactionScope scope = new TransactionScope())
        {
            using (DataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                foreach (InvoiceEntity invoice in collection)
                {
                    Guid uniqueInvoiceId = Guid.NewGuid();
                    invoice.UniqueId = uniqueInvoiceId;

                    QueuedInvoiceTaskEntity queuedInvoice = queuedInvoices.AddNew();
                    queuedInvoice.QueuedItemId = uniqueInvoiceId;
                }
                adapter.SaveEntityCollection(collection, false, true);
                adapter.SaveEntityCollection(queuedInvoices, false, false);
            }
            scope.Complete();
        }

Using LLBLGen 2.6.09.0116 + Adapter templates targetting .net 3.5

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 25-Jun-2009 20:06:18   

Hi Robert,

I can't reproduce your issue. Anyway, please update to the latest libraries. Also, are you sure that the first save is rollback? Please check the generated sql to see if there's something to save.

David Elizondo | LLBLGen Support Team
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 26-Jun-2009 09:58:22   

Hi,

No item from collection exists in DB (looking for Invoices.UniqueId = QueuedInvoiceTasks.QueuedItemId) which leads me to conclusion that it had to rollback. If collection didn't have anything to save then queuedInvoices would be empty too.

Although it would be possible to delete from Invoices table after the transaction completes leaving the QueuedInvoiceTasks out of sync (since they don't have referential integrity enforced on db level) this would be spotted by system users immediately - so this scenario can be ruled out.

This seems to happen once every couple of months in production - I was never able to catch this using my integration tests.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Jun-2009 11:04:16   

The code you posted is not complete and not clear to me.

        using (TransactionScope scope = new TransactionScope())
        {
            using (DataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                foreach (InvoiceEntity invoice in collection)
                {
                    Guid uniqueInvoiceId = Guid.NewGuid();
                    invoice.UniqueId = uniqueInvoiceId;

                    QueuedInvoiceTaskEntity queuedInvoice = queuedInvoices.AddNew();
                    queuedInvoice.QueuedItemId = uniqueInvoiceId;
                }
                adapter.SaveEntityCollection(collection, false, true);
                adapter.SaveEntityCollection(queuedInvoices, false, false);
            }
            scope.Complete();
        }
  • What are you doing with the invoice object?
  • For any collection, please use Add() instead of AddNew(), the later is used in databinding scenarioes only.
  • queuedInvoice.QueuedItemId, is this a PK field?
  • How collection was filled?And what does it contain?

Please explain your code in mre details.

btw, which database type is it? is this an Oracle db?

Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 26-Jun-2009 12:38:07   

1) What are you doing with the invoice object?

each invoice modified in the foreach loop and then saved in batch: adapter.SaveEntityCollection(collection, false, true);

2) For any collection, please use Add() instead of AddNew(), the later is used in databinding scenarioes only.

Somehow missed that - it worked fine during my tests but will switch to Add.

3) queuedInvoice.QueuedItemId, is this a PK field? Yes

4) How collection was filled?And what does it contain? Below is the full method code. collection was filled using constructor overload and contains InvoiceEntity objects each of which was constructed using the default constructor:

InvoiceEntity output = new InvoiceEntity();

and then added to the list. IList<InvoiceEntity> invoices supplied to the method is List<InvoiceEntity>.

    public static void RegisterInvoices(IList<InvoiceEntity> invoices, BatchEntity batch)
    {
        if (invoices == null)
            throw new ArgumentNullException("invoices", "invoices is null.");
        if (batch == null)
            throw new ArgumentNullException("batch", "batch is null.");

        EntityCollection<InvoiceEntity> collection = new EntityCollection<InvoiceEntity>(invoices);
        EntityCollection<QueuedInvoiceTaskEntity> queuedInvoices = new EntityCollection<QueuedInvoiceTaskEntity>();

        using (TransactionScope scope = new TransactionScope())
        {
            using (DataAccessAdapter adapter = DataAccessAdapterFactory.Create())
            {
                adapter.SaveEntity(batch, true);
                foreach (InvoiceEntity invoice in collection)
                {
                    Guid uniqueInvoiceId = Guid.NewGuid();
                    invoice.ImportBatchId = batch.BatchId;
                    invoice.UniqueId = uniqueInvoiceId;
                    invoice.IsNew = true;

                    QueuedInvoiceTaskEntity queuedInvoice = queuedInvoices.AddNew();
                    queuedInvoice.QueuedItemId = uniqueInvoiceId;
                }
                adapter.SaveEntityCollection(collection, false, true);
                adapter.SaveEntityCollection(queuedInvoices, false, false);
            }
            scope.Complete();
        }
    }

Please let me know if you need any more details.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Jun-2009 13:15:30   

Rollback is always a result of exception (most proably at database level), do you have any logs for such exception?

If you think the ionvoices collection save has been rolledBack but the QueuedInvoices has not, what about the BatchEntity save? was it rolledBack too, or not.

Would you please post the DDL of the inovoice table and that of the QueuedInvoiceTask table? I want to know how they are related and what are the PKs and FKs.

Anyway your case is wierd as we can't reproduce it, and neither can you. As you said it happens every once in a while. Which is not of much help to us to trace it down.

Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 26-Jun-2009 14:12:07   

I know this must be an exception (i bet this is a timeout since those collections can be quite big) and I'm ok with everything rollingback - the problem is it seems to only rollback some of the operations that's why I was wondering if there was something wrong with entity collections recognizing transactionscope properly.

Forgot to mention the db is sql server 2005

I have no execption logged as of now - this situation results in thousands of exceptions logged which overwrite the one that would be helpful

BatchEntity is probably rolledback too - but I can't be 100% certain since QueuedTaskEntity has no relation with BatchEntity

CREATE TABLE [dbo].[QueuedTasks]( [QueuedItemId] [uniqueidentifier] NOT NULL,

    not relevant columns removed

CONSTRAINT [PK_QueuedTasks] PRIMARY KEY CLUSTERED ( [QueuedItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]


CREATE TABLE [dbo].[Invoices]( [InvoiceId] [bigint] IDENTITY(1,1) NOT NULL, [UniqueId] [uniqueidentifier] NOT NULL, [ImportBatchId] [bigint] NULL,

    not relevant columns removed

CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [InvoiceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

CONSTRAINT [UQ_Invoices_UniqueId] UNIQUE NONCLUSTERED ( [UniqueId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], GO

ALTER TABLE [dbo].[Invoices] WITH CHECK ADD CONSTRAINT [FK_Orders_Batches] FOREIGN KEY([ImportBatchId]) REFERENCES [dbo].[Batches] ([BatchId]) GO

ALTER TABLE [dbo].[Invoices] CHECK CONSTRAINT [FK_Orders_Batches] GO

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Jun-2009 09:55:18   

the problem is it seems to only rollback some of the operations

I don't think you are 100% sure of that, are you?

BatchEntity is probably rolledback too - but I can't be 100% certain since QueuedTaskEntity has no relation with BatchEntity

So we wouldn't even know if a Rollback was issued or not. Also from the DDL you supplied I see that there is no relation between Invoices and QueuedTasks.

Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 29-Jun-2009 10:19:22   

Quote: the problem is it seems to only rollback some of the operations

I don't think you are 100% sure of that, are you?

I'm saving everything under same transaction scope. One part is saved, other is not - I'm assuming there is a rollback for one of those but again I haven't written sql server or llblgen so I'm not 100% sure I'm not missing something - this is just a very, very strong feeling based on what I see in the DB once this happens.

Quote: BatchEntity is probably rolledback too - but I can't be 100% certain since QueuedTaskEntity has no relation with BatchEntity

So we wouldn't even know if a Rollback was issued or not. Also from the DDL you supplied I see that there is no relation between Invoices and QueuedTasks

Yes, no relation exists since QueuedTasks apply to different entities nit only Invoices I decided to use a "loose" reference and cover the lack of db level referential integrity with unit tests instead.

I know there's only that much you can do with this limited amount of information - I'm happy with you confirming that you're 99.999% sure that batch saving two collections one after another as it is done in my scenario (coupled with the way I fill the in and create entities) will correctly make use of existing transaction (TransactionScope). Once I'm sure I'm not missing something obvious I can assume there is something wrong in my code - and will wait until this happens again hopefully equipped in some better exception logging.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jun-2009 15:18:21   

Will look into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Jun-2009 10:17:19   

Robert.W wrote:

Quote: the problem is it seems to only rollback some of the operations I don't think you are 100% sure of that, are you?

I'm saving everything under same transaction scope. One part is saved, other is not - I'm assuming there is a rollback for one of those but again I haven't written sql server or llblgen so I'm not 100% sure I'm not missing something - this is just a very, very strong feeling based on what I see in the DB once this happens.

TransactionScope uses for 1 connection a lightweight transaction, which is bumped up to an MS DTC controlled transaction when 2 or more connections are opened. You're using the same adapter, and although two transactions are created, they are both within the same lightweight transaction. The adapter registers itself with the scope (using our monitor) and will notify the monitor to commit or rollback the transaction at the end of the scope.

When a transaction rolls back (forced, e.g. through an exception), it can be rolled back from two sides: from the DB or from the client. SqlServer has a nasty aspect which is called severity level of errors. When an error occurs which is above a given severity level (I think it was 16), it will roll back the transaction at the server level and the client side equivalent (the ado.net transaction object) is then orphaned, as there's no connection from server to client. This might cause a problem inside the ado.net client in some edge cases where the first rolls back due to a severity level error, causing a problem for the ado.net transaction object which is then destroyed by the sql client. This might (but I'm guessing here as we can't reproduce it) cause a problem within the scope as there's at that point no physical ado.net transaction object anymore that's usable.

Quote: BatchEntity is probably rolledback too - but I can't be 100% certain since QueuedTaskEntity has no relation with BatchEntity

So we wouldn't even know if a Rollback was issued or not. Also from the DDL you supplied I see that there is no relation between Invoices and QueuedTasks

Yes, no relation exists since QueuedTasks apply to different entities nit only Invoices I decided to use a "loose" reference and cover the lack of db level referential integrity with unit tests instead.

I know there's only that much you can do with this limited amount of information - I'm happy with you confirming that you're 99.999% sure that batch saving two collections one after another as it is done in my scenario (coupled with the way I fill the in and create entities) will correctly make use of existing transaction (TransactionScope). Once I'm sure I'm not missing something obvious I can assume there is something wrong in my code - and will wait until this happens again hopefully equipped in some better exception logging.

As you're saving with the same adapter to the same db, it's not really necessary to use the transaction scope: an ado.net transaction will do just fine. What you could do is create a Unitofwork, add both collections to it and the entity to save and commit it in one go by passing in an adapter. This will wrap everything in the same transaction and save everything in the right order. You then also don't keep the connection open longer than necessary as the loop you have inside the using statement should be outside the using statement (as during that loop nothing is saved). Commit() of the unit of work automatically rolls back the transaction and will rethrow the received exception. If it's a severity level problem, the rollback will crash with an exception (as the client-side ado.net transaction object is 'orphaned', something we have no control over unfortunately) and you'll see that as well.

This might be helpful overcome this problem and/or get rid of it as well. As it happens very rarerly, it's either a data-oriented problem or some weird state in the lightweight transaction manager of sqlserver which might cause problems. I assume you have applied all service packs for sqlserver 2005?

Frans Bouma | Lead developer LLBLGen Pro