SQL Server Transactions

Brief introduction

A transaction is a single unit of work, which means that there are multiple operations within the unit and that the transaction is a consolidation of multiple operations. If a transaction executes successfully, all data operations that are covered in the transaction are committed together and become a permanent part of the database.

If a transaction executes an error due to an operation, all operations within the transaction are invalidated, the transaction is rolled back, and the data operation reverts to the state that was initially changed.

Why transactions are required

In the banking business, there is a principle of accounting, that is, borrowing loans, loans are equal. To ensure this principle, every bank transaction occurs, it is necessary to ensure that the debit and credit accounts are at least one account, and that the two accounts either succeed at the same time or fail at the same time .

If the records on the account have only been debited, or only credit has occurred. Then this violates the principle of accounting, there is a case of accounting errors. This business scenario is a typical representation of transactions that can be addressed by transaction processing mechanisms to address such business scenarios.

　　

Sample Analysis

　　Business Scenario:

Bank transfers often involve two or more accounts, including the transfer of objects and two of the transferred objects. Two When the transferred deposit amount is reduced by a certain amount, the transfer to the account will increase the corresponding amount of deposit. Next, take the example of a transfer scenario and use the code to demonstrate the transfer process.

　　

　　Data preparation:

--Create a tableif exists(Select *  fromsysobjectswhereName='Bank' )Drop TableBankGoCreate TableBank (CustomerNamenvarchar(4), Currentmoney Money)Go--Add Constraint: Account balance (Currentmoney) must not be less than 1 yuan, otherwise it is considered as a sales userAlter TableBankAdd constraintCk_currentmoneyCheck(Currentmoney>=1)Go--Insert test data, that is, two objects transferred: Zhang San account balance is 1000 yuan, John Doe account balance is 1 yuanInsert  intoBankValues('Zhang San', +),('John Doe',1);Go--View ResultsSelect *   fromBankGo

　　　

　　Use SQL statements to simulate the transfer function:

Logic: Direct transfer from Zhang San account of 1000 yuan to John Doe account, Zhang San account reduced by 1000 yuan. John Doe account increased 1000 yuan.

The code is as follows:

--write SQL statements based on business logic to perform data operations--turn outUpdateBankSetCurrentmoney=Currentmoney- + whereCustomerName='Zhang San'--transferredUpdateBankSetCurrentmoney=Currentmoney+ +whereCustomerName='John Doe'

Execution Result:

　　

Results Analysis:

Zhang San transferred to John Doe 1000 yuan did not deduct from the account, John Doe account is more than 1000 yuan, after the transfer of the balance of the two accounts into 1000+1001=2001, deposited money in the bank more than 1000 yuan. If this happens in life, think about how horrible it is. The reason for this is because the constraint that was previously defined when the table was created causes the first SQL command to fail, and the next SQL does not stop execution because of the interrupt.

If in the program, you may use code logic to control, that is, the first command execution succeeds in executing the next command, although you can monitor the results of the first command to make corresponding processing. However, if the first command executes successfully, the second command executes an error, and it does not guarantee the integrity of a business.

We can use the transaction mechanism to solve this problem, the transfer process is a transaction, it requires two or more SQL statements to complete a series of operations, regardless of how many commands, they always revolve around a topic is money transfer. If one of these steps goes wrong, the entire transaction is not set up and an error should be made to restore the data of the operation in the transactions to the original data. In development, it is very difficult to ensure that a single operation in a transaction is not error-prone without the use of transaction processing mechanisms. Once an operation in a transaction goes wrong, it leads to overall logic.

The concept of a transaction　　

A transaction is a mechanism, a sequence of operations, that contains multiple operations commands, and all commands as a whole and all around a business topic to the system to submit or revoke the operation request, that is, the set of commands are either executed, or do not execute, the common understanding is joint retreat. Therefore, the transaction is an inseparable working logical unit, when executing concurrent operations on the database system, the transaction is used as the smallest control unit, and it is especially suitable for multi-user operation of the database system. For example, airline ticketing systems, banks, insurance companies, and so on.

A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four properties, namely atomicity, consistency, isolation, and durability, which are referred to as acid.

　　　　

　　

SQL Server Transactions