SQL Server TransAction all rollback

The table structure is as follows:

USE [TestDB]
GO
/***** Object: Table [dbo]. [Person] script Date: 11/23/2008 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create table [dbo]. [Person] (
[PersonId] [nchar] (18) not null,
[PersonName] [nchar] (20) not null,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

 

By default, if an error occurs during the execution of a transaction, only the wrong operation statement will be rolled back (that is, this statement will not be executed, but will not be rolled back ), the correct statement before or after the error is submitted. For example:

Use TestDB

Begin TransAction
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('3', 'name3 ')
Commit TransAction
/*
Select values include '1', 'name1', and '3', 'name3 ',
This indicates that only the error in the second sentence has been canceled.
*/

 

Method 1 for all rollback: Open XACT_ABORT

Use TestDB
SET XACT_ABORT ON -- open
Begin TransAction
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('3', 'name3 ')
Commit TransAction
/*
When SET XACT_ABORT is ON,
If a running error occurs when you execute a Transact-SQL statement,
The entire transaction is terminated and rolled back.
It is OFF by default.
*/

 

Method 2: Use Try... Catch

Use TestDB
Begin Try
Begin TransAction
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Insert Into Person (PersonId, PersonName)
Values ('3', 'name3 ')
Commit TransAction
End Try
Begin Catch
Rollback TransAction
End Catch
/*
Use TryCatch to catch exceptions.
If the Errors generated in the TRY block cause the current transaction status to fail,
The transaction is classified as a non-commit transaction.
If an error occurs during TRY,
The transaction enters the uncommitted state.
Transactions that cannot be committed can only be read or rollback transaction.
This TRANSACTION cannot execute any Transact-SQL statements that may generate write operations or COMMIT TRANSACTION.
If a transaction is classified as a non-commit transaction, the XACT_STATE function returns-1.
*/

 

Method 3: custom error Variables

Use TestDB
Declare @ tranError int -- defines the variable
Set @ tranError = 0
Begin TransAction
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Set @ tranError = @ tranError + @ Error
Insert Into Person (PersonId, PersonName)
Values ('1', 'name1 ')
Set @ tranError = @ tranError + @ Error
Insert Into Person (PersonId, PersonName)
Values ('3', 'name3 ')
Set @ tranError = @ tranError + @ Error
If @ tranError = 0
Commit TransAction
Else
Rollback TransAction
/*
Customizes a variable to determine whether an error has occurred.
*/

 

 

Note: If a TransAction writes Begin TransAction but does not write Commit TransAction or Rollback TransAction, the data of the related operation (maybe a table or a column, I haven't tested this yet ...) Will be locked... The lock solution is to execute Commit TransAction or Rollback TransAction separately.