SQL SERVER TRANSACTION Things

1. The concept of a transaction

A thing is a mechanism, a sequence of operations that contains a set of operations commands for a database that either executes entirely or does not execute. So the thing is a set of inseparable things logical unit, when the database carries on the concurrency operation, the thing is used as the smallest control unit, this is especially suitable for the multi-user simultaneous operation data communication system. For example: booking, banking, insurance and securities trading system.

2.4 major attributes of a thing

• Atomicity: A thing is a complete operation;
• Isolation: All concurrent things that modify data are isolated from each other;
• Consistency: When things are done, things must be in a consistent state;
• Persistence: After the completion of the thing, the impact on the system is permanent;

3. Creating things

• Start things: Transaction begin
• Commit a thing: Commit Transaction
• Rolling back things: Rollback TRANSACTION

4. Classification of Things

• Show things: BEGIN TRANSACTION The beginning of a specified thing with a COMMIT TRANSACTION, ROLLBACK TRANSACTION to end or rollback a transaction
• Implicit things (auto-commit things): Implicit transactions automatically commit transactions after a statement is completed

5. Examples

To achieve the transfer operation, the sender transfers and the account receivable is a complete set of operation sequence, must complete or not complete, prepare a User wallet table (tbuserwallet), Transfer Transaction form (Tbtransaction), simple design as follows

Add test data to the User Wallet table (tbuserwallet)

Create a transfer store thing

Use [test]go/****** object:storedprocedure [dbo].    [Paddtransaction] Script date:2018/5/21 12:44:59 ******/set ansi_nulls ongoset quoted_identifier ongo--================================ =============--author:yangyi--Create date:18/05/21--Description: Transfer--=========================================== ==create PROCEDURE [dbo]. [Paddtransaction] @InOpenID uniqueidentifier, @InTDesc nvarchar, @OutOpenID uniqueidentifier, @OutTDesc nvarchar ( @TAmount Decimal (2), @TTID int, @Result int outputasbeginif (SELECT Amount from Tbuserwallet WHERE [email  Protected]) >[email protected]) beginbegin transactionbegin tryupdate tbuserwallet SET [email protected] where [email protected]update tbuserwallet SET [email protected] where [email protected]--select 1+ ' A ' INSERT into [dbo]. [Tbtransaction] ([Tid],[openid],[tamount],[ttid],[tdesc],[createdt]) VALUES (NEWID (), @InOpenID, @TAmount, @TTID, @InTDesc, GETDATE ()) INSERT into [dbo]. [Tbtransaction] ([Tid],[openid],[tamount],[tTid],[tdesc],[createdt]) VALUES (NEWID (), @OutOpenID, [email protected], @TTID, @OutTDesc, GETDATE ()) END Trybegin Catchif (@ @TRANCOUNT >0) beginset @Result =-1print ' Things perform error, rollback ' ROLLBACK transactionendend catchif (@ @TRANCOUNT >0) Beginset @Result =1print ' Everything is executed as expected ' COMMIT transactionendendelsebeginprint ' insufficient money ' SET @Result =0endendgo

Test 1>: Insufficient money transfer test

use [Test]godeclare@return_value int, @Result intexec@return_value = [dbo]. [Paddtransaction] @InOpenID = ' 1ccd524d-de62-47ca-87d3-38787b040ba3 ', @InTDesc = N ' received the transfer of a ', @OutOpenID = ' 2ccd524d-de62-47ca-87d3-38787b040ba3 ', @OutTDesc = N ' transfer to A100 ', @TAmount = @TTID = 1, @Result = @Result Outputselect@result as N ' @Result ' GO

Test 2>: Simulate things with errors, roll back

To cancel a store: SELECT 1+ ' A ' comment (an error occurred in a simulated thing)

use [Test]godeclare@return_value int, @Result intexec@return_value = [dbo]. [Paddtransaction] @InOpenID = ' 2ccd524d-de62-47ca-87d3-38787b040ba3 ', @InTDesc = N ' received the transfer of a ', @OutOpenID = ' 1ccd524d-de62-47ca-87d3-38787b040ba3 ', @OutTDesc = N ' transfer to A100 ', @TAmount = @TTID = 1, @Result = @Result Outputselect@result as N ' @Result ' GO

　　

Test 3.> perform a successful test, commenting on SELECT 1+ ' A '

use [Test]godeclare@return_value int, @Result intexec@return_value = [dbo]. [Paddtransaction] @InOpenID = ' 2ccd524d-de62-47ca-87d3-38787b040ba3 ', @InTDesc = N ' received the transfer of a ', @OutOpenID = ' 1ccd524d-de62-47ca-87d3-38787b040ba3 ', @OutTDesc = N ' transfer to A100 ', @TAmount = @TTID = 1, @Result = @Result Outputselect@result as N ' @Result ' GO

SQL SERVER TRANSACTION Things