当前位置：首页 > 文章列表 > 数据库 > MySQL > 浅谈 MySQL 的事务和 ACID

浅谈 MySQL 的事务和 ACID

来源：SegmentFault 2023-01-13 11:09:04 0浏览收藏

本篇文章给大家分享《浅谈 MySQL 的事务和 ACID》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

最近把个人博客搭建好了，链接在这里：tobe的呓语，文章会先在博客和公众号更新~ 希望大家多多收藏啊

所谓事务（Transaction），就是通过确保成批的操作要么完全执行，要么完全不执行，来维护数据库的完整性。举一个烂大街的例子：A 向 B 转账 1000 元，对应的 SQL 语句为：（没有显式定义事务）

UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';

运行后的结果如下：

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    3000 |
| B    |    5000 |
+------+---------+

这样做可能遇到问题，比如执行完第一条语句之后，数据库崩溃了，最后的结果就可能会是这样（毕竟咱不会模拟这种故障）：

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    5000 |
+------+---------+

A 的 1000 块钱平白无故消失了，这肯定不合适。事务就是为了解决类似的问题而出现的，如果使用事务来处理转账，对应的 SQL 就是：

START TRANSACTION;
UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';
COMMIT;

仅仅是在这原先的两条 SQL 语句前后加上了

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

mysql> 
START TRANSACTION;
INSERT INTO deposit_table VALUES('C', 7000);
INSERT INTO deposit_table VALUES('D', 8000);
#再次插入 D，由于主键的唯一性，该语句会执行失败
INSERT INTO deposit_table VALUES('D', 9000);
COMMIT; #提交事务

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

1062 - Duplicate entry 'D' for key 'PRIMARY'
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.04 sec)

我们可以看到，在执行

# 创建一个存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
    # 创建一个标志符，出现错误就将其置为 1
    DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    START TRANSACTION;
        INSERT INTO deposit_table VALUES('C', 7000);
                INSERT INTO deposit_table VALUES('D', 8000);
                INSERT INTO deposit_table VALUES('D', 9000);
        
        # 发生错误，回滚事务
        IF err_flg = 1 THEN
            SELECT 'SQL Err Invoked'; # 错误提示信息
            ROLLBACK;
            SELECT * FROM deposit_table;
        # 没有发生错误，直接提交
        ELSE
            SELECT 'TRANSACTION Success';
            COMMIT;
            SELECT * FROM deposit_table;
        END IF;
    
END

接下来我们调用该存储过程：

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.04 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

结果里打印出了错误信息

# 表 a，b 的定义略过
START TRANSACTION;
UPDATE a SET name = 'a_new' WHERE name = 'a_old';
UPDATE b SET name = 'b_new' WHERE name = 'b_old';
COMMIT;

这个例子里的一致性，就是说，如果此时有查询

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

新的存储过程（仅仅删除了

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
    #Routine body goes here...
    DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    # START TRANSACTION;
    INSERT INTO deposit_table VALUES('C', 7000);
    INSERT INTO deposit_table VALUES('D', 8000);
    INSERT INTO deposit_table VALUES('D', 9000);
        
        IF err_flg = 1 THEN
            SELECT 'SQL Err Invoked';
            ROLLBACK;
            SELECT * FROM deposit_table;
        ELSE
            SELECT 'TRANSACTION Success';
            COMMIT;
            SELECT * FROM deposit_table;
        END IF;
    
END

调用的结果：

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.24 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.28 sec)

Query OK, 0 rows affected (0.21 sec)

在这里，我们看到尽管确实执行了 ROLLBACK，但 C 和 D 仍然插入到了