Home / Questions / 166407
Accepted
Ivxn
Asked: 2020-05-22 11:14:18 +0800 CST 2020-05-22 11:14:18 +0800 CST

Commit tran in SQL

  • 772

I have doubts that my SP is correct since I don't know if it should be used only once begin tryand those instructions, apart from that, it returns a 1 but it doesn't do the 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 Activo=@Activo WHERE IDVariable=@IdVariableElimina AND ValorDeVariable=@ValorElimina
        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 Activo=@Activo WHERE IDVariable=@IdVariableElimina
        COMMIT TRAN
        SELECT 1
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT 0, 'Hubo un error.'
    END CATCH
END
sql

1 Answers

  1. Best Answer

    Simplifying a bit, your procedure is something like this:

    Si <condicion>
   udpate 1
sino
   update 2

    Therefore your updatesare exclusive, or 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 minimally involves at least two statements, suppose you had to update ValoresDeVariablesbut before, insert a record in a second table, for example ValoresDeVariablesLogto record the value before the update, again, it is just an example.

    INSERT INTO ValoresDeVariablesLog (IDVariable, Activo, Fecha)
       SELECT IDVariable, Activo, GETDATE()
              FROM ValoresDeVariables
              WHERE IDVariable=@IdVariableElimina

UPDATE ValoresDeVariables 
       SET Activo=@Activo 
       WHERE IDVariable=@IdVariableElimina

    In this case, imagine that what we need is for the two operations to be atomic, that is, for both to work or neither for the operation to be consistent. Without a transaction, which could happen eventually, the first one insertworks and the updatelast one doesn't. That is exactly what we did not want at first and that is what transactions are for, if any statement fails, we will execute a ROLLBACKif on the contrary everything works, we will do a COMMIT, there are several ways to build a transactioned statement, this 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 IDVariable=@IdVariableElimina

    UPDATE ValoresDeVariables 
           SET Activo=@Activo 
           WHERE IDVariable=@IdVariableElimina

    COMMIT TRANSACTION MiTransaccion
END 
BEGIN CATCH
    ROLLBACK TRANSACTION MiTransaccion
END

    For clarification, a transaction is normally rolled back on a database error, which is what we're doing with TRY/CATCHit however it could also be rolled back by business logic, but this is obviously handled differently as you don't have an error but a state/condition that you should eventually check.

    Now back to the original question: why would we need to use a transaction in this case? The answer: in this case it doesn't make sense . Why? Well, we have already seen that there are two updateexclusive statements, and the other important data is that every statement of update/insert/deleteis atomic, that is, it is executed completely or it does not. It could never be the case that any of these operations are executed halfway. Implicitly when you do:

    UPDATE ValoresDeVariables SET Activo=@Activo WHERE IDVariable=@IdVariableElimina AND ValorDeVariable=@ValorElimina

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

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

    • 2