When to use Transactions in SQL Server

Question

There are lots and lots of questions on HOW to use Transactions. What I want to know is WHEN? Under what circumstances? What types of queries? Can Try-Catch blocks suffice instead? Etc...

I've designed a database with ~20 tables and ~20 stored procedures. Currently none of my SPs use a transaction, but there are numerous Try-Catch blocks throughout. The reason is because every time I tried to wrap them in a transaction the SP would cease to function and I would end up with missing data and worse off than had I used Trans.

So again...

1. When is an appropriate time to use a Transaction?
2. As a follow-up question, if I use them, how can I use them in such a way as to ONLY prevent other SPs from accessing the same data at the same time in order to prevent corruption rather than causing my SPs to not function at all?

Here's a little sample SP I wrote for renaming a product:

CREATE PROCEDURE spRenameProduct
    @pKey int = NULL,
    @pName varchar(50)
AS
BEGIN
    BEGIN TRY
        IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL
        IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN
            declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey)
            IF @pKeyExisting is null BEGIN
                update rProduct set IsValid = 1, Product = @pName where ID = @pKey
            END ELSE BEGIN
                update Request set ProductID = @pKeyExisting where ProductID = @pKey
                update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey
                delete from rProduct where ID = @pKey
            END
        END
    END TRY BEGIN CATCH END CATCH
END

Now what if two people were using this at the exact same time? I really don't want to, nor do I have time (unfortunately), to get to fancy. K.I.S.S. is best in this case. :)

Answer 1

This post is the first result when searching the web. And the consensus transpires more or less: Always use transactions except in some cases. In fact, the answer is the opposite. Explicit transactions either by misuse or bad design or by simple bad luck can cripple not only a DB but the entire server or even entire infrastructures. I have seen it. We have several cases:

a) Transaction does not help at all. According to some answers and linked blog post we should use it just in case. Why? What doesn't help you, is in the way, get rid of it. So no, don't put transactions "just in case".

b) The transaction may be of some use, you have some doubts. Check your code and your data, What is the value of this transaction? Your job is to know. Leaving a transaction because you're not sure means you need to work more on your sql skills and the problem at hand.

c) I need this transaction. Really, I mean it. Now the question is, can I remove it? How can I minimize its impact? If you have trivial insertion/update/deletion use MERGE. MERGE is an atomic operation, it does not require an explicit transaction.

Let's say we have any task that needs to be atomic. And we have a piece of code that performs that task. Is it enough to add a Begin/commit/rollback transaction to this piece of code?

I'll let you choose. But the answer is no. What needs to be atomic is the result of the operations. Not the execution of a piece of code that we happen to have at hand. We need to review the code. Evaluate it. And rewrite it most of the time. The only operations that need to go inside the transaction are the operations that need to be atomic. Not the creation of a temporary table or the reading of an XML file.

Maybe we dont need atomic operation at all. Do we have a RowStatus column in some critical table? If we do we can mark our rows as RowStatus = 'Pending' or 'In Process' or 'Step 14'. That procedure where you want to put transactions, is it idempotent? If it is not, you must change it (with or without transactions). If it has no transactions, it must be possible to run it safely n consecutive times and each run must continue the work of the previous one or do nothing if job is done. If atomic is not REALLY required, idempotent procedures that do not need to start from scratch (no explicit transactions) are the best better choice. We can process everything like:

DO THIS WHERE RowStatus = Something. 

And finally, after verifying that everything is ok, (and maybe using a transaction) we do our final updates:

Update Table1 SET RowStatus ='Done' WHERE RowStatus ='Almost Done';
Update Table2 SET RowStatus ='Done' WHERE RowStatus ='Almost Done'
etc.

Final case You need to do a lot of things in different tables, And then some. And the whole thing must be atomic.

Option 1) Use a trigger. Yes a, trigger. Just don't put explicit transactions inside. Triggers have a bad reputation and the fair share that belongs to them is Triggers are executed within a transaction (That's why you can say rollback transaction within the trigger without having started any).

Option 2) Rewrite your procedure/script. Make all your remote captures/reading, calculations and heavy stuff in temp tables, variables or real tables in your BD (Create a Temp, Staging, Whatever schema in your DB to that purpose). No transactions. If you need to replicate all your database, so be it. Add some Try/Catch and checks to be sure everything is fine. And finally, Begin Try Begin transaction, do your atomic thing (which at this point, will be all trivial operations). and rollback/commit accordingly. Sample:

If you have this:

BEGIN TRY
    BEGIN TRANSACTION;
    --All my stuff
    IF @MyCheck = 'Error'
        THROW 51000, 'MyError', 1;
    IF @@TranCount > 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    --Other stuff
    IF @@TranCount > 0 ROLLBACK TRANSACTION;
END CATCH;

You need to refactor to this:

DECLARE @SomeThing int
DROP TABLE IF EXISTS #MyTable
CREATE TABLE #MyTable(MyColumn int);

TRUNCATE TABLE Temp.MyOtherTable
DELETE TABLE  Temp.MyOtherTable2 WHERE x IS NULL;
/*My Heavy stuff*/
--Bring data from linked server to #MyTable
--Bla bla etc

BEGIN TRY
    BEGIN TRANSACTION;
    MERGE MyRealTable T USING #MyTable... 
    MERGE MyRealTable2 T USING Temp.MyOtherTable... 
    IF @MyCheck = 'Error' THROW 51000, 'MyError', 1;
    IF @@TranCount > 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    --My catch stuff
    IF @@TranCount > 0  ROLLBACK TRANSACTION;
END CATCH;

Remarks: Never call a stored procedure inside transactions (or inside triggers). Or be very careful. Further modification of this procedure may destroy performance or create major headaches.

Answer 2

You use transactions when the set of database operations you are making needs to be atomic.

That is - they all need to succeed or fail. Nothing in between.

Transactions are to be used to ensure that the database is always in a consistent state.

In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.

---

Try/Catch blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.

Answer 3

Simple answer is to use transactions if you have more than 1 update or insert query in a single operation/function.

Answer 4

The common answer is that transactions allow database operations to be atomic. The confusion is in what this means. It's not about the particular operations involved whether they are SELECT, UPDATE, DELETE, etc. It's about the semantic meaning of the data itself. From the viewpoint of the operations, from the bottom-up, we say that as a group, they are atomic. But, from the abstract level, looking from the top-down, we say we have conservation of information.

An easy example would be if you had 2 accounts and you did not want money to be created nor destroyed in the transfer between them. Another, more subtle example, would be if you had a group of data that needed to be either created or destroyed as a group. In other words, having partial information doesn't make sense. I guess an example might be if you had a user and wanted to always guarantee they had a first and last name. Not a partial name.

With that said, people come up with phrases and rules of thumb to express what atomic means, such as "the operations all need to succeed or fail". Also, people tend to notice patterns, such as a SELECT would not need a transaction.