We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails…
Simple we have something similar to:
BEGIN TRY
BEGIN TRANSACTION;
--run some sprocs
EXEC [dbo].[sproc1];
EXEC [dbo].[sproc2];
EXEC [dbo].[sproc3];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Re-raise error
EXEC [dbo].[reraise_error];
RETURN -1;
END CATCH
These sprocs are pretty long running processes. However, I noticed the team who developed this application was running this job at 10pm and have a step before this job that takes down the site completely. They wanted to ensure no one edits any of this data so they have the site down while this process happens and then once its done the site is automatically brought back to life. This has been fine for that past 3 years until we had a customer join us from overseas…they noticed the site was down (due to this process) and now has requested a change.
I thought simple I could just disable the taking down of the site and handle edits in the front end of my application. The problem is the database seems to be locked while running this above transaction, for instance, a user trying to get to the site gets a spinning wheel until this transaction completes.
As I ran tests if I comment out the BEGIN TRANSACTION
and just run the sprocs (outside of a transaction) then I no longer have any issue as the tables aren't locked.
But I cannot do that as we want this process to either pass / fail completely. If it fails we want the whole transaction to fail (as we have a rollback transaction). The customer still wants the site not to lock up and continue this process so I played around with adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But this did not help in any way as it still seems users cannot do anything in the application until after the transaction is complete.
Am I doing something incorrectly? What can I do so I do not have to lock up the application but continue this process in a transaction? I would of thought changing the isolation level would help me out but I get the same results.
Best Answer
Inside a transaction, the modification locks are held for the duration of the entire transaction. Without the BEGIN TRAN, locks are only held until the end of each statement. There will still be blocking, but it won't last as long, so your site is probably able to operate more effectively.
Data modification queries don't work that way. Check out the documentation on isolation levels:
What you actually want is the opposite - the nightly process is going to take locks, you want the client (website) to be able to bypass them. But using
READ UNCOMMITTED
/NOLOCK
is not the ideal way to do that. There's a better way, which isUse RCSI
If the web site is mostly reading data, then you might have success with the read committed snapshot isolation level (RCSI). This would allow the website to continue to read from the database without being blocked by the nightly process.
You can read a detailed account of what to consider when turning on RCSI here: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide