Sql 简明教程

SQL - Transactions

SQL Transactions

事务是对数据库执行的一组或一系列工作。事务按逻辑顺序完成，无论是由用户手动完成还是由某种数据库程序自动完成。

A transaction is a unit or sequence of work that is performed on a database. Transactions are 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, updating or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the 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 all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states 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.

Transactional Control Commands

事务控制命令仅与 DML Commands 一起使用，例如 INSERT、UPDATE 和 DELETE。在创建表或删除表时无法使用它们，因为这些操作会在数据库中自动提交。可以使用以下命令控制事务。

Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.

COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction.

The COMMIT Command

COMMIT 命令是一个事务命令，用于保存因事务而引起的变化。它保存数据库中上次 COMMIT 或 ROLLBACK 之后发生的所有事务。

The COMMIT command is the transactional command used to save changes invoked by a transaction. It saves all the transactions occurred on the database since the last COMMIT or ROLLBACK.

COMMIT 命令的语法如下。

The syntax for the COMMIT command is as follows.

COMMIT;

Example

首先，让我们使用以下查询创建名为 CUSTOMERS 的表 −

Firstly, let us create a table names CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

我们正在向上面创建的表中插入一些记录 −

We are inserting some records into the above-created table −

INSERT INTO CUSTOMERS VALUES
(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, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

该表将按如下方式创建：

The table will be created as follows −

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下查询将从表中删除 AGE 为 25 的记录，然后在数据库中提交更改。

Following query would delete those records from the table which have AGE as 25 and then COMMIT the changes in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

表中的两行将被删除，如果您使用 SELECT 语句验证 CUSTOMERS 表的内容，如下所示：

The two rows from the table would be deleted and if you verify the contents of the CUSTOMERS table using the SELECT statement as −

SELECT * FROM CUSTOMERS;

表将显示如下：

The table will be displayed as follows −

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The ROLLBACK 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 undo transactions since the last COMMIT or ROLLBACK.

ROLLBACK 命令的语法如下：

The syntax for a ROLLBACK command is as follows −

ROLLBACK;

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下查询将从表中删除 AGE 值为 25 的记录，然后在数据库中回滚更改。

Following query would delete those records from the table where the AGE value is 25 and then ROLLBACK the changes in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

删除操作不会影响表，而 SELECT 语句将产生以下结果。

The delete operation would not impact the table and the SELECT statement would 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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The SAVEPOINT Command

保存点是事务中的逻辑回滚点。

A SAVEPOINT is a logical rollback point in a transaction.

通常，当您执行 ROLLBACK 命令时，它将撤消直到上次 COMMIT 的更改。但是，如果您创建保存点，则可以将事务部分回滚到这些点。您可以在两次提交之间创建多个保存点。

Usually, when you execute the ROLLBACK command, it undoes the changes until the last COMMIT. But, if you create save points you can partially roll the transaction back to these points. You can create multiple save points between two commits.

在事务中创建 SAVEPOINT 的语法如下所示。

The syntax to create a SAVEPOINT among the transactions is as shown below.

SAVEPOINT savepoint_name;

然后，要回滚到创建的 SAVEPOINT，您可以使用以下语法：

Then, to roll back to the SAVEPOINT created, you can use the following syntax −

ROLLBACK TO savepoint_name;

Example

以下是一个计划从 CUSTOMERS 表删除三个不同记录的示例。你希望在每次删除前创建一个保存点，以便你能随时回滚到任何保存点，将其相应的数据返回到其初始状态。

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

可以将 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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下代码块中包含了一系列的操作。

The following code block contains the series of operations.

SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)

SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)

SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)

在完成这三个删除操作后，假设你改变了想法，并决定回滚到标识为 SP2 的保存点。由于 SP2 是在第一次删除后创建的，因此会撤销最后两次删除操作 -

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −

ROLLBACK TO SP2;

Verification

如果你显示 CUSTOMERS 表，你可以注意到，由于您已回滚到 SP2，因此仅执行了第一次删除。

If you display the CUSTOMERS table, you can notice that only the first deletion took place since you rolled back to SP2.

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	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The RELEASE SAVEPOINT Command

RELEASE SAVEPOINT 命令用于删除现有保存点。

The RELEASE SAVEPOINT command is used to remove an existing SAVEPOINT.

RELEASE SAVEPOINT 命令的语法如下。

The syntax for a RELEASE SAVEPOINT command is as follows.

RELEASE SAVEPOINT SAVEPOINT_NAME;

一旦释放了保存点，就无法再使用 ROLLBACK 命令来撤消自上次保存点执行的事务。

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

可以使用 SET TRANSACTION 命令来启动数据库事务。此命令用于为后续事务指定特征。例如，你可以将事务指定为只读或可读写。

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

Syntax

SET TRANSACTION 命令的语法如下。

The syntax for a SET TRANSACTION command is as follows.

SET TRANSACTION [ READ WRITE | READ ONLY ];