MySQL中关于事务和锁的常见执行命令整理包括版本区别

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的特性，实际使用时需结合具体版本文档验证。