Database Basics (Small exercises for database transactions and stored procedures)

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 interaction with the banking system

generate orders and save to database ·   Update user-related information, such as the number of purchases, etc. normally, these operations will proceed smoothly, the final transaction is successful, and all the database information related to the transaction is also successfully updated. 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 the information in the database must remain unchanged 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 has not been updated, the user has not paid, and the order has not been 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:

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, number 1001 added 10

Statement two: 1001 Goods shipped 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 four: Shipment, number 1022 of the goods shipped, but the warehouse does not have this number of goods

Database Basics (Small exercises for database transactions and stored procedures)