MS SQL Basics Tutorial: transactions in SQL databases

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 ' at this time, 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

A transaction is a mechanism, 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 not 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.

The start of a transaction is typically identified in a program with the Begin TRANSACTION command, and the CommitTransaction command identifies the end of the transaction. 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 of the two commands is as follows:

BEGIN tran[saction] [transaction_name | @tran_name_variable]

COMMIT [tran[saction] [transaction_name | @tran_name_variable]]

The Begin TRANSACTION can be abbreviated as a BEGIN TRAN, a 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 the return of operations to a database before or at a specified location when a statement in a transaction fails to execute.

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.

The 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.

See the full set of "MS SQL Basics Tutorials"