SQL Server transaction rollback (1)

SQL transaction

First, the concept of business
A transaction is a mechanism, a sequence of operations, that contains a set of database operations commands that either execute all or do not. Therefore, a transaction is an inseparable unit of work logic. The transaction is used as the smallest control unit when concurrent operations are performed on the database system. This is especially applicable to multi-user simultaneous operation of the data communication system. For example: booking, banking, insurance and securities trading system.

Second, transaction properties
Transaction 4 Large Properties:
1 atomicity (atomicity): A transaction is a complete operation.
2 Consistency (consistency): When a transaction completes, the data must be in a consistent state.
3 Isolation (Isolation): All concurrent transactions that modify data are isolated from each other.
4 Persistence (Durability): After a transaction completes, its effect on the system is permanent.

Third, create a transaction
Statements that manage transactions in T-sql:
1 starting transaction: BEGIN TRANSACTION
2 COMMIT TRANSACTION: COMMIT Transaction
3 Rolling back transactions: ROLLBACK TRANSACTION

Transaction classification:
1 Explicit transactions: explicitly specify the beginning of a transaction with BEGIN transaction.
2 Implicit transaction: Open implicit transaction: Set implicit_transactions on, when operating in implicit transaction mode, SQL Servler automatically starts a new transaction after committing or rolling back the transaction. Cannot describe the beginning of a transaction, only the transaction must be committed or rolled back.
3 autocommit transaction: The default mode of SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is automatically committed or rolled back. 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] () 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 = O N) 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:

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 a bit there are ' 1 ', ' Name1 ' and ' 3 ', ' Name3 ',
stating that only the second sentence of the error was canceled.
*/

Method 1: Open Xact_abort for all rollbacks

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 you execute transact- SQLStatement produces a run-time error,
The entire transaction terminates and is rolled back.
It is the off state by default.
*/

All Rollback 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 error generated within a TRY block causes the state of the current transaction to expire,
The transaction is categorized as a non-committed transaction.
If the error that aborts the transaction usually occurs outside the try block,
Will cause the transaction to enter a non-committed state.
Non-committed transactions can only perform read operations or ROLLBACK TRANSACTION。
The transaction cannot perform any of the possible write operations or COMMIT TRANSACTIONThe transact- SQLStatement.
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

Use TestDB
Declare @tranError INT--Define variables
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
/*
Customize a variable to determine if an error has occurred in the end.
*/

Finally, note that if a transaction writes Begin TransAction and does not write Commit TransAction or Rollback TransAction Then the data about the operation (perhaps the table, perhaps the column, which I have not tested ...) ) will be locked ... The solution for locking is to execute the commit TransAction or Rollback alone TransAction

Reproduced in this article

SQL Server transaction rollback (1)