sql--transactions

SQL transaction:

1. Definition:

A transaction is a series of operations performed as a single logical unit. Multiple operations are submitted to the system as a whole, either executed or not executed, and the transaction is an inseparable unit of work logic. This is especially applicable to multi-user simultaneous operation of the data communication system. For example: booking, banking, insurance and securities trading system.

2. Features that a transaction must have:

1) atomicity: atomicity: A transaction is a complete operation, and an element is non-divided. Transaction so the element must be committed or rolled back as a whole. If any element in the XV fails, the entire transaction fails.

2) Consistency: Consistency: When the transaction is complete, the data must be in a consistent state.

3) Isolation: lsolation: All concurrent transactions that modify data are isolated from each other and do not depend on or affect other transactions in any way.

4) Persistence: The results of transaction processing are permanent regardless of whether the system has failed.

3. How to perform a transaction:

1) Start transaction: BEGIN TRANSACTION

2) COMMIT TRANSACTION: Commit Transaction

3) ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

4. Transaction classifications

1) Show transactions: explicitly specify the start of a transaction with BEGIN TRANSACTION

2) Implicit transaction: Sets the implicit transaction mode to open by setting the SET IMPLICIT_TRANSACTIONS on statement. When operating in an implicit transaction mode, SQL Server automatically starts a new transaction after the transaction is committed or rolled back. You do not need to describe the start of each transaction, as long as each transaction is committed or rolled back.

3) autocommit transaction: This is the default mode for SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is automatically committed. If an error occurs, it is automatically rolled back.

5. Example

XXXXXX business logic

BEGIN TRANSACTION--Start a transaction

DECLARE @error INT--Define variables and accumulate errors during transaction execution

Set @error = 0

-----EXECUTE Statement 1

Set @error = @error + @ @error--Cumulative error

----EXECUTE Statement 2

Set @error = @error + @ @error--Cumulative error

------

--Judgment

If @error <> 0--wrong

Begin

print ' ROLLBACK TRANSACTION '

ROLLBACK TRANSACTION

End

Else

Begin

print ' Commit transaction '

Commit TRANSACTION

End

sql--transactions