Hi,
ASP.Net 4.0 website, SQL Server 2008
I have some code in a website that reads, updates and inserts records into various tables with a transaction around it using .Net code. I am doing it this way as all the DLLs we have at a DAL level are already done for the calls to the database, and the whole updating can happen in a loop many times.
So I begin the transaction, loop through a bunch of data, selecting, updating and inserting records. Once the loop finishes, I either commit the transaction or roll it back if I discover that there was a problem along the way.
The problem is that if another user is logged on to the website, and accesses the same tables I get errors from either user and either user will have an exception occur which might be:
This SqlTransaction has completed; it is no longer usable.
or
Transaction (Process ID 57) was deadlocked on lock resources with another process ...
My guess is that it seems as though one user is somehow accessing another connection or something.
If my open transaction could potentially take a long time, should it all work if set up properly do you think? Or is this not a good way to go anyway (not the preferred answer ;) ).
I know you haven't seen any code, but there's quite a bit to it, and I just wanted a general opinion, as long as what I've said makes sense.
Thanks
Julian
What I have tried:
google searches, colleagues, different environments