MySQL - 事务与锁

MySQL 事务与锁

事务控制
ACID 特性
隔离级别
锁机制
表锁与行锁
死锁
Savepoint

事务控制

MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务。

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

默认情况下，MySQL 是 autocommit 的，即每条 SQL 语句会自动提交。如果需要通过明确的 COMMIT 和 ROLLBACK 来提交和回滚事务，需要通过明确的事务控制命令来开始事务。

-- 方式1：用 START TRANSACTION 开始单个事务
START TRANSACTION;
INSERT INTO orders (cust_id, order_date) VALUES (10005, '2005-09-01');
COMMIT;

-- 方式2：关闭自动提交，所有操作都需要显式提交
SET AUTOCOMMIT = 0;
INSERT INTO orders (...) VALUES (...);
COMMIT;  -- 或 ROLLBACK

COMMIT 提交事务
ROLLBACK 回滚事务
CHAIN 子句在提交或回滚后立即启动一个新事务，具有相同的隔离级别
RELEASE 子句在提交或回滚后断开与客户端的连接
SET AUTOCOMMIT = 0 设置后所有事务都需要通过明确的命令进行提交或回滚

事务的隐式提交：所有 DDL 语句（CREATE、ALTER、DROP 等）是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。

ACID 特性

事务必须具备 ACID 四个特性：

原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
一致性（Consistency）：事务前后数据的完整性必须保持一致
隔离性（Isolation）：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务干扰
持久性（Durability）：事务一旦提交，对数据库的改变就是永久性的

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。MyISAM 不支持事务。

隔离级别

SQL 标准定义了 4 种隔离级别，InnoDB 支持全部 4 种：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	可能	可能	可能
READ COMMITTED（读已提交）	不可能	可能	可能
REPEATABLE READ（可重复读）	不可能	不可能	可能
SERIALIZABLE（串行化）	不可能	不可能	不可能

REPEATABLE READ 是 InnoDB 的默认隔离级别。InnoDB 通过 next-key 锁定算法，在这个级别下也能防止幻读
READ COMMITTED 类似 Oracle 的默认隔离级别，仅锁定索引记录而不锁定记录前的间隙，允许紧挨着已锁定的记录插入新记录

-- 查看当前隔离级别
SELECT @@tx_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

锁机制

如何加锁：

-- 锁定表
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
     [, tbl_name ...];

-- 解锁
UNLOCK TABLES;

InnoDB 的行级锁：InnoDB 存储引擎提供行级锁，支持共享锁和排他锁两种锁定模式。

共享锁（S 锁）：允许事务读一行数据。SELECT ... LOCK IN SHARE MODE
排他锁（X 锁）：允许事务更新或删除一行数据。SELECT ... FOR UPDATE

-- 加共享锁
SELECT * FROM orders WHERE order_num = 20005 LOCK IN SHARE MODE;

-- 加排他锁
SELECT * FROM orders WHERE order_num = 20005 FOR UPDATE;

表锁与行锁

什么情况下使用表锁（表锁在以下情况比行锁更优越）：

很多操作都是读表
在严格条件的索引上读取和更新，当更新或删除可以用单独的索引来读取得到时
SELECT 和 INSERT 语句并发的执行，但只有很少的 UPDATE 和 DELETE 语句
很多的扫描表和对全表的 GROUP BY 操作，但没有任何写表

行级锁定的优点：

当在许多线程中访问不同的行时只存在少量锁定冲突
回滚时只有少量的更改
可以长时间锁定单一的行

行级锁定的缺点：

比页级或表级锁定占用更多的内存
当在表的大部分中使用时，比页级或表级锁定速度慢
经常进行 GROUP BY 或扫描整个表时明显慢很多

重要注意：MySQL 的行锁是针对索引加的锁，不是针对记录加的锁。如果更新时没有使用索引访问数据，即使只更新一行也会导致全表锁定。所以要确保 SQL 使用索引来访问记录，必要时用 EXPLAIN 检查执行计划。

死锁

InnoDB 自动检测事务的死锁，并回滚一个或几个事务来防止死锁。InnoDB 不能在 MySQL LOCK TABLES 设定表锁定的地方或涉及 InnoDB 之外的存储引擎设置锁定的地方检测死锁，必须通过设定 innodb_lock_wait_timeout 系统变量的值来解决。

-- 查看死锁信息
SHOW INNODB STATUS;

-- 设置锁等待超时（默认 50 秒）
SET innodb_lock_wait_timeout = 50;

如何减少锁冲突：

MyISAM 表可以考虑改成 InnoDB 表来减少锁冲突
根据应用情况，尝试横向拆分成多个表
对 InnoDB 表，尽量使用索引检索记录，避免全表锁定
注意 MySQL 的行锁是针对索引加的锁，相同索引键的不同行也会被加锁
用 SHOW INNODB STATUS 确定死锁原因
确定更合理的事务大小，小事务更少倾向于冲突
以固定的顺序访问表和行，避免死锁
如果使用锁定读，试着用更低的隔离级别（如 READ COMMITTED）

Savepoint

在事务中可以通过定义 savepoint 指定回滚事务的一个部分，但不能指定提交事务的一个部分。可以定义多个不同的 savepoint，满足不同条件时回滚不同的 savepoint。

START TRANSACTION;

INSERT INTO orders (...) VALUES (...);
SAVEPOINT sp1;

INSERT INTO orderitems (...) VALUES (...);
SAVEPOINT sp2;

-- 如果某条件满足，回滚到 sp1
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

如果定义了相同名字的 savepoint，后面的定义会覆盖之前的
不再需要的 savepoint 可以用 RELEASE SAVEPOINT 删除
删除后的 savepoint 不能再执行 ROLLBACK TO SAVEPOINT

注意事项：

在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事务类型的表进行特别处理
commit、rollback 只能对事务类型的表进行提交和回滚
开始一个事务会造成一个隐含的 UNLOCK TABLES 被执行
对 LOCK 方式加的表锁，不能通过 ROLLBACK 进行回滚

MySQL - 事务与锁