A transaction is the most basic unit of work. Either the entire transaction is completed, or the entire transaction is not operated. this is very important. For example, the SQL statement in the banking system that transfers RMB 10,000 from a account to B account, the first statement update Balance Set account = Account + 10000 where id = 'B '; the second statement is update Balance Set account = Account-10000 where id = 'A'. The two statements are written as a stored procedure to complete the transfer. however, if the first statement is successfully executed, but the second statement fails to be executed (for example, if the check constraint is set, account <0 is not allowed, and the original account <10,000 of account A will cause execution failure ), so we have an additional 10 thousand yuan for account B, and the account A has not been reduced, this statement will cause losses to the bank.
This is just a simple example. If such a problem exists in the banking system, neither users nor banks are willing to see it. to avoid the successful execution of some statements and the failure of some statements, transactions are required. the transaction tracks every change to the database. If all the SQL statements are successfully executed, the transaction is committed to complete all the changes. If any operation fails, the entire execution process will be rolled back so that the entire transaction will not make any changes to the database. the following describes the transaction knowledge:
1. Common statements used to manage transactions include: Begin transaction, commit transaction, rollback transaction, save transaction, @ error, @ transcount.
The complete basic statement of a transaction is: declare @ er1 int, @ er2 int
Begin transaction
--...... Database Operations
Set @ er1 = @ error -- Record the error of the previous operation. If the operation is successful, the value is 0.
--...... Another database operation
Set @ er2 = @ error -- Record the error of the second operation
If (@ er1 = 0 and @ er2 = 0) -- if both operations are successfully completed, the transaction is committed.
Commit transaction
Else -- roll back the transaction if an operation fails.
Rollback transaction
This is a simple transaction example. Other Transaction statements share the same principle.
2. transactions support nesting. the nesting principle is the same as general program nesting principles. Commit transaction and rollback transaction are for the latest unmatched begin transaction. it is best not to give the transaction name when nesting. in addition, if transaction A contains a nested transaction B, transaction B commit, and transaction a rollback, will the modifications executed in transaction B take effect? No. Because transaction a rollback is nested in transaction A, all operations in transaction a are rolled back. in short, SQL Server writes all modifications to the database only when the outermost transaction is committed.
3. save transaction
This statement provides a transaction tag point that matches the content after the commit or rollback operation tags. Save transation and begin transaction with commit transaction and rollback transaction.
For example:
Begin transaction
Update... 1
Save transaction saved
Update... 2
Rollback transaction saved
Commit transaction
The execution result of this statement is Update 1, but Update 2 is not.
The above is just my personal understanding and opinions. If you have any mistakes or mistakes, I hope you can correct them.
