SQL Server Transactions

SQL Server Transactions

　　

• Defined

　　　　

A transaction (TRANSACTION) is a series of operations performed as a single logical unit of work and is an inseparable unit of work that is used as the smallest control unit when concurrent operations are performed on the database system.

All of the database operations commands that he contains are either committed or revoked as a whole, and the set of database operations commands are executed or not executed .

• Four characteristics

　　

1. Atomicity: The smallest control unit, no further points, either executed, or not executed
2. Consistency: When a transaction completes, the data must be consistent (before the transaction, the data in the store is consistent and the data integrity is guaranteed)
3. Isolation: Multiple transactions that modify data are isolated from each other and do not affect each other
4. Persistent: After a transaction is completed, its modifications to the database are persisted, and the transaction log maintains a permanent transaction

• Classification:

• • Show transactions: The most common way to transact transactions, using begin transation to clarify the start of a transaction
  • Implicit transaction: Sets the implicit transaction mode to open by setting the SET IMPLICIT_TRANSACTIONS on statement, and the next statement automatically starts a new transaction. When the transaction completes, the next T-SQL statement will start a new transaction
  • Autocommit transactions: This is the default mode for SQL Server, which treats each individual T-SQL statement as a transaction and commits automatically if executed successfully; automatically rollback if an error occurs

• Grammar

Start transaction: Begin Tran[saction]

Execution transaction: COMMIT Tran[saction]

ROLLBACK TRANSACTION: ROLLBACK Tran[saction]

Save rollback point: Save Tran[saction]

• Error in judgment statement

@ @ERROR

@sumerror = @sumerro [email protected] @ERROR

• Example

Pre-transaction Data graph:

　　　　　　　　

Requirements:

Liu Bei gave Zhang Fei 30 yuan

Execute code:

BEGIN TRANTran_money--Start a transactionDECLARE @tran_error int;SET @tran_error = 0; BEGINTRYUPDATETb_moneySETMymoney=Mymoney-  - WHEREName= 'Liu Bei'; SET @tran_error = @tran_error + @ @ERROR; --test the error code to see if Liu Bei's money is reduced and whether Guan Yu's money will increase        --SET @tran_error = 1;        UPDATETb_moneySETMymoney=Mymoney+  - WHEREName= 'Guan Yu'; SET @tran_error = @tran_error + @ @ERROR; ENDTRYBEGINCATCHPRINT 'An exception occurred, error number:' + Convert(varchar, Error_number ())+ ', error message:' +error_message ()SET @tran_error = @tran_error + 1ENDCATCHIF(@tran_error > 0)    BEGIN        --performing an error, rolling back a transaction        ROLLBACK TRAN; PRINT 'transfer failed, cancel transaction!'; ENDELSE    BEGIN        --no exception, COMMIT transaction        COMMIT TRAN; PRINT 'Transfer Success!'; END

SQL Server Transactions