Mysql 简明教程

MySQL - Transactions

The MySQL Transactions

MySQL 事务是一组连续的数据库操作,执行时如同一个单独的工作单元。换言之,一个事务永远不会完全完成,除非组中的每个单独操作成功完成。如果事务中的任何操作失败,则整个事务都将失败。

The MySQL transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

实际上,您会将多个 SQL 查询组合到一个组中,并将它们全部作为事务的一部分一起执行。这将确保没有数据丢失或 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. This will ensure no data losses or failed executions of SQL queries.

Properties of Transactions

事务有四个标准属性,通常用缩写 ACID 表示 −

There are four standard properties of transactions, often referred to by the acronym ACID

  1. Atomicity − This ensures that all operations within a transaction are treated as a single unit. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in its original state.

  2. Consistency − This ensures that the database properly changes states upon a successfully committed transaction.

  3. Isolation − This enables transactions to operate independently and transparent to each other.

  4. Durability − This ensures that once a transaction is committed, its effects on the database are permanent and will survive system failures (e.g., power outages, hardware failures).

Transactional Statements in MySQL

在 MySQL 中,事务以 START TRANSACTIONBEGINBEGIN WORK 语句开始,并以 COMMITROLLBACK 语句结束。在开始语句和结束语句之间执行的 MySQL 命令构成了事务的主体。

In MySQL, the transactions begin with either START TRANSACTION, BEGIN or BEGIN WORK statements, and end with either a COMMIT or a ROLLBACK statement. The MySQL commands executed between the beginning and ending statements forms the main body of the transaction.

要启用或禁用事务中的自动提交选项,可以使用 SET AUTOCOMMIT 命令。要启用自动提交,请将命令设置为“1”或“ON”,要禁用它,请将命令设置为“0”或“OFF”。

To enable or disable the auto-commit option in a transaction, you can use the SET AUTOCOMMIT command. To enable auto-commit, set the command to '1' or 'ON,' and to disable it, set the command to '0' or 'OFF.'

The COMMIT Command

COMMIT 命令是 MySQL 中的事务控制命令。发出该命令后,它将完成到目前为止事务对数据库表所做的更改,使这些更改变为永久。因此,这些更改将对 MySQL 中的其他活动会话可见。

The COMMIT command is a transaction control command in MySQL. When issued, it finalizes the changes made to a database table up to that point in a transaction, making those changes permanent. As a result, these changes become visible to other active sessions in MySQL.

Syntax

以下是 MySQL 中执行 COMMIT 命令的语法:

Following is the syntax to execute the COMMIT command in MySQL −

COMMIT;

Example

我们使用以下查询创建一个名为 CUSTOMERS 的表:

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);

显示的 CUSTOMERS 表如下:

The CUSTOMERS table displayed is as follows −

使用以下查询启动一个事务并删除 CUSTOMERS 表中 AGE 为 25 的记录,然后 COMMIT 数据库中的更改:

Using the following query, start a transaction and delete the records from the CUSTOMERS table whose AGE is 25, then COMMIT the changes in the database −

START TRANSACTION;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

表中将删除两行。要验证,使用以下 SELECT 语句显示修改后的 CUSTOMERS 表:

Two rows from the table would be deleted. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

以下是所获得的输出 −

Following is the output obtained −

The AUTOCOMMIT Command

你可以通过设置会话变量 AUTOCOMMIT 来控制事务的行为。如果 AUTOCOMMIT 设置为 1(默认),则每个 SQL 语句(无论是否在事务中)都被视为一个完整的事务,并在完成后默认提交。

You can control the behaviour of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes.

当 AUTOCOMMIT 设置为 0 时,通过发出 SET AUTOCOMMIT = 0 命令,一系列后续语句将充当一个事务,并且没有任何活动被提交,直到发出显式的 COMMIT 语句。

When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

The ROLLBACK Command

ROLLBACK 命令是一个事务命令,用于撤销尚未保存(提交)到数据库的事务中所做的更改。此命令只能撤销自上次执行 COMMIT 或 ROLLBACK 语句以来所做事务的影响。

The ROLLBACK command is a transactional command used to undo changes made in a transaction that have not been saved (committed) to the database. This command can only reverse the effects of transactions made since the last COMMIT or ROLLBACK statement was executed.

Syntax

以下是 MySQL 中 ROLLBACK 命令的语法:

Following is the syntax for ROLLBACK command in MySQL −

ROLLBACK;

Example

使用以下查询删除 CUSTOMERS 表中 AGE 为 25 的记录,然后 ROLLBACK 数据库中的更改:

Using the following query, delete the records from the CUSTOMERS table whose AGE is 25, then ROLLBACK the changes in the database −

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

此表不会受到影响。要验证,使用以下 SELECT 语句显示修改后的 CUSTOMERS 表:

The table will not be affected. To verify, display the modified CUSTOMERS table using the following SELECT statement −

SELECT * FROM CUSTOMERS;

以下是要获得的表:

Following is the table obtained −

你必须记住,ROLLBACK 仅在事务中起作用。如果你尝试在不启动事务的情况下执行它,则不会撤销更改。

You must remember that ROLLBACK only works inside a transaction. If you try to execute it without starting a transaction, the changes would not be revoked.

The SAVEPOINT Command

保存点是在 MySQL 交易中一个逻辑回滚点。

A SAVEPOINT is a logical rollback point within a transaction in MySQL.

执行 ROLLBACK 命令时,会将交易中所进行的更改还原成上一个 COMMIT 操作,或(如果未执行过任何 COMMIT)还原成交易的开头。但是,通过在交易中创建保存点,你可以建立特定点来部分回滚该交易。在两个提交点之间,你可以在交易中创建多个保存点,以获得多个回滚选项。

When you execute the ROLLBACK command, it reverts the changes made in the transaction back to the last COMMIT or the beginning of the transaction if there haven’t been any COMMITs. However, by creating save points within the transaction, you can establish specific points to which you can partially roll back the transaction. You can create multiple save points within a transaction to have multiple rollback options between two commits.

Syntax

在交易中创建 SAVEPOINT 命令的语法如下所示:

The syntax for creating a SAVEPOINT command among transactions is as shown below −

SAVEPOINT SAVEPOINT_NAME;

回滚到所创建的 SAVEPOINT 的语法如下:

The syntax for rolling back to the SAVEPOINT created is as follows −

ROLLBACK TO SAVEPOINT_NAME;

Example

在以下示例中,你计划从 CUSTOMERS 表中删除三条不同的记录,同时在每次删除前先创建 SAVEPOINT。这将允许你随时 ROLLBACK 到任何 SAVEPOINT,以将相应数据还原到其原始状态:

In the following example, you plan to delete three different records from the CUSTOMERS table while creating SAVEPOINTs before each delete. This allows you to ROLLBACK to any SAVEPOINT at any time to restore the appropriate data to its original state −

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)

由于三个删除操作都已进行,让我们假设你已经改变想法,并决定 ROLLBACK 到标识为 SP2 的 SAVEPOINT。由于 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 identified as SP2. Since SP2 was created after the first deletion, this action undoes the last two deletions −

ROLLBACK TO SP2;

Verification

如果你使用以下 SELECT 语句显示 CUSTOMERS 表,你将注意到自回滚到 SP2 后,仅进行了第一次删除:

If you display the CUSTOMERS table using the following SELECT statement, you will notice that only the first deletion took place since you rolled back to SP2 −

SELECT * FROM CUSTOMERS;

获得的结果如下所示 −

The result obtained is as shown below −

Transaction-Safe Table Types in MySQL

在 MySQL 中,并不是所有表类型都本机支持交易。为了有效地使用交易,你应当以特定方式创建表。虽然可以使用各种表类型,最常用的事务安全表类型是 InnoDB。

In MySQL, not all table types natively support transactions. To use transactions effectively, you should create your tables in a specific way. Although various table types are available, the most commonly used transaction-safe table type is InnoDB.

要启用 InnoDB 表支持,你可能需要在编译 MySQL 源代码期间设置特定的编译参数。如果你的 MySQL 版本不包含 InnoDB 支持,你可以要求你的互联网服务提供商 (ISP) 提供支持 InnoDB 的 MySQL版本,或下载并安装 MySQL-Max Binary Distribution 以便在开发环境中使用 InnoDB 表。

To enable InnoDB table support, you may need a specific compilation parameter during the MySQL source compilation. If your MySQL version doesn’t include InnoDB support, you can request your Internet Service Provider (ISP) to provide a MySQL version with InnoDB support, or you can download and install MySQL-Max Binary Distribution for Windows or Linux/UNIX to work with InnoDB tables in a development environment.

如果你的 MySQL 安装支持 InnoDB 表,你可以按以下方式创建一个 InnoDB 表:

If your MySQL installation supports InnoDB tables, you can create an InnoDB table as shown below −

CREATE TABLE tcount_tbl (
   tutorial_author varchar(40) NOT NULL,
   tutorial_count  INT
) ENGINE = InnoDB;

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.05 sec)

你还可以使用 GEMINIBDB 等其他表类型,但这取决于你的安装,以及它是否支持这两种表类型。

You can also use other table types like GEMINI or BDB, but it depends on your installation, whether it supports these two table types or not.

Transactions Using a Client Program

我们还可以使用客户端程序执行交易。

We can also perform transactions using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −