My SQL Server → 5 Transaction Processing

My SQL Server → 5 transaction processing. the concept of transactions the transaction mechanism treats a group of database operation commands as a complete and independent operation sequence, that is, a group of commands either succeed or fail to be executed. 1. Role of a transaction: either all operations are completed, or all operations are not executed. 2. what is a transaction in the transaction database is a mechanism, each transaction is an independent unit of work, it contains a group of database operation commands, and this group of commands are either executed or not executed, therefore, a transaction is an inseparable unit of work logic. 3. Transaction attributes: ① atomicity: each element of the transaction is inseparable. ② Consistency: when the transaction is completed, all data must be consistent. ③ Isolation: all concurrent transactions that modify data are isolated from each other. ④ Persistence: as long as the transaction is successfully committed, it cannot be rolled back to the status before submission. · Use of transactions 1. create TRANSACTION: ① start TRANSACTION: begin transaction ② COMMIT transaction: commit transaction ③ rollback transaction: rollback transaction category: Display transaction: Use begin TRANSACTION to specify the start of the TRANSACTION, subsequent T-SQL statements are a whole. Implicit Transaction: use the Set implicit_transaction on statement to enable the implicit transaction. Automatic transaction commit: it is the default mode of SQL Server d. It treats each separate T-SQL statement as a transaction. If it is successfully executed, it is automatically committed. If it fails, automatic rollback. Commonly used is to display transactions, which clearly specify the boundary at which the transaction starts. The fewer statements a transaction contains, the better. Do not place irrelevant statements in a transaction, such as variable declaration and value assignment. 2. Principles for creating a transaction: ① the transaction should be as short as possible. ② Minimize the amount of data accessed in transactions. ③ Do not open transactions when Browsing data. ④ Do not request user input during transaction processing.. Use transactions in the stored procedure. 1. Considerations for using transactions: ① After each operation, check the value of @ error or @ rowcount. ② After a transaction ends, the T-SQL code that follows the transaction can continue to be executed, but the transaction cannot be rolled back after an error occurs. ③ The committed transactions will formally write the data into the database. ④ When a transaction is being executed, in the event of an accident such as a power outage, the transaction will be automatically rolled back when the system restarts again. ⑤ If an error occurs in the transaction instance, the transaction cannot be executed and the transaction will be rolled back automatically. ⑥ Statements that cannot be rolled back cannot be used in transactions. Example: create database alter database modify database drop database delete database. transaction isolation level ① Read uncommitted: data is not isolated, and other transactions can modify or delete the data at the same time even if the transaction is in use. ② Read committed: Read of uncommitted data is not allowed ③ Repeatable Read: locking the Read data in a subtransaction does not allow users to modify or delete the data. ④ Snapshot: Snapshot isolation, which can provide a committed version of the required data for transactions that read data. Therefore, transactions that write data are blocked from transactions that do not read data. ⑤ Serializable: locks all data tables used by the transaction, and does not allow other transactions to add, modify, or delete data.