[Solved] Transactions limited to 50?

[Simon Flachsbart]

Hi there,
I tried to work off some download tasks, that are saved in a database table, that has no relations to other tables.

My code looks like that:

using (IObjectScope scope = ObjectScopeProvider.GetNewObjectScope())
{
    IObjectScopeQuery<AssetDownload> assets = from a in scope.Extent<AssetDownload>()
                                              select a;
    foreach (AssetDownload asset in assets)
    {
        this.Download(asset.Filepath, asset.Id);
        scope.Transaction.Begin();
        try
        {
            scope.Remove(asset);
            scope.Transaction.Commit();
        }
        catch
        {
            scope.Transaction.Rollback();
            throw;
        }
    }
}

The Download method has no further database transactions or telerik calls.
But the foreach loop always fails in the 50th turn with such message:

A non handled exception of type "Telerik.OpenAccess.Exceptions.UnsupportedException" has occured in Telerik.OpenAccess.dll.
Additional Information: Unsupported Operation: Transaction was terminated before and result set is discarded.

Independend of which data in this table, it is everytime the 50th turn that fails.

I work with SQL server 2008. I know that it is not directly supported yet, but I think this may not be the problem, because I used loops otherwhere too and had no problems. Maybe this is a problem of timeout? Because the this.Download call takes some time.

[PetarP]

Hi Simon Flachsbart,

Most probably your active connection has been recycled. The oldest idle (or busy) connection is tested every 2 minutes. If it fails during the validation process, it is closed and the next idle connection is tested. This process gets rid of stale connections after a database server has been restarted or a network problem has occurred. The feature can be disabled by using the Test con when idle property. The test interval (in seconds) can also be changed by using the Pool test interval property.
Please try extending the interval or disabling the feature in order to observe how this will impact your project.

Best wishes,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Simon Flachsbart]

Hi Petar,

Thanks for your reply.

This doesn't seem to work. I tried both: Setting Test con when idle to false and Pool test interval to 100000.

But don't you think it is strange, it is on the same turn each time.
I tested it with several orders:

IObjectScopeQuery<AssetDownload> assets = from a in scope.Extent<AssetDownload>() orderby [ORDER] select a;

Where I took a.Id, a.Timestamp, a.Date, a.Filepath as [ORDER].
It was the 50th loop turn every time.

Regards
Simon

[Simon Flachsbart]

Hi Petar,

I could solve the problem. Maybe this was no telerik problem:
I found that an sql connection gets closed anyway after a defined time. Now i reworte my code so it opens a scope, closes it during the download and then opens a new to delete the database row.
I'm not sure, if this really was the problem but with this solution it works.
But what I only find strange, is that it was each time the 50th turn. But maybe the loop took almost the same time.
Thank you anyway.

Regards
Simon

[PetarP]

Hi Simon Flachsbart,

We were finally able to see the exact issue in your project. The problem arises from the fact that Telerik OpenAccess ORM retrieves the result set in chunks of 50. This means that even if you retrieve 1000 records only 50 will be loaded in memory ( the local cache). Whenever you call the commit method Telerik OpenAccess will clear the result set ( everything else but the 50 records already loaded in the cache). Now when you start traversing the result everything will work for the 50 records that are cached. However the connection to the rest of the result set will be closed, thus raising the exception.

To avoid this behavior you have several possibilities:
1. You can iterate with your result set without committing (thus without discarding the result set). To achieve that you can use the Flush() method. This will temporarily push the changes to the server without committing it. Should you decide to use this approach than your code should look something similar to this:

 scope.Transaction.Begin();
            foreach (OrderDetail asset in details)
            {
                try
                {
                    scope.Remove(asset);
                    scope.Transaction.Flush();
                }
                catch
                {
                    scope.Transaction.Rollback();
                    throw;
                }
            }
            if (scope.Transaction.IsActive)
            {
                scope.Transaction.Commit();
            }

However have in mind that all the deletion is done within one transaction. This will inevitably result in either all records being deleted if the transaction is successful or none if the transaction fails.
2. Another approach would be to just pass the entire result set (scope.Remove(details)). Since the result set implement IEnumarable then Telerik OpenAccess ORM would traverse the result set collection and delete everything in it. Again have in mind that this approach will lead to the above behavior.
3. You can execute your query in a temporary list that will hold the result (using ToList()) over your result set. This will retrieve all data from the SQL server thus enabling you to execute your logic over the temporary list.
4. You can increase the chunk size that OpenAccess uses for retrieving information.

I hope this response has been useful to you. If any questions arise please don't hesitate to contact us again.

Greetings,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.