0

We have a ASP.NET web application which sends a request to a windows service to delete/update records in a number of tables. Windows service does this in a stored procedure and that call is in ADO.NET TransactionScope.

If number of records involved are many, It takes 20-30 seconds and during that period If user tries to access other parts of ASP.NET web application (which would be in a different connection) which involves those tables, he gets timeout error (because the transaction is still in progress)

I can change the select queries to include the with(nolock) hint or include SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the select stored procedures. However, that means I need to change all the select stored procedures which is not feasible at this point of time.

Also, I can't use SNAPSHOT transaction isolation model as for that I need to set the isolation model of the database to SNAPSHOT which wont be possible at this point of time.

Any alternatives?

Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
  • Ashish, if the user browses on the webapp during the transaction, will it mostly read data or it may have some writing too? – Andre Calil Jul 01 '12 at 22:43
  • It could read or update data as well. But what would be the difference in this scenario? – Ashish Gupta Jul 01 '12 at 22:52
  • If it is mostly reading, you can change the database to snapshot isolation, that's a good choice when you have concurrent write x read on certain objects. – Andre Calil Jul 02 '12 at 01:15

1 Answers1

0

IF you can't change the database to snapshot isolation, and you can't touch all of the SELECT procedures to allow dirty reads, can you change the way you delete or update data? Can you change your stored procedures to do smaller batches of deletes and/or updates?

Stuart Ainsworth
  • 12,792
  • 41
  • 46