When I delete multiple entities (say 200.000) within a transaction (adapter scenario) and then perform a rollback, I experience a System.Data.SqlClient.SqlException (“Time-out is expired …”). I use SQL Server 2000. Apparently a rollback uses the connection timeout (default is 15 sec.) rather than the command timeout. I have searched the internet and saw other developers struggle with the same problem. Someone suggested two possible solutions: 1) Adjust the connection timeout 2) Execute the rollback as SqlCommand. Both are not good practice, I think. 1) I don’t want to increase the connection timeout and if I wanted it, how much must I increase it? 2) Executing SqlCommand’s is handled internally by LLBL Gen and I don’t want to ‘workaround’ LLBL Gen. What is best practice, when using LLBL Gen?

You can pass a connectionString to the Adapter constructor, in which you should set the connection timeout to be more than 15 sec. (How much more? I don't know).

This way this connectionString would only be used to the Adapter instace it's passed to, and not be used by other Adapter instances.

your inserting 200,000 records and then deleting 200,000 records one by one. no matter what that will take some time. the sql looks like this

delete from [table] where [pk] = 1
delete from [table] where [pk] = 2
delete from [table] where [pk] = 3
delete from [table] where [pk] = 200,000

if possible use the adapter.DeleteEntitiesDirectly(); function. that will generate

delete from [table] where [pk] in (1, 2, 3, ... 200,000)

if you still have issues, break the collection up into smaller chunks and commit each chunk

if (collection.count > 100000)
{
    int max = 50000;
    while (collection.count > 0)
    {
         if (collection.count < 50000) max = collection.count;
         subset = collection.getrange(0, max);

         adapter.starttransaction();
         adapter.deletecollection(subset);
         adapter.commit();

         collection.remorerange(0, max);
    }
}
else
{
    adapter.starttransaction();
    adapter.deletecollection(collection);
    adapter.commit();
}

Walaa wrote:

You can pass a connectionString to the Adapter constructor, in which you should set the connection timeout to be more than 15 sec. (How much more? I don't know).

This way this connectionString would only be used to the Adapter instace it's passed to, and not be used by other Adapter instances.

Thanks. Using multiple instances of the adapter could decrease the problem. That is the way I worked around the problem for now. I am still curious how other developers tackle this ‘problem’ or if someone has another solution. What I really want is a kind of ‘transaction timeout’ (daydreaming?). Than, I could use the connection timeout for 'testing' if the database is still alive, the command timeout for general database access and the transaction timeout for committing and rolling back transactions. Another possibility is a request for a new feature for executing a rollback as SqlCommand!? This could be implemented in the IDataAccessAdapter interface as RollbackAsCommand().

Rollbacks have to consult the transaction log and re-add rows. The amount of time it takes depends on the fact if there are individual statements (which means individual sub transactions) or just 1 statement which affected 200,000 rows. The latter is much faster to rollback than the former.

If rollbacks use the connection timeout, I can't do much about it I think: the connection is already made, so the connection string to START the transaction should contain the timeout setting, something you don't want.

Otis wrote:

Rollbacks have to consult the transaction log and re-add rows. The amount of time it takes depends on the fact if there are individual statements (which means individual sub transactions) or just 1 statement which affected 200,000 rows. The latter is much faster to rollback than the former.

If rollbacks use the connection timeout, I can't do much about it I think: the connection is already made, so the connection string to START the transaction should contain the timeout setting, something you don't want.

If a have to choose between connection timeout and command timeout, I chose the command timeout. The trick is that you can force (ADO.NET) to use the command timeout by executing the rollback command as SQLCommand (query). That is why I suggested a 'new feature': IDataAccessAdapter.RollbackAsCommand().

Peter wrote:

Otis wrote:

Rollbacks have to consult the transaction log and re-add rows. The amount of time it takes depends on the fact if there are individual statements (which means individual sub transactions) or just 1 statement which affected 200,000 rows. The latter is much faster to rollback than the former.

If rollbacks use the connection timeout, I can't do much about it I think: the connection is already made, so the connection string to START the transaction should contain the timeout setting, something you don't want.

If a have to choose between connection timeout and command timeout, I chose the command timeout. The trick is that you can force (ADO.NET) to use the command timeout by executing the rollback command as SQLCommand (query). That is why I suggested a 'new feature': IDataAccessAdapter.RollbackAsCommand().

Ok, so you're suggesting that a command like "ROLLBACK TRANS" is executed via a command, instead of a rollback of the SqlTransaction object...

I'm not entirely sure if that will mess up housekeeping inside the Sqltransaction/connection objects though...