MySQL中關于事務和鎖的常見執行命令實例整理，并標注了不同版本下的區別（如MySQL 8.0與舊版本的差異）：

---

一、事務相關命令

1. 事務控制

命令	描述	版本差異
START TRANSACTION; 或 BEGIN;	顯式開啟事務	通用語法，無版本差異。
COMMIT;	提交事務，使更改永久生效	通用語法，無版本差異。
ROLLBACK;	回滾事務，撤銷未提交的更改	通用語法，無版本差異。
SAVEPOINT 保存點名稱;	設置事務保存點	通用語法，無版本差異。
ROLLBACK TO 保存點名稱;	回滾到指定保存點	通用語法，無版本差異。

2. 自動提交設置

命令	描述	版本差異
SHOW VARIABLES LIKE 'autocommit';	查看自動提交狀態	通用語法，無版本差異。
SET @@autocommit = 0/1; 或 SET autocommit = OFF/ON;	開啟/關閉自動提交	通用語法，無版本差異。

3. 事務隔離級別

命令	描述	版本差異
SET SESSION TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};	設置事務隔離級別	通用語法，無版本差異。默認為 REPEATABLE READ。

4. 示例事務

-- 轉賬事務示例
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

---

二、鎖相關命令

1. 共享鎖與排他鎖

命令	描述	版本差異
SELECT ... LOCK IN SHARE MODE;	手動獲取共享鎖（允許其他事務讀，但禁止寫）	MySQL 8.0之前使用此語法。
SELECT ... FOR SHARE;	MySQL 8.0+ 的優化版共享鎖寫法	MySQL 8.0+ 新增，替代 LOCK IN SHARE MODE。
SELECT ... FOR UPDATE;	手動獲取排他鎖（禁止其他事務讀和寫）	通用語法，無版本差異。

2. 表級鎖

命令	描述	版本差異
LOCK TABLES 表名 READ;	獲取表級共享鎖（只讀）	通用語法，無版本差異。
LOCK TABLES 表名 WRITE;	獲取表級排他鎖（獨占寫）	通用語法，無版本差異。
UNLOCK TABLES;	釋放表鎖	通用語法，無版本差異。

3. 全局鎖

命令	描述	版本差異
FLUSH TABLES WITH READ LOCK;	獲取全局讀鎖（全庫備份場景）	通用語法，無版本差異。
UNLOCK TABLES;	釋放全局鎖	通用語法，無版本差異。

4. 特殊鎖類型

命令	描述	版本差異
SELECT ... FOR UPDATE;	行級排他鎖（InnoDB默認行為）	通用語法，無版本差異。
SELECT ... FOR UPDATE SKIP LOCKED;	跳過被鎖定的行（MySQL 8.0+）	MySQL 8.0+ 新增。
SELECT ... FOR UPDATE WAIT n;	等待鎖超時（MySQL 8.0+）	MySQL 8.0+ 新增，n 為等待秒數。

5. 死鎖檢測

命令	描述	版本差異
SELECT * FROM information_schema.innodb_lock_waits;	查看死鎖信息	通用語法，無版本差異。

6. 樂觀鎖

命令	描述	版本差異
UPDATE 表名 SET version = version + 1 WHERE 條件 AND version = 期望值;	樂觀鎖模式（基于版本號）	通用語法，無版本差異。

---

三、版本差異總結

MySQL 8.0 的改進

1. 共享鎖語法優化：

   • 舊版本：SELECT ... LOCK IN SHARE MODE;
   • MySQL 8.0+：SELECT ... FOR SHARE;（推薦使用）。

2. 排他鎖擴展功能：

   • MySQL 8.0+ 支持 SELECT ... FOR UPDATE SKIP LOCKED 和 SELECT ... FOR UPDATE WAIT n，提供更靈活的鎖控制。

3. 自增鎖優化：

   • MySQL 8.0+ 使用輕量級互斥量替代傳統表級鎖，提升并發性。

舊版本兼容性

• MySQL 5.7及以下：
  • 必須使用 LOCK IN SHARE MODE 實現共享鎖。
  • 不支持 SKIP LOCKED 和 WAIT n 等高級鎖功能。
  • 自增鎖（AUTO-INC Lock）為傳統表級鎖，可能影響并發性能。

---

四、鎖類型與事務隔離級別的關聯

1. 間隙鎖（Gap Lock）：

   • 生效條件：僅在 REPEATABLE READ 隔離級別下生效（默認）。
   • 命令觸發：范圍查詢（如 SELECT ... FOR UPDATE WHERE id BETWEEN 10 AND 20）。

2. 臨鍵鎖（Next-Key Lock）：

   • 作用：防止幻讀（默認在 REPEATABLE READ 下生效）。
   • 觸發場景：SELECT ... FOR UPDATE 或 UPDATE 操作。

3. 插入意向鎖（Insert Intention Lock）：

   • 作用：允許多個事務同時插入同一間隙的不同位置。
   • 版本要求：通用語法，無版本差異。

---

五、鎖查看與調試

命令	描述	版本差異
SHOW OPEN TABLES WHERE In_use > 0;	查看當前被鎖的表	通用語法，無版本差異。
SHOW ENGINE INNODB STATUS;	查看InnoDB鎖狀態和死鎖信息	通用語法，無版本差異。

---

六、示例場景

場景1：高并發轉賬操作

-- MySQL 8.0+
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- 共享鎖
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

場景2：避免幻讀

-- MySQL 8.0+
START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE; -- 臨鍵鎖防止插入新數據
-- 執行業務邏輯
COMMIT;

---

以上命令和版本差異總結基于MySQL 5.7及8.0的特性，實際使用時需結合具體版本文檔驗證。