Commit tran in SQL

3

I have doubts that my SP is correct since I do not know if it should be used only once begin try and those instructions, apart it returns a 1 but does not do update :

ALTER PROCEDURE [dbo].[SP_EliminarVariableFormula]
    @IdVariableElimina int, @ValorElimina varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE  @Activo bit = 0    


    if @ValorElimina <> '' 
    BEGIN TRY
        BEGIN TRAN  
            UPDATE ValoresDeVariables SET [email protected] WHERE [email protected] AND [email protected]
        COMMIT TRAN
        SELECT 1
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT 0, 'Hubo un error.'
    END CATCH
    else
    BEGIN TRY
        BEGIN TRAN  
            UPDATE ValoresDeVariables SET [email protected] WHERE [email protected]
        COMMIT TRAN
        SELECT 1
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT 0, 'Hubo un error.'
    END CATCH
END
    
asked by Ivxn 21.05.2018 в 21:14
source

1 answer

2

Simplifying a bit, your procedure is something like this:

Si <condicion>
   udpate 1
sino
   update 2

Therefore your updates are exclusive, either one is executed or the other is executed. Never both. Now, Why would we need to use a transaction in this case?

The usual use of a transaction is to provide consistency in an operation that involves at least two sentences, suppose you had to update ValoresDeVariables but before, insert a record in a second table, for example ValoresDeVariablesLog to register the pre-upgrade value, again, it's just an example.

INSERT INTO ValoresDeVariablesLog (IDVariable, Activo, Fecha)
       SELECT IDVariable, Activo, GETDATE()
              FROM ValoresDeVariables
              WHERE [email protected]

UPDATE ValoresDeVariables 
       SET [email protected] 
       WHERE [email protected]

In this case, let's imagine that we need the two operations to be atomic, that is to say that both or neither work so that the operation is consistent. Without a transaction, which could happen eventually, the first insert works and the update final% does not. That's exactly what we did not want at first and that's what transactions are for, if any sentence fails, we will execute a ROLLBACK if on the contrary everything works, we will do a COMMIT , there are several ways to build a transaction sentence, it is is one of them, using the TRY/CATCH :

BEGIN TRANSACTION MiTransaccion
BEGIN TRY
    INSERT INTO ValoresDeVariablesLog (IDVariable, Activo, Fecha)
           SELECT IDVariable, Activo, GETDATE()
                  FROM ValoresDeVariables
                  WHERE [email protected]

    UPDATE ValoresDeVariables 
           SET [email protected] 
           WHERE [email protected]

    COMMIT TRANSACTION MiTransaccion
END 
BEGIN CATCH
    ROLLBACK TRANSACTION MiTransaccion
END

A clarification, a transaction is usually undone in the face of a database error, which is what we are doing with the TRY/CATCH however it could also be undone by a business logic, but this is obviously controlled in another way since you do not have an error but a state / condition that you should eventually verify.

Now let's go back to the original question: Why would we need to use a transaction in this case? The answer: in this case it does not make sense . Why? well we have already seen that there are two update exclusionary sentences, and the other important data, is that every statement of update/insert/delete is atomic, that is, it is executed completely or not. It could never be the case that some of this operations is executed halfway. Implicitly when you do:

UPDATE ValoresDeVariables SET [email protected] WHERE [email protected] AND [email protected]

or the other statement, the engine handles the same as if it were a transaction, so to indicate it explicitly, is redundant.

Clarification : I do not detail it so as not to extend myself, but a transaction can also manage the atomicity of the data that is read, but neither does it seem to be the case in this example.

    
answered by 21.05.2018 / 22:16
source