T Sql 简明教程
T-SQL - Transactions
transaction 是针对数据库执行的一项工作单元。事务是由用户手动或由某种数据库程序自动以逻辑顺序执行的工作单元或工作序列。
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
事务是向数据库传播一项或多项更改的过程。例如，如果您正在创建记录或更新记录或从表中删除记录，那么您正在对表执行事务。控制事务以确保数据完整性并处理数据库错误非常重要。
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
实际上，您会将许多 SQL 查询合并到一个组中，并将它们全部作为一个事务的一部分一起执行。
Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
Properties of Transactions
事务具有以下四个标准属性，通常用首字母缩写词 ACID 提到 -
Transactions have the following four standard properties, usually referred to by the acronym ACID −
-
Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
-
Consistency − Ensures that the database properly changes state upon a successfully committed transaction.
-
Isolation − Enables transactions to operate independently of and transparent to each other.
-
Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.
Transaction Control
有以下用于控制事务的命令 −
There are following commands used to control transactions −
-
COMMIT − To save the changes.
-
ROLLBACK − To roll back the changes.
-
SAVEPOINT − Creates points within groups of transactions in which to ROLLBACK.
-
SET TRANSACTION − Places a name on a transaction.
事务控制命令仅用于 DML 命令 INSERT、UPDATE 和 DELETE。它们不能用于创建表或删除表，因为这些操作会自动提交到数据库中。
Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.
为了在 MS SQL Server 中使用事务控制命令，我们必须使用“begin tran”或“begin transaction”命令开始事务，否则这些命令将不起作用。
In order to use transactional control commands in MS SQL Server, we have to begin transaction with ‘begin tran’ or begin transaction command otherwise these commands will not work.
COMMIT Command
COMMIT 命令用于将由事务调用的更改保存到数据库的事务命令。此命令将自上次 COMMIT 或 ROLLBACK 命令以来所有事务保存到数据库。
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. This command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Example
可以将 CUSTOMERS 表视为包含以下记录。
Consider the CUSTOMERS table having the following records.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00以下命令示例将从表中删除年龄 = 25 的记录，然后在数据库中提交更改。
Following command example will delete records from the table having age = 25 and then COMMIT the changes in the database.
Begin Tran
DELETE FROM CUSTOMERS
WHERE AGE = 25
COMMIT最后将从表中删除两行，并且 SELECT 语句将生成以下输出。
As a result, two rows from the table would be deleted and SELECT statement will produce the following output.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 kaushik 23 Kota 2000.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00ROLLBACK Command
ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。该命令只能用于自上次发出 COMMIT 或 ROLLBACK 命令以来撤消事务。
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Example
可以将 CUSTOMERS 表视为包含以下记录。
Consider the CUSTOMERS table having the following records.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00以下命令示例将从表中删除年龄 = 25 的记录，然后在数据库中回滚更改。
Following command example will delete records from the table having age = 25 and then ROLLBACK the changes in the database.
Begin Tran
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK最后，删除操作将不影响表，并且 SELECT 语句将生成以下结果。
As a result, delete operation will not impact the table and SELECT statement will produce the following result.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00SAVEPOINT Command
SAVEPOINT 是事务中的一个点，在该点上您可以将事务回滚到某个点，而无需回滚整个事务。
SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
Syntax
以下是 SAVEPOINT 命令的语法。
Following is the syntax for SAVEPOINT command.
SAVE TRANSACTION SAVEPOINT_NAME此命令仅用于在事务语句中创建 SAVEPOINT。ROLLBACK 命令用于撤消一组事务。
This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions.
以下是回滚到 SAVEPOINT 的语法。
Following is the syntax for rolling back to a SAVEPOINT.
ROLLBACK TO SAVEPOINT_NAME在以下示例中，我们将从 CUSTOMERS 表中删除三条不同的记录。在每次删除之前，我们都必须创建一个 SAVEPOINT，以便我们可以在任何时间回滚到任何 SAVEPOINT，以将适当的数据返回到其原始状态。
In the following example, we will delete three different records from the CUSTOMERS table. We will have to create a SAVEPOINT before each delete, so that we can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.
Example
考虑包含以下记录的 CUSTOMERS 表：
Consider the CUSTOMERS table having the following records −
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00以下是操作系列：
Following are the series of operations −
Begin Tran
SAVE Transaction SP1
Savepoint created.
DELETE FROM CUSTOMERS WHERE ID = 1
1 row deleted.
SAVE Transaction SP2
Savepoint created.
DELETE FROM CUSTOMERS WHERE ID = 2
1 row deleted.
SAVE Transaction SP3
Savepoint created.
DELETE FROM CUSTOMERS WHERE ID = 3
1 row deleted.这三个删除操作已经发生，但是，我们改变了主意并决定回滚到我们标识为 SP2 的 SAVEPOINT。因为 SP2 是在第一次删除后创建的，所以最后两个删除操作被撤消了 -
The three deletions have taken place, however, we have changed our mind and decide to ROLLBACK to the SAVEPOINT that we identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −
ROLLBACK Transaction SP2
Rollback complete.请注意，由于我们回滚到了 SP2，因此只执行了第一次删除。
Notice that only the first deletion took place since we rolled back to SP2.
SELECT * FROM CUSTOMERS选择了 6 行。
6 rows selected.
ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00