But I Need To Roll That Back
It all sounds like a great idea.
You want your stored procedure to be a unit of work that you can roll back if something goes awry.
At first, it’s a few quick operations.
But over the years, the code has gotten more complicated, data has gotten larger, and now you’ve got terrible blocking problems.
Do The Worm
Assuming you can’t get rid of the transaction, you need to think of it like a movie about interstellar travel.
You know the kind where someone folds a piece of paper in half and pokes a hole through it?
Your job is to make all the stuff that happens inside as fast as possible.
This won’t fix the blocking, but it will shorten it.
Other Approaches
It might be possible for you to get rid of the transaction if you can store the before and after data somewhere.
This will get rid of the transaction, so locks won’t be held for the duration of it, but it makes you do more work in the procedure.
For every row or set of rows you change, you have to get the current values, put them somewhere, and hang onto them.
If you need to roll back your work, you need to go through and put them back. This can cause all sorts of problems, especially if someone else is working on the same data at the same time. Who should win?
You can partially solve this with sp_getapplock, but then you’re locking access to code until the whole thing is done, and…
You’re back to having locking problems.
Another thing you might be able to implement if you can change table structure is add an “in use” bit column, and skip over rows that are in use by someone else. This is common in queue tables, but can be extended to other types of work tables.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.