Hello,

I have a question about the appropriate use of transactions when performing a long task.

I have a C# application which runs on a semi-frequent basis. The application downloads data from a number of different data sources, post-processes the data on the fly, then progressively writes the post-processed data out to a local SQL Server (2000) database. The download of data takes about 3 hours, and quite commonly dies part way through (due to unreliable data sources).

It seems like a good idea that I wrap the whole download process in a single transaction (using the SqlTransaction object), so that I can roll back if something goes wrong part way through.

What I hadn't anticipated, however, is that by wrapping the process in a transaction, as soon as a table has an INSERT command issued to it, subsequent attempts by other users to issue a SELECT command to that table are blocked, until the transaction is either committed or rolled back.

Is this the expected behaviour? I tried playing around with different IsolationLevels, but none of these seemed to make any difference.

I had hoped that wrapping the process in a transaction would isolate the affected tables while the download was under way, but permit other users read-only access to these tables such that they would see the data that was present before the transaction was initiated.

Any help would be greatly appreciated!

Thanks,

Nick.

Hi,

Yes, this is an expected behaviour. This dis-advantage has been overcome in SQL-Server 2005 by using a database level isolation setting called SNAPSHOT READ COMMITTED

Regards

Arvind T N

Great - thanks for that. I noticed that this isolation level wasn't supported for SQL Server 2000. Gives me a good excuse to go to 2005...

Cheers,

Nick