tickets 表里只剩一张票。客户端 A 读到 nums = 1,判断“有票”;客户端 B 也读到 1,也判断“有票”;两边各自执行 update nums = nums - 1,同一张票卖给了两个人。问题不在某一条 SQL,而在“读—判断—写”这三步之间既没有原子性,也没有隔离。

客户端 A 和 B 同时检查到 nums=1,各自卖票、各自更新;两次更新都成功,票数却只该减一次

事务(transaction)把一组逻辑相关的 DML 包成一个整体:要么全部生效,要么全部不生效。它同时规定不同客户端能看到什么。一个完整的事务要满足四个属性,也就是 ACID:

  • 原子性(Atomicity):事务里的操作要么全做,要么全不做。执行到一半出错,回滚(rollback)到事务开始前的状态。
  • 一致性(Consistency):事务前后,数据库的完整性约束仍然成立。一致性是结果状态,技术上靠原子性、隔离性和持久性支撑,业务规则仍要应用层自己守。
  • 隔离性(Isolation):并发事务之间不因为交叉执行而看到互相污染的数据。
  • 持久性(Durability):事务提交后,修改就是永久的,系统故障也不丢。

事务不是数据库天生就有的语义,它是给应用层用的:把网络中断、进程崩溃、并发改写这些情况收进 commit 和 rollback 两个动作里。

show engines;

InnoDB 的 Transactions 和 Savepoints 都是 YES;MyISAM 两项都是 NO。只有 InnoDB 支持事务和保存点,在 MyISAM 表上执行 rollback 没有任何效果。

show variables like 'autocommit';
set autocommit = 0;

默认是 ON,每条 SQL 自己就是一个事务,执行完立即提交。关掉之后,后续语句进入一个隐式事务,直到 commit 或 rollback。

begin 之后,崩溃时数据在不在

测试表沿用课件的字段名,blance 是课件里的拼写,正确英文是 balance:

create table if not exists account (
    id     int primary key,
    name   varchar(50) not null default '',
    blance decimal(10,2) not null default 0.0
) engine=InnoDB default charset=utf8mb4;

事务内部可以分段回退:

begin;
savepoint save1;
insert into account values (1, '张三', 100);
 
savepoint save2;
insert into account values (2, '李四', 10000);
 
select * from account;              -- 两条都在
 
rollback to savepoint save2;        -- 只撤掉李四
select * from account;              -- 只剩张三
 
rollback;                           -- 回到事务开头,张三也没了
select * from account;              -- Empty set

rollback to savepoint 不结束事务,回退后还能继续执行、继续提交。没有保存点时,rollback 只能回到事务开头;commit 之后就不能再 rollback。start transaction 和 begin 都是开启事务,前者还能带 read only、read write、with consistent snapshot 修饰。

课件把“插一行、让客户端异常终止”重复了四遍,每次只改 autocommit 和 begin/commit 的组合。结果可以压成一张表:

autocommit写 begincommit崩溃后
ON有无回滚
ON有有保留
OFF有无回滚
OFF无—回滚
ON无—保留

结论只有一句:显式开了事务之后,数据能不能留下来只取决于有没有 commit,跟 autocommit 当前是 ON 还是 OFF 无关。

课件给其中一段演示起的小标题是“证明 begin 操作会自动更改提交方式”,这个说法不对。begin 只是开启一个显式事务,它不会修改 autocommit 变量;演示里 begin 之后紧接着 show variables like 'autocommit',输出仍然是 ON。显式事务要手动提交,是因为它已经脱离了 autocommit 的控制,不是因为变量被改了。

commit 也不是把数据页立刻刷盘。InnoDB 先把改动写进 redo log,默认配置下提交时保证日志落盘;数据页由后台线程慢慢刷。崩溃重启后按 redo 重放已提交的改动,undo 负责撤销未提交的改动。两个日志方向相反,缺一不可。

还有一条容易被忘掉的边界:alter table、create index、drop table 这类 DDL 会隐式提交当前事务。把 DDL 和业务 DML 混在一个事务里,回滚是回不到 DDL 之前的。

四个隔离级别,分别挡住哪一个现象

先记三个现象:

  • 脏读(dirty read):读到别的事务还没提交的修改。
  • 不可重复读(non-repeatable read):同一个事务里,同样的查询两次读到不同的值,通常是别人 update 或 delete 造成的。
  • 幻读(phantom read):同一个事务里,同样的范围条件两次读到不同的行数,通常是别人 insert 造成的。

课件自己的隔离级别表:读未提交三种问题都会发生;读提交只剩不可重复读和幻读;可重复读一栏的三种问题都标成不会发生;串行化加锁

这张表里可重复读的幻读标的是“不会发生”,和前面定义处的文字“会有幻读问题”对不上。以表和实践为准:SQL 标准允许可重复读出现幻读,InnoDB 的可重复读没有让它发生。

下面几段演示前,先插入一行测试数据,两个终端的会话级别也要设成同一个值:

insert into account values (1, '张三', 100);

读未提交:

-- 两个终端都执行
set session transaction isolation level read uncommitted;
 
-- 终端 A
begin;
update account set blance = 123.0 where id = 1;
-- 不 commit
 
-- 终端 B
begin;
select * from account;   -- 看到 123.00
 
-- 演示完再提交,让后面的演示从 123.00 开始
commit;
 
-- 终端 A
commit;

B 读到了 A 还没提交的值,这就是脏读。这个级别几乎不加锁,隔离性最弱,实际生产不用,课件拿它来演示最方便。

读提交。下面假设 id = 1 这一行的当前值是 123.00:

-- 两个终端都执行
set session transaction isolation level read committed;
 
-- 终端 A
begin;
update account set blance = 321.0 where id = 1;
-- 先不提交
 
-- 终端 B
begin;
select * from account;   -- 仍是旧值 123.00
 
-- 终端 A
commit;
 
-- 终端 B 再查
select * from account;   -- 变成 321.00
commit;

B 在同一个事务里读到了两个值,这就是不可重复读。Oracle 和 PostgreSQL 的默认级别都是读提交。

可重复读:

-- 两个终端都执行
set session transaction isolation level repeatable read;
 
-- 终端 B 先开事务并读一次
begin;
select * from account;   -- 321.00
 
-- 终端 A 再更新并提交
begin;
update account set blance = 4321.0 where id = 1;
commit;
 
-- 终端 B 再读
select * from account;   -- 仍是 321.00
commit;
 
select * from account;   -- 新事务才看到 4321.00

B 的事务开始后,不管 A 什么时候提交,B 在事务里看到的都是同一个版本;B 提交完再开新事务,才看得到新值。这是 MySQL 的默认级别。

幻读用 insert 演示:

-- 终端 B 先开事务并读一次
begin;
select * from account;   -- 一条
 
-- 终端 A 插入并提交
begin;
insert into account (id, name, blance) values (3, '王五', 5432.0);
commit;
 
-- 终端 B 再读
select * from account;   -- 还是一条,看不到王五
commit;
 
select * from account;   -- 两条

普通 select 走快照读,看到的是事务开始时的那个版本,新插入的行天然不可见。如果 B 用的是 for update 或 lock in share mode,走的是当前读,会看到最新提交的版本;阻挡并发插入靠的是 next-key 锁,不是 Read View。

串行化:

-- 两个终端都执行
set session transaction isolation level serializable;
 
-- 终端 B 先开事务并读一次,读锁落在行上
begin;
select * from account;
 
-- 终端 A 的 update 这时只能等
begin;
update account set blance = 1.00 where id = 1;   -- 阻塞
 
-- 终端 B 提交,A 的 update 才返回
commit;
 
-- 终端 A
commit;

串行化把普通 select 也按共享锁执行。两个读不冲突,但 A 的写要等 B 提交,课件实测等了 18.19 秒。安全,吞吐极低。

课件说“隔离基本都是通过锁实现的”,这句话只说对了一半:写操作和当前读靠锁,普通 select 在读提交和可重复读下靠 MVCC 的无锁快照读。把两者都归给锁,会解释不了为什么快照读不阻塞写。

级别越高越安全,并发越低。不可重复读关注已有行的值变了,幻读关注行数变了。MySQL 默认可重复读,一般不改。

查看和设置:

select @@global.transaction_isolation;
select @@session.transaction_isolation;
select @@transaction_isolation;
 
set session transaction isolation level repeatable read;
set global transaction isolation level read committed;

@@tx_isolation 是 5.7 的旧名字,从 5.7.20 起废弃,8.0.3 移除;现在用 transaction_isolation。set global 只影响之后新建的连接,已有会话要重连才生效。

版本链和 Read View

并发场景分三种:读-读不需要控制;读-写是 MVCC 要解决的,读不加锁,写不阻塞读;写-写要靠锁排队。MVCC(multi-version concurrency control)的做法是每次修改都留下旧版本,读操作按自己的快照挑一个版本。

每行记录藏着几个字段:

字段长度作用
DB_TRX_ID6 字节最近一次修改这条记录的事务 ID
DB_ROLL_PTR7 字节回滚指针,指向 undo 里的上一个版本
DB_ROW_ID6 字节没有显式主键时,用它生成聚簇索引

记录头里还有一个删除标记位。delete 不是物理删除,是把这一位置 1,旧版本仍然留在链上。

事务 10 把“张三”改成“李四”:先给该行加锁,把当前行拷进 undo log,再改原记录,DB_TRX_ID 写成 10,DB_ROLL_PTR 指向 undo 里的副本。事务 11 再把 age 从 28 改成 38,重复一遍,新副本用头插法接到链上。

事务 10 提交后的状态:当前行是李四/28,DB_TRX_ID 是 10,DB_ROLL_PTR 指向 undo 里的张三/28;版本链就是沿这个指针往旧版本走

课件把 undo log 说成“一段内存缓冲区”,这个简化过头了。undo 记录落在 undo 表空间里,是持久化的,Buffer Pool 只是缓存。如果 undo 只存在内存,服务器崩了之后未提交事务就没法回滚,原子性也就无从谈起。

insert 没有旧版本,不形成版本链;但为了回滚,插入动作也会记 undo,事务提交后这条 undo 可以清理。update 和 delete 才会形成链。

读取分两种:

  • 当前读:读最新版本,insert、update、delete、for update、lock in share mode 都算。
  • 快照读:读事务该看到的版本,普通 select 算。

快照读选哪一版,由 Read View 决定。它记四样东西:

字段含义
m_ids生成视图时所有活跃事务的 ID 列表
m_up_limit_idm_ids 里的最小值,低水位
m_low_limit_id系统尚未分配的下一个事务 ID,高水位
m_creator_trx_id创建这个视图的事务 ID

判断一条记录的某个版本可不可见,就拿它的 DB_TRX_ID 去比:

  1. 等于 creator_trx_id:自己改的,可见。
  2. 小于 up_limit_id:生成视图时已经提交,可见。
  3. 大于等于 low_limit_id:视图生成之后才出现的事务,不可见。
  4. 落在 m_ids 里:生成视图时还没提交,不可见。
  5. 其余情况可见。

不可见就顺着 DB_ROLL_PTR 找上一个版本,直到找到第一个可见的。

时间轴把判断讲清楚:快照生成前已提交的事务在左边,可见;生成时仍活跃的事务落在 m_ids 里,不可见;快照之后才出现的事务 ID 大于等于高水位,也不可见

事务 2 建快照时,最新版本由事务 4 写入(DB_TRX_ID=4)。事务 4 已经提交,所以这个版本对事务 2 可见;再往下的张三版本才是事务 4 之前的旧版本

可重复读和读提交的差别只有一处:可重复读只在该事务第一次快照读时生成一个 Read View,之后一直用它;读提交每次快照读都新建一个。读提交每次拿最新的活跃列表,别人一提交,下一次 select 就看得见,不可重复读由此而来;可重复读拿的是第一次的列表,之后别人提交也看不见。

课件里两个对照用例的差别只在于“事务 B 在 A 修改前有没有做过一次快照读”。做过,第一次快照读就定下了 Read View,后面看到旧值;没做过,第一次快照读发生在 A 提交之后,看到的是新值。

当前读不受 Read View 限制,所以可重复读下 for update 能看到别人刚提交的新值。这不是可重复读失效,是两种读法不同。

锁、死锁和长事务

行锁挂在索引记录上,不是挂在“一行抽象数据”上。where 条件能用到索引,才锁得住少量记录;没有索引,InnoDB 只能扫一遍并锁住扫过的记录,范围会放大到接近全表。

类型作用
共享锁 S读锁,多个事务可以同时持有
排他锁 X写锁,和任何锁冲突
行锁锁住索引记录,InnoDB 的默认粒度
表锁锁整张表,MyISAM 常用;InnoDB 在特定场景使用
意向锁表级标记,表示表里有行锁
间隙锁锁住索引记录之间的空隙,阻止插入
next-key 锁记录锁加间隙锁,可重复读下防幻读
select * from account where id = 1 lock in share mode;   -- 共享锁
select * from account where id = 1 for update;           -- 排他锁

两个事务都“读旧值、算新值、写回去”,还会丢更新:库存 10,A 读到 10 卖 3 写回 7,B 读到 10 卖 5 写回 5,A 的更新被盖掉。要么先用 for update 锁住这一行再算,要么把读和写合成一条 update stock set num = num - 3 where id = 1 and num >= 3。

死锁是锁的副产品。两个事务各持一把锁,再等对方那把,就成环。InnoDB 有死锁检测,会回滚其中一个,报:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

应用层捕到 1213 应该重试整个事务,不是只重试报错那条语句。降低概率的办法不复杂:所有事务按同一顺序访问资源,缩短事务,给更新条件加索引。

排查死锁和长事务:

select * from performance_schema.data_lock_waits;   -- 5.7 用 information_schema.innodb_lock_waits
 
select trx_id, trx_state, trx_started,
       timestampdiff(second, trx_started, now()) as seconds,
       trx_mysql_thread_id, trx_query
from information_schema.innodb_trx
order by trx_started;
 
show engine innodb status;

show engine innodb status 的 LATEST DETECTED DEADLOCK 段会给出两个事务的 SQL 和持锁顺序。innodb_trx 里 trx_started 很早、trx_query 还挂着的,就是长事务的候选。

长事务的代价是复利的:undo 不能及时 purge,版本链越拖越长,快照读要沿链走更远;行锁持有时间长,别人排队;崩溃恢复要重放更多日志。监控里看到事务活了几分钟还不结束,先查它在等什么,再决定是回滚还是叫业务方修代码。