Is it necessary to use a transaction if other statements are dependent on the first?

Question

For so long, I've omitted using SQL Transactions, mostly out of ignorance.

But let's say I have a procedure like this:

CREATE PROCEDURE CreatePerson

AS
BEGIN

declare @NewPerson INT

INSERT INTO PersonTable ( Columns... ) VALUES ( @Parameters... )
SET @NewPerson = SCOPE_IDENTITY()

INSERT INTO AnotherTable ( @PersonID, CreatedOn ) VALUES ( @NewPerson, getdate() )

END
GO

In the above example, the second insert depends on the first, as in it will fail if the first one fails.

Secondly, and for whatever reason, transactions are confusing me as far as proper implementation. I see one example here, another there, and I just opened up adventureworks to find another example with try, catch, rollback, etc.

I'm not logging errors. Should I use a transaction here? Is it worth it?

If so, how should it be properly implemented? Based on the examples I've seen:

CREATE PROCEURE CreatePerson

AS
BEGIN TRANSACTION

....

COMMIT TRANSACTION
GO

Or:

CREATE PROCEDURE CreatePerson

AS
BEGIN
    BEGIN TRANSACTION

    COMMIT TRANSACTION
END
GO

Or:

CREATE PROCEDURE CreatePerson

AS
BEGIN

BEGIN TRY
    BEGIN TRANSACTION

    ...

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
END CATCH
END

Lastly, in my real code, I have more like 5 separate inserts all based on the newly generated ID for person. If you were me, what would you do? This question is perhaps redundant or a duplicate, but for whatever reason I can't seem to reconcile in my mind the best way to handle this.

Another area of confusion is the rollback. If a transaction must be committed as a single unit of operation, what happens if you don't use the rollback? Or is the rollback needed only in a Try/Catch similar to vb.net/c# error handling?

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

You are probably missing the point of this: transactions are suppose to make a set of separate actions into one, so if one fails, you can rollback and your database will stay as if nothing happened.

This is easier to see if, let's say, you are saving the details of a purchase in a store. You save the data of the customer (like Name or Address), but somehow in between, you missed the details (server crash). So now you know that John Doe bought something, but you don't know what. You Data Integrity is at stake.

Your third sample code is correct if you want to handle transactions in the SP. To return an error, you can try:

RETURN @@ERROR

After the ROLLBACK. Also, please review about:

set xact_abort on

as in: SQL Server - transactions roll back on error?

edited May 23 '17 at 11:43

Community

1
1

answered Mar 16 '15 at 22:54

Juan Carlos Eduardo Romaina Ac

256
4
8

I think I'm more in favor of xact_abort on after a bit of studying... seems easier to deal with if specific error handling isn't really needed. – user1447679 Mar 17 '15 at 02:35
If I were to use xact_abort on, taking a look at my first and second example... Is the first one sufficient? Is there a benefit to having BEGIN and then BEGIN TRANS? Or is that when you'll have multiple transaction blocks? – user1447679 Mar 17 '15 at 02:36
The first BEGIN is for the start of the Stored Procedure. The BEGIN TRANS is for the start of the transaction. I recomend you do it this way to keep it organized. Also, xact_abort on will work the same way in any of them, problem is that this doesn't catch all possible errors, so it is better for you to use a TRY CATCH. – Juan Carlos Eduardo Romaina Ac Mar 18 '15 at 01:33

score 0 · Answer 2 · answered Mar 16 '15 at 22:10

0

If the first insert succeeds and the second fails you will have a database in a bad state because SQL Server cannot read your mind. It will leave the first insert (change) in the database even though you probably wanted it all tosucceed or all fail.

To ensure this you should wrap all the statements in begin transaction as you illustrated in the last example. Its important to have a catch so any half completed transaction are explicitly rolled back and the resources (used by the transaction) released as soon as possible.

answered Mar 16 '15 at 22:10

benjamin moskovits

5,261
1
12
22

How would the try/catch effect returning an error output parameter back to the client in that case? Works all in the same? – user1447679 Mar 16 '15 at 22:13
As soon as you enter the catch you can preserve the values set by the error. You can use raiseerror to rethrow the error. For an example of this see:http://sqlblog.com/blogs/roman_rehak/archive/2007/12/01/how-to-rethrow-errors-in-t-sql.aspx – benjamin moskovits Mar 16 '15 at 23:03

Is it necessary to use a transaction if other statements are dependent on the first?

2 Answers2