SQL Database Transaction Store procedure Practice

Database transactions:

A database transaction is a series of operations performed as a single logical unit of work (Transaction) .

Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully.

Imagine a transaction of online shopping where the payment process includes at least the following steps of database operations:

Update the inventory information of the products purchased by customers

Save customer payment information - may include interactions with the banking system

Generate orders and save to database • Update user-related information, such as number of purchases, etc. normally, these operations will proceed smoothly, the final transaction is successful, and all database information related to the transaction is updated successfully. However, if there is a mistake in any part of the process, such as an exception when updating commodity inventory information, the customer's bank account is under-deposited, and so on, will cause the transaction to fail. Once the transaction fails, all information in the database must remain the same as before the transaction, such as failure to update the user information in the last step, resulting in the failure of the transaction, then it must be ensured that the failed transaction does not affect the state of the database -The inventory information is not updated, The user also did not pay, and the order was not generated. Otherwise, the database information will be chaotic and unpredictable.

Database transactions are the technology used to ensure the smoothness and predictability of transactions in this situation.

BEGIN Tran (or transaction) -- start transaction

Commit-- COMMIT TRANSACTION

Rollback--rollback transaction

Transaction characteristics:

A atomicity (atomicity)

C Conformance (consistency)

I Isolation (isolation)

D Persistence (durability)

@ @ERROR is a condition that determines whether a transaction is wrong, the error-free value is 0, and the error value is not 0.

Transaction creation and usage examples:

Select *  fromCangkubegin Tran  --Start a transactionInsert  intoCangkuValues(10008,'Boxster', -,Ten,1004)--Nothing wrong .if @ @ERROR >0begin-- Every execution statement is written in order to make the last sentence if there is an error ,--no matter how many statements are executed, the following will not be executed, directly to Tranrollback    GotoTranrollback--Tranrollback to the last execution statementEndInsert  intoCangkuValues(10002,'Aurora',66.50,Ten,1002)--PRIMARY KEY constraintif @ @ERROR>0    GotoTranrollbackInsert  intoCangkuValues(10009,'XF', +,Ten,1003)--Nothing wrong .if @ @ERROR>0beginTranrollback:--Need to add a colon    rollback Tran        --rollback of commands executed in all transactions (undo all execution statements) endElsebegin    Commit Tran   -Commit the transaction (only the real commit is the real change to the data in the database)End

Exercise: Create three tables and write a simple stored procedure that requires you to purchase, ship, and print a small ticket.

In the absence of this goods and for the purchase, add this line of information;

In the absence of this goods and for shipping, print without this cargo!

The following starts the execution of the statement:

Statement one: Incoming goods, numbered 1001 of the cargo added ten

Statement two: goods shipped with 1001 number 5 units

A small ticket is printed each time it is shipped and the quantity is sufficient.

Statement three: The new arrival number is 1021

Statement IV: Shipment, number 1022 goods shipped, but the warehouse does not have this number of goods

SQL Database Transaction Store procedure Practice