Transaction rollback of SQL Server

First create a data table in the test library TestDB:

 Use [TestDB]GO/** * * object: Table [dbo]. [Person] * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE TABLE [dbo].[ Person](    [PersonId] [nchar]( -) not NULL,    [PersonName] [nchar]( -) 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 in the execution of a transaction, only the error action statement is rolled back (that is, the sentence is not executed, the rollback is counted), and the correct operation statement before or after the error is still committed. Such as:

 UseTestDBBegin TransAction    Insert  intoPerson (Personid,personname)Values('1',' Zhang')    Insert  intoPerson (Personid,personname)Values('1','Wang')    Insert  intoPerson (Personid,personname)Values('3','Sun')Commit TransAction/*Select has ' 1 ', ' Zhang ' and ' 3 ', ' Sun ', stating that only the second sentence of the error has been canceled.*/

Cause of the problem:

The "xact_abort" switch specifies whether SQL Server automatically rolls back to the current transaction when a run-time error occurs for the Transact-SQL statement. When SET Xact_abort is on, the entire transaction terminates and rolls back if the Transact-SQL statement produces a run-time error. When SET Xact_abort is OFF, sometimes only the Transact-SQL statement that produces the error is rolled back, and the transaction continues processing. if the error is severe, the entire transaction may be rolled back even if SET xact_abort is OFF. OFF is the default setting. compilation errors, such as syntax errors, are not affected by SET Xact_abort. For most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statement in the implicit or display transaction to ON. The only case where this option is not required is when the provider supports nested transactions.

Method 1: Open Xact_abort for all rollbacks

 UseTestDBSETXact_abort on --OpenBegin TransAction    Insert  intoPerson (Personid,personname)Values('1','Zhang')    Insert  intoPerson (Personid,personname)Values('1','Wang')    Insert  intoPerson (Personid,personname)Values('3','Sun')Commit TransAction/*When SET Xact_abort is on, the entire transaction terminates and rolls back if the Transact-SQL statement produces a run-time error. It is the off state by default. */

All Rollback Method 2: Use Try ... Catch

 UseTestDBBeginTryBegin TransAction        Insert  intoPerson (Personid,personname)Values('1','Zhang')        Insert  intoPerson (Personid,personname)Values('1',' Wang')        Insert  intoPerson (Personid,personname)Values('3','Sun')    Commit TransActionEndTryBeginCatchRollback TransActionEndCatch/*use Trycatch to catch exceptions.    If an error generated within a TRY block causes the state of the current transaction to become invalid, the transaction is categorized as a non-committed transaction.    If an error that aborts a transaction outside the try block typically occurs within a try, it causes the transaction to enter a non-committed state.    Non-committed transactions can only perform read operations or ROLLBACK TRANSACTION.    The transaction cannot perform any Transact-SQL statements that could generate a write operation or COMMIT TRANSACTION. If the transaction is classified as a non-committed transaction, the Xact_state function returns a value of 1. */

Rollback All Method 3: Custom error variables

 UseTestDBDeclare @tranError int --Defining VariablesSet @tranError=0    Begin TransAction        Insert  intoPerson (Personid,personname)Values('1','Zhang')            Set @tranError = @tranError + @ @Error        Insert  intoPerson (Personid,personname)Values('1','Wang')            Set @tranError = @tranError + @ @Error        Insert  intoPerson (Personid,personname)Values('3','Sun')            Set @tranError = @tranError + @ @Error    If @tranError = 0        Commit TransAction    Else        Rollback TransAction/*customize a variable to determine if an error has occurred in the end. */

Special attention:

If a transaction writes the Begin TransAction and does not write Commit TransAction or Rollback TransAction then the associated operation data is locked. The solution for locking is to execute the commit TransAction or Rollback TransAction alone.

Therefore, when using SQL statements directly on the production server, use no write Commit TransAction or Rollback TransAction the following statement:

Begin TransAction Actionable         SQL statements

After execution, if the operation result is correct, commit TransAction is executed separately, and if the operation result is incorrect, the rollback TransAction is executed separately.

Transaction rollback of SQL Server