SQL thing usage "Go"

SQL transaction One, the transaction concept transaction is a mechanism, is a sequence of operations, it contains a set of database operations commands, the set of commands are either all executed, or all do not execute. 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. Two, transaction property transactions 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. Iii. creating statements for managing transactions in a transaction T-SQL: 1 start transaction: Begin Transaction2 COMMIT TRANSACTION: Commit Transaction3 ROLLBACK TRANSACTION: ROLLBACK TRANSACTION Transaction Classification: 1 explicit transaction: Begin TRA Nsaction explicitly specifies the start of the 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. Example: Zhang San to $800 to the John Doe account. Use studbgo--create Account table Bank--if exists (select* from sysobjects where name= ' bank ') drop table bankcreate Table Bank (custo Mername Char (10),--Customer name Currentmoney money--current balance) go/*--add constraint, account cannot be less than--*/alter table Bank add Constrai NT Ck_currentmoney Check (currentmoney>=1)/*--Insert test data--*/insert into bank (Customername,currentmoney) SelecT ' Zhang San ', Unionselect ' John Doe ', 1select * from bankgo/*--using transaction--*/use studbgo--to restore the original data--update bank set currentmoney= currentMoney-1000 where Customername= ' Li ' Set NOCOUNT on--does not show the number of rows affected print ' View the balance before the transfer transaction ' select * from bankgo/*--start transaction--*/beg  In Transactiondeclare @errorSum INT--Defines the variable that is used to accumulate errors during transaction execution/*--transfer--*/update Bank set currentmoney=currentmoney-800 where Customername= ' Zhang San ' set @[email protected][email protected] @error--Cumulative Error update bank set currentmoney= currentmoney+800 where customername= ' John Doe ' set @[email protected][email protected] @error--Cumulative error print '        View balances during Transfer transactions ' select * from bank/*--determines whether a transaction commits or rolls back based on an error--*/if @errorSum >0 begin print ' transaction failed, rollback transaction. ' ROLLBACK TRANSACTION Endelse begin PRINT ' transaction successful, commit transaction, write to hard disk, save forever! ' Commit Transaction Endgoprint ' view the balance after transfer ' select * from Bankgo

　　This article is from: http://blog.csdn.net/zerolsy/article/details/2123637

SQL thing usage "Go"