Database Transaction example (cainiao Edition)

Begintrans: Start transaction
Committrans: commit a transaction
Rollbacktrans: roll back the transaction
The transaction is atomic, either not executed or fully executed. Once the transaction is successfully executed, it is permanently saved. The following is an example of a transfer transaction application.

1. Create a database

-----------------------------------------------------------------------

If not exists (select * From sysobjects where name = 'bank ')
Begin
Create Table bank
(
Bankid int identity (1, 1) primary key,
Username varchar (50) not null,
Rmbnum float not null
)
End

------------------------------------------------------------------------

 

2. Insert data

------------------------------------------------------------------------

Insert into Bank (username, rmbnum) values ('zhang san', 10000)
Insert into Bank (username, rmbnum) values ('lily', 10000)
Insert into Bank (username, rmbnum) values ('王', 10000)
Insert into Bank (username, rmbnum) values ('Children, 10000)

-------------------------------------------------------------------------

 

3. Limit the account deposit not less than 0

-------------------------------------------------------------------------

Alter table bank add constraint ck_bank_rmbnum check (rmbnum> 0)

-------------------------------------------------------------------------

 

4. Execute the transfer (normal version) to transfer 200 million of Michael Jacob's account to Mr. Li's account (Mr. Li successfully added the money because Michael Jacob's account balance was insufficient and he failed to deduct the money. So the bank will not lose 20000 ?)

--------------------------------------------------------------------------

Declare @ howmuch float
Set @ howmuch = 20000
Update Bank set rmbnum = rmbnum-@ howmuch where username = 'zhangsan'
Update Bank set rmbnum = rmbnum + @ howmuch where username = 'Li si'

---------------------------------------------------------------------------

5. Execute the transfer (transaction Version)

Select * From bank

-- View the balance before transfer

1 Zhang San 10000
2 Li Si 10000
3. Wang Wu 10000
4 pediatric 10000

 

Execute business

----------------------------------------------------------------------------

Begin TransAction
Declare @ errno int
Declare @ num int
Set @ errno = 0
Set @ num = 100000
-- Remove Wang Wu's account @ num
Update Bank set rmbnum = rmbnum-@ num where username = 'wang wu'
Set @ errno = @ errno + @ error -- add the error number generated during execution
-- Add @ num to the pediatric account
Update bank set rmbnum = rmbnum + @ num where username = 'pediatric'
Set @ errno = @ errno + @ error

-- Determines whether the transaction is committed or canceled based on whether errors are generated.
If @ errno> 0
Begin
Print ('transaction processing failed, roll back the transaction! ')
Rollback TransAction
End
Else
Begin
Print ('transaction processed successfully, commit transaction! ')
Commit TransAction
End

------------------------------------------------------------------------------

Running result

(One row is affected)

Transaction Processing failed. roll back the transaction!

Select * From bank

1 Zhang San 10000
2 Li Si 10000
3. Wang Wu 10000
4 pediatric 10000

The query database data has not changed (Transfer failed, and the Update statement has not been executed)