MySQL探秘之旅：繞不開的數據庫事務

作者：老吳 2024-11-27 11:39:02

數據庫 MySQL

不論是日常編程，還是在面試當中，數據庫事務都是繞不開的一個話題，所以有必要深入掌握，特別是它的隔離級別，也是經常被經常問起的問題。?

怎么強調數據庫事務的重要性都不為過，如果你還不知道、還沒有使用過事務，那么你可能連初級程序員都算不上。

一、什么是數據庫事務

數據庫事務是用戶定義的一個數據庫操作序列，這些操作要么全做要么全不做，是一個不可分割的工作單位。

事務具有四個特性：原子性（Atomicity），一致性（Consistency），隔離性（Isolation）和持久性（Durability），簡稱ACID特性。

我們以經典的轉賬例子來分別說明這幾個特性的詳細含義：A、B賬戶的余額都是一萬元，然后A賬戶向B賬戶轉賬1000元，這實際上包括了兩步操作，先是A賬戶減去1000元，變成了9000元，而B賬戶加上1000元，變成了11000元。

1. 原子性：事務中包含的操作要么全都做，要么全都不做。比如上面的轉賬例子，要么A賬戶減少1000元且B賬戶增加了1000元，要么兩個賬戶的余額都沒有改變。

2. 一致性：事務執行的結果必須是使數據庫從一個一致性狀態變成另外一個一致性狀態。在轉賬前，A、B賬戶的余額是一萬元，合計是兩萬元，轉賬后兩者的賬戶合計也必須是兩萬元，如果轉賬所涉及的兩步操作有一步失敗，比如A賬戶減去1000元沒有成功，但B賬戶卻加上了1000元，那么事務結束后，兩者的余額合計就變成了兩萬一千元，這時數據庫就處于不一致的狀態了。

3. 隔離性：多個事務可以并發執行，但事務之間不能相互干擾，以確保一個事務的操作不會影響到另外一個事務。

4. 持久性：指事務一旦提交，它對數據庫中數據的影響是永久性的，接下來的其他操作或者故障不應該對執行結果有任何影響。

二、MySQL中的事務

在MySQL的架構體系中，最下面一層是存儲引擎層，它負責數據的存儲和提取，通過命令show engines可以查看當前服務器支持的存儲引擎：

由上圖可以看出，只有InnoDB存儲引擎才支持事務，而它也是MySQL 5.7默認的選項，其它存儲引擎都不支持事務。NDB Cluster也支持事務，它是基于內存的存儲引擎，需要另行安裝，本文暫不討論。

MySQL默認采用自動提交（AUTOCOMMIT）模式，即使沒有手動開啟事務（START TRANSACTION），MySQL也會默認將當前的每個SQL語句當成一個事務進行提交，可通過以下語句查詢當前連接的自動提交模式：

SHOW VARIABLES LIKE 'AUTOCOMMIT';

從以上語句的執行結果可以看到，這個參數默認是ON，也就是開啟了自動提交模式：

圖片

如果需要關閉自動提交模式，則把它設置為零即可，1或者ON表示啟用，0或者OFF表示關閉：

SET AUTOCOMMIT=0;

自動模式關閉以后，就需要手動開啟并提交或者回滾事務。

三、MySQL事務控制語句

BEGIN或START TRANSACTION：開啟一個新事務

COMMIT：提交當前事務，讓SQL語句對數據庫的修改成為永久性的

ROLLBACK：回滾當前事務，撤銷對數據庫的修改

SAVEPOINT identifier：保存點，可以使用它回滾部分指定事務，從而可以更精細的控制事務，identifier為保存點的名稱

ROLLBACK TO SAVEPOINT：事務回滾到指定的保存點

RELEASE SAVEPOINT：刪除事務的保存點

SET TRANSACTION：用于指定事務的特性，比如隔離級別、訪問模式（讀寫/只讀）等。

我們用一個簡單的例子來演示一下部分控制語句的用法，特別是關于保存點的用法，相對來講，它可能有點陌生。我們創建一個非常簡單的表，并且往里面插入幾行數據：

CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_employee` (`name`) VALUES ('Tom');
INSERT INTO `t_employee` (`name`) VALUES ('John');
INSERT INTO `t_employee` (`name`) VALUES ('Hans');

# 開啟一個事務
START TRANSACTION;
# 更新id為1的name字段
UPDATE t_employee SET name='full stack' WHERE id = 1;
# 確認更新成功
SELECT * FROM t_employee WHERE id = 1;
# 設置一個保存點，名稱為s1
SAVEPOINT s1;
# 再次更新id為1的name字段
UPDATE t_employee SET name='full stack new' WHERE id = 1;
SELECT * FROM t_employee WHERE id = 1;
# 回滾事務到保存點s1
ROLLBACK TO s1;
# 會發現最后一次更新沒有成功，因為它被回滾了
SELECT * FROM t_employee WHERE id = 1

如果執行上面的SQL有錯誤，請按照提示刪除多余的換行符。

四、事務隔離級別

上面我們提到過，事務有四大特性，其中一個是隔離性，在并發執行事務的情況，可能會存在事務之間相互干擾的情況，這時候就需要指定合適的隔離級別，來保證程序運行的正確性。事務隔離級別是指多個事務同時操作數據庫時，事務之間相互隔離的程度，SQL 92規范有四種隔離級別，MySQL InnoDB存儲引擎也支持這四種級別，它們分別是：讀未提交，讀已提交，可重復讀，串行化。

圖片

下面我們用實例演示這四種隔離級別，首先創建一個表并接入記錄：

CREATE TABLE `checking` (
  `customer_id` int(11) NOT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `balance` decimal(16,4) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  KEY `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of checking
-- ----------------------------
INSERT INTO `checking` VALUES ('1002', 'Jane', '500.0000', '2024-11-01 09:57:08', '2024-11-08 16:34:09');
INSERT INTO `checking` VALUES ('1003', 'Tom', '300.0000', '2024-11-01 09:57:08', '2024-11-08 16:34:09');
INSERT INTO `checking` VALUES ('1004', 'John', '600.0000', '2024-11-01 09:57:08', '2024-11-08 16:34:09');