Analysis of stored procedure containing transaction in SQL Server

Time:2021-7-7

Let’s start with a concept

Database transaction refers to a series of operations executed as a single logical unit of work, either executed completely or not. Then, adding a transaction to a stored procedure can ensure that all SQL codes in the transaction are either executed completely or not executed at all.

For example, a simple stored procedure with transactions:

Begin
    Set NOCOUNT ON; -- Does not return the number of affected rows
    Set XACT_ ABORT ON; -- Xact needs to be turned on to execute transactions using stored procedures_ Abort parameter (default is off)

      Delete from table1 where name = '-- delete data sql1
    Begin tran tran1 -- start a transaction tran1
      Delete from table1 where name = '-- delete data SQL2
    Save tran tran2 -- save a transaction point tran2
      Update table2 set name = '"where id =' -- modify data SQL3

      If @ @ error < > 0 -- judge whether there is an error in modifying the data (@ error means to return the non-zero error code of the statement closest to @ @ error (i.e. SQL3). If there is no error, return 0)

        begin
          Rollback tran tran2 -- rollback the transaction to the restore point of tran2
          Commit tran tran1 -- commit tran1
        end
      Else -- commit transaction tran1 if there is no error
        Commit tran tran1 -- commit tran1
  End

If the execution of SQL3 fails, it will be rolled back to the creation of tran2 (equivalent to no execution except the execution of sql1 and SQL2).