MySQL 分区 MySQL ROW_NUMBER()函数

MySQL 事务

一、MySQL 事务是什么

MySQL 中的事务是一组顺序的语句、查询或操作，例如选择、插入、更新或删除，作为可以提交或回滚的单个工作单元执行。如果事务对数据库进行多次修改，则会发生两件事：

提交事务时，要么所有修改都成功。
或者，在事务回滚时撤消所有修改。

换句话说，如果不完成集合中可用的每个操作，事务就无法成功。这意味着如果任何语句失败，则事务操作无法产生结果。

MySQL 中的事务从第一个可执行的 SQL 语句开始，并在找到提交或显式或隐式回滚时结束。它显式使用 COMMIT 或 ROLLBACK 语句，并在使用 DDL 语句时隐式使用。

让我们通过下面的解释来理解交易的概念。

我们可以通过考虑银行数据库来理解MySQL中事务的概念。假设银行客户想要将钱从一个账户转移到另一个账户。我们可以通过使用将分为以下步骤的 SQL 语句来实现这一点：

首先，需要检查第一个帐户中请求金额的可用性。
接下来，如果金额可用，则从第一个帐户中扣除。然后，更新第一个帐户。
最后，将金额存入第二个账户。然后更新第二个帐户以完成交易。
如果上述任何一个过程失败，事务将回滚到之前的状态。

二、事务的特性

该事务主要包含四个特征，称为ACID属性。现在，我们将详细讨论 ACID 属性。ACID 属性代表：

原子性
一致性
隔离性
持久性

原子性：该属性确保事务单元内的所有语句或操作都必须成功执行。否则，如果任何操作失败，整个事务将被中止，并回滚到之前的状态。它包括以下功能：

提交声明。
回滚语句。
自动提交设置。
INFORMATION_SCHEMA 表中的操作数据。

一致性：此属性确保数据库仅在事务成功提交时才更改状态。它还负责保护数据免受崩溃。它包括以下功能：

InnoDB 双写缓冲区。
InnoDB 崩溃恢复。

隔离性：该属性保证事务单元中的每个操作独立操作。它还确保语句对彼此透明。它包括以下功能：

SET ISOLATION LEVEL 语句。
自动提交设置。
InnoDB 锁定的底层细节。

持久性：此属性保证即使系统崩溃或失败，已提交事务的结果也会永久保留。它包括以下功能：

在存储设备中写入缓冲区。
存储设备中的电池支持缓存。
配置选项 innodb_file_per_table。
配置选项 innodb_flush_log_at_trx_commit。
配置选项sync_binlog。

三、MySQL 事务语句

MySQL 借助以下语句控制事务：

MySQL 提供了一个 START TRANSACTION 语句来开始事务。它还提供了“BEGIN”和“BEGIN WORK”作为 START TRANSACTION 的别名。
我们将使用 COMMIT 语句来提交当前事务。它允许数据库永久地进行更改。
我们将使用 ROLLBACK 语句来回滚当前事务。它允许数据库取消所有更改并进入之前的状态。
我们将使用 SET 自动提交语句来禁用/启用当前事务的自动提交模式。默认情况下，COMMIT 语句自动执行。因此，如果我们不想自动提交更改，请使用以下语句：

SET autocommit = 0;  
OR,  
SET autocommit = OFF:

同样，使用以下语句启用自动提交模式：

SET autocommit = 1;  
OR,  
SET autocommit = ON:

四、MySQL 事务示例

假设我们有两个名为“employees”和“Orders”的表，其中包含以下数据：

employees表：

Orders表：

五、MySQL提交事务示例

如果我们要使用事务，则需要将SQL语句分成逻辑部分。之后，我们可以定义数据是应该提交还是回滚。

以下步骤说明了创建事务：

使用 START TRANSACTION 语句开始事务。
然后，在员工中选择最大收入。
在员工表中添加一条新记录。
将新记录添加到订单表中。
使用 COMMIT 语句完成事务。

以下是执行上述操作的命令：

-- 1. Start a new transaction  
  
START TRANSACTION;  
  
-- 2. Get the highest income  
  
SELECT @income:= MAX(income) FROM employees;  
  
-- 3. Insert a new record into the employee table  
  
INSERT INTO employees(emp_id, emp_name, emp_age, city, income)   
VALUES (111, 'Alexander', 45, 'California', 70000);  
  
-- 4. Insert a new record into the order table  
  
INSERT INTO Orders(order_id, prod_name, order_num, order_date)   
VALUES (6, 'Printer', 5654, '2020-01-10');  
  
-- 5. Commit changes      
COMMIT;

执行过程如下图：

六、MySQL回滚事务示例

我们可以借助下图来理解回滚事务。首先，打开 MySQL 命令提示符并使用密码登录数据库服务器。接下来，我们必须选择一个数据库。

假设我们的数据库包含“Orders”表。现在，以下是执行回滚操作的脚本：

-- 1. Start a new transaction  
START TRANSACTION;  
  
-- 2. Delete data from the order table  
DELETE FROM Orders;

执行上述语句后，我们将得到如下输出，表明 Orders 表中的所有记录均已成功删除。

现在，我们需要打开一个单独的 MySQL 数据库服务器会话并执行以下语句来验证 Orders 表中的数据：

SELECT * FROM Orders;

它将给出如下输出。

尽管我们在第一个会话中进行了更改，但我们仍然可以看到表中的记录可用。这是因为在我们没有在第一个会话中执行COMMIT 或 ROLLBACK语句之前，更改不是永久性的。

因此，如果我们想使更改永久化，请使用 COMMIT 语句。否则，执行 ROLLBACK 语句以回滚第一个会话中的更改。

-- 3. Rollback changes      
ROLLBACK;  
  
-- 4. Verify the records in the first session  
SELECT * FROM Orders;

成功执行后，会产生如下结果，我们可以看到更改已经回滚。

使用 MySQL Transaction 时不能回滚的语句

MySQL Transaction 无法回滚所有语句。例如，这些语句包括 DDL（数据定义语言）命令，例如 CREATE、ALTER 或 DROP 数据库以及 CREATE、UPDATE 或 DROP 表或存储例程。我们必须确保在我们设计事务时，这些语句应该不包括在内。

七、SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

SAVEPOINT语句：使用事务内的标识符名称创建一个特殊标记。它允许回滚保存点之后执行的所有语句。以便事务恢复到它在保存点时所处的先前状态。如果我们在当前事务中设置了多个同名的保存点，则新的保存点负责回滚。

ROLLBACK TO SAVEPOINT语句：允许我们将所有事务回滚到已建立的给定保存点，而无需中止事务。

RELEASE SAVEPOINT语句：从当前事务中销毁指定的保存点，而不会撤消建立保存点后执行的查询的效果。在这些语句之后，不会发生回滚命令。如果事务中不存在保存点，则会出错。

MySQL Transaction 中上述语句的语法如下：

SAVEPOINT savepoint_name  
ROLLBACK TO [SAVEPOINT] savepoint_name  
RELEASE SAVEPOINT savepoint_name

示例

让我们通过示例了解如何使用这些语句。在下面的示例中，我们将使用 SAVEPOINT 和 ROLLBACK TO SAVEPOINT 语句来解释保存点如何确定当前事务的哪些记录可以回滚。

START TRANSACTION;  
  
SELECT * FROM Orders;  
  
INSERT INTO Orders(order_id, prod_name, order_num, order_date)   
VALUES (6, 'Printer', 5654, '2020-01-10');  
  
SAVEPOINT my_savepoint;  
  
INSERT INTO Orders(order_id, prod_name, order_num, order_date)   
VALUES (7, 'Ink', 5894, '2020-03-10');  
  
ROLLBACK TO SAVEPOINT my_savepoint;  
  
INSERT INTO Orders(order_id, prod_name, order_num, order_date)   
VALUES (8, 'Speaker', 6065, '2020-02-18');  
  
COMMIT;

在上面SQL中，

我们必须首先开始事务，然后在 Orders 表中显示可用的记录。
接下来，我们将一条记录插入到表中，然后创建一个保存点标记。
同样，我们在表中插入了一条记录，然后使用 ROLLBACK TO SAVEPOINT 语句删除保存点建立的更改。
同样，我们在表中插入了一条记录。
最后，执行 COMMIT 语句以进行永久更改。

下面的输出按顺序解释了上述步骤，有助于非常容易地理解它。

现在，我们将使用 SELECT 语句来验证上述操作。在输出中，我们可以看到order_id=6和order_id=8添加成功，但是order_id=7没有插入到表中。它回滚建立保存点后输入的值：

现在我们将采用另一个示例 RELEASE SAVEPOINT，它建立 my_savepoint，然后删除一个保存点。

START TRANSACTION;  
  
INSERT INTO Orders(order_id, prod_name, order_num, order_date)   
VALUES (7, 'Ink', 5894, '2020-03-10');  
  
SAVEPOINT my_savepoint;  
  
UPDATE Orders SET prod_name='Scanner' WHERE order_id=8;  
  
RELEASE SAVEPOINT my_savepoint;  
  
COMMIT;

在输出中，我们可以看到事务中的所有语句都成功执行。在这里，INSERT和UPDATE语句都在 COMMIT 处修改表。