Transactions in the SQL database

The origin of 11.4.1 Affairs

When you update a database with the delete command or the update command, you can only manipulate one table at a time, which can cause data inconsistencies in the database. For example: The enterprise cancels the Logistics Division, needs to remove the ' logistics ' from the Department table, modifies the department table, and the employee table's department number and the logistics staff should also be deleted. Therefore, all two tables need to be repaired, and this modification can only be done through two DELETE statements. Suppose the logistics number is ' 1012 '

The first DELETE statement modifies the Department table
Delete from department
where dept_id = ' 1012 '
The second DELETE statement modifies the employee table
Delete From employee
where dept_id = ' 1012 '

     after the first DELETE statement is executed, the data in the database is in an inconsistent state because there is no ' Logistics Department, but the employee table still holds the staff records belonging to the logistics Department. Only after the second DELETE statement has been executed will the data be again in a consistent state. However, if the computer suddenly fails after the first statement is executed, and the second DELETE statement cannot continue, the data in the database will be in a state of perpetual inconsistency. Therefore, you must ensure that these two DELETE statements are executed concurrently. To solve similar problems, database systems often introduce the concept of transaction (Transaction). The concept of

11.4.2 Transactions
     Transaction is a mechanism that is a sequence of operations that contains a set of database operation commands, all of which, as a whole, submit or undo an action request to the system, that is, either executed, Or none of them are executed. Therefore, a transaction is an indivisible working logical unit, similar to the primitive language in the operating system. When a concurrency operation is performed on a database system, the transaction is used as the smallest control unit.

     typically uses the begin TRANSACTION command in a program to identify the beginning of a transaction, identifying the end of the transaction with the CommitTransaction command. All statements between the two commands are treated as one, and the update operation of the database in the transaction is not confirmed until the commit TRANSACTION command is executed. and begin ... The end command is similar, the two commands can also be nested, that is, the transaction can be nested to execute. The syntax for the two commands is as follows:

BEGIN tran[saction] [transaction_name | @tran_name_variable]
COMMIT [Tran[saction] [ transaction_name | @tran_name_variable]]
where the begin TRANSACTION can be abbreviated to begin Tran, COmmit transaction can be abbreviated as a commit TRAN or a commit.

• Transaction_name
  Specifies the name of the transaction. Only the first 32 characters will be recognized by the system.
• @tran_name_variable
  A variable is used to specify the name of the transaction, and the variable can only be declared as a char, VARCHAR, NCHAR, or NVARCHAR type.

      





11.4.3 Transaction Rollback


transaction rollback (Transaction Rollback) is when a statement in a transaction fails to recover the operation of the database to a specified location before the execution of the transaction.

The
transaction rollback uses the rollback TRANSACTION command with the following syntax:


ROLLBACK [tran[saction] [transaction_name | @tran_name_variable


| Savepoint_name | @savepoint_variable]]


where the savepoint_name and @savepoint_variable parameters are used to specify a rollback to a specified location.


If you want the transaction to roll back to the specified location, you need to set the SavePoint (save point) in the transaction. The save point is the transaction statement that precedes the location where it is specified, and statements that are not rolled back are considered valid.





its syntax is as follows:


SAVE tran[saction] {savepoint_name | @savepoint_variable}

The
parameters are described as follows:


Avepoint_name


Specifies the name of the save point. As with the name of the transaction, only the first 32 characters are recognized by the system.

@savepoint_variable
Use a variable to specify the name of the savepoint. A variable can only be declared as a char, VARCHAR, NCHAR, or nvarchar type.



Note: If you do not specify a ROLLBACK transaction name or savepoint, the ROLLBACK TRANSACTION command rolls the transaction back to a transaction before it is executed, and rolls back to the nearest BEGIN TRANSACTION command if the transaction is nested.