Quartz 5

MySQL 数据库 10（Day58）

Properties1
tags来源, 数据库, mysql, 事务, 锁, innodb, data

10分钟阅读

MySQL 数据库 10（Day58）

主题：事务运行模式、保存点与局部回滚、并发读现象、数据库锁分类、表级锁、写操作默认排他锁、锁等待、死锁与意向锁

结构总览

MySQL 数据库 10
  ├─ 事务复习：ACID、BEGIN/COMMIT/ROLLBACK
  ├─ 自动提交、显式事务、隐式事务
  ├─ SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT
  ├─ 脏读、不可重复读、幻读
  ├─ 锁分类：粒度、权限、使用方式
  ├─ 表级锁与写操作默认排他锁
  └─ 锁等待、死锁、意向锁与锁排查

关键要点

事务与三种运行模式

事务是一组不可分割的数据库操作：要么全部提交，要么在异常时回滚。事务通过 ACID 特性保障转账、支付、库存扣减等多步骤业务的一致性。

START TRANSACTION;
UPDATE user SET balance = balance - 100 WHERE name = 'wsb';
UPDATE user SET balance = balance + 10 WHERE name = 'egon';
UPDATE user SET balance = balance + 90 WHERE name = 'ysb';
COMMIT;

MySQL 常见事务运行方式：

模式行为适用场景
自动提交每条修改语句执行后自动提交，通常是默认模式单条独立操作
显式事务手动 BEGIN/START TRANSACTION，用 COMMIT 或 ROLLBACK 结束多条 SQL 必须整体成功
隐式事务关闭自动提交后事务自动开始，仍需手动提交或回滚连续事务控制
SELECT @@autocommit;
SET autocommit = 1;
 
START TRANSACTION;
UPDATE employee SET name = 'EGON_NB' WHERE id = 1;
COMMIT;
 
SET autocommit = 0;
UPDATE employee SET name = 'WXX' WHERE id = 3;
ROLLBACK;
SET autocommit = 1;

注意：事务应使用支持事务的存储引擎；执行 DDL 等可能产生隐式提交的语句时，不能假设所有操作都能回滚。关闭自动提交后应及时结束事务，避免连接长期占用锁。

保存点

**保存点（Savepoint）**是事务内部的临时回滚位置。ROLLBACK TO SAVEPOINT 只撤销指定保存点之后的修改，事务仍然可以继续执行；COMMIT 或完整 ROLLBACK 后保存点失效。

START TRANSACTION;
UPDATE employee SET name = 'EGON_NB' WHERE id = 1;
UPDATE employee SET name = 'ALEX_SB' WHERE id = 2;
SAVEPOINT one;
 
UPDATE employee SET name = 'YXX_SB' WHERE id = 4;
UPDATE employee SET name = 'LXX' WHERE id = 5;
SAVEPOINT two;
INSERT INTO employee VALUES (19, 'egonxxx', 19);
 
ROLLBACK TO SAVEPOINT two;
COMMIT;

上例会保留 two 之前的修改，撤销其后的插入，但不会结束事务。删除不再需要的保存点：

RELEASE SAVEPOINT one;

常见错误：把局部回滚写成普通 ROLLBACK、使用不存在的保存点、事务提交后继续使用旧保存点。

并发读现象

现象说明
脏读读取其他事务尚未提交、可能随后回滚的修改
不可重复读同一事务两次读取同一行，期间其他事务修改并提交
幻读同一事务按相同条件查询，第二次出现新的符合条件记录或记录数变化

现象是否出现取决于隔离级别、存储引擎和并发事务的执行顺序。测试时需要至少两个数据库连接，记录事务开始、读取、修改与提交时序。

锁的分类

锁用于协调并发访问，在提高数据安全性的同时会带来等待和并发损耗。常见分类如下：

分类维度类型说明
粒度行级锁、表级锁、页级锁行锁范围小、并发能力通常较高；表锁范围大、实现简单
访问权限共享锁 S、排他锁 X共享锁通常允许并发读；排他锁用于独占修改
使用方式悲观锁、乐观锁悲观锁操作前先加锁；乐观锁提交时用版本号或条件检查冲突

手动加共享锁和排他锁：

START TRANSACTION;
SELECT * FROM employee WHERE id = 3 FOR SHARE;
 
START TRANSACTION;
SELECT * FROM employee WHERE id = 3 FOR UPDATE;

LOCK IN SHARE MODE 是较旧的共享锁写法。普通一致性读不等同于加锁读，不同存储引擎与隔离级别的行为也可能不同。

表级锁

表级锁以整张表为单位，管理简单但影响范围较大。手动读锁允许多个连接读取、限制写入；写锁通常只允许当前连接读写，其他连接的读写会受到限制。

LOCK TABLES employee READ;
UNLOCK TABLES;
 
LOCK TABLES employee WRITE;
UNLOCK TABLES;

查看表锁和存储引擎：

SHOW OPEN TABLES WHERE In_use > 0;
SHOW TABLE STATUS LIKE 'employee';

高并发业务中应避免在事务里随意持有大范围表锁，并在操作结束后及时 UNLOCK TABLES。

写操作与排他锁

支持事务的存储引擎中，INSERT、UPDATE、DELETE 等写操作通常会自动申请排他锁。相关事务锁一般持续到事务提交或回滚。

-- 事务一
START TRANSACTION;
UPDATE employee SET name = 'EGON_LOCK' WHERE id = 3;
 
-- 事务二更新相同记录时可能等待
START TRANSACTION;
UPDATE employee SET name = 'ALEX_LOCK' WHERE id = 3;
SET SESSION innodb_lock_wait_timeout = 10;

写操作的条件应尽量命中合适索引，避免扫描和锁定范围过大：

EXPLAIN
UPDATE employee SET name = 'EGON_LOCK' WHERE id = 3;

锁等待、死锁与意向锁

  • 锁等待：一个事务持有资源锁，另一个事务申请冲突锁时进入等待。
  • 死锁：两个或多个事务互相持有对方需要的锁，形成循环等待。InnoDB 通常会检测死锁并回滚其中一个事务。
  • 意向锁：表级标记，表示事务准备在表中的某些行上加共享锁或排他锁，帮助快速判断表级锁与行级锁的潜在冲突。常见类型为 IS、IX。

典型死锁：事务一先锁记录 1 再请求记录 5；事务二先锁记录 5 再请求记录 1。预防与处理：

  1. 让事务按统一顺序访问多张表或多条记录。
  2. 缩短事务范围和锁持有时间。
  3. 为查询和更新条件建立合适索引。
  4. 在应用层捕获死锁异常并重试事务。
  5. 通过锁等待超时限制异常等待时间。

排查命令：

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM sys.innodb_lock_waits;

出现锁等待时，应先找出阻塞事务和持锁 SQL，而不是盲目重复执行原语句。事务执行时间过长、缺少索引和不一致的访问顺序，都是常见根因。

当日总结

  • 自动提交适合单条操作；显式/隐式事务适合多条 SQL 的整体控制。
  • 保存点支持事务局部回滚，不会结束当前事务。
  • 行锁范围小但管理复杂，表锁范围大且会降低并发。
  • INSERT、UPDATE、DELETE 通常自动申请排他锁，锁一般在提交或回滚后释放。
  • 锁等待和死锁要结合事务状态、阻塞关系、执行计划分析。
  • 统一访问顺序、缩小事务范围、合理建索引和及时结束事务可以降低锁冲突。

相关页面

最近更新

关系图谱

反向链接