第43问：锁用得太多, 为什么要调整 Buffer Pool

当我们使用一个事务操作很多数据时, MySQL 有时会报错: The total number of locks exceeds the lock table size

根据官方文档, 我们需要调大 buffer pool 的大小:

本期实验, 我们来探索一下锁用得多与 buffer pool 大小的关系

实验

我们用老方法建一个数据库, 并将 buffer pool 大小调整到了最小值5M, 方便我们复现问题

现在来模拟一个用锁特别多的事务:

我们还是用老方法让表翻倍, 来不停地占用锁.

看一下效果:

我们可以通过 information_schema.INNODB_TRX 来查看事务使用了多少锁, 解释一下上图中标记的这几个状态:

• trx_tables_locked: 该事务锁了几张表
• trx_rows_locked: 该事务锁了多少数据行
• trx_lock_structs: 该事务一共用到了多少个锁结构. 一个锁结构用于锁住多个表或多个行
• trx_lock_memory_bytes: 该事务的锁结构一共用了多少内存

再来看看 buffer pool 的状态:

解释一下 Buffer pool 的这两个状态:

• total 是 Buffer pool 的总页数
• misc 是 Buffer pool 中非数据页的页数

我们继续造数据, 让该事务使用的锁越来越多, 再来看看状态:

与最初的状态相比, 该事务使用的锁的内存增长了 (1269968 - 24784 = ) 1245184 字节 = 1216 k, 而 buffer pool 非数据页多使用了 (84-8 = ) 76页, 每页16k, 总共 1216 k

也就是说, 该事务的锁内存均分自 buffer pool .

我们继续造数据, 造到报错为止, 不停查看状态:

可以看到, 发生报错时, 事务会回滚. 回滚前, 该事务的锁占用的内存大小是 3842256 字节, 也就是 ( 3842256 / 1024 / 16 = ) 234.5 页, 此时 buffer pool 使用率大概是 ( 234.5 / 320 = ) 73%

我们的实验到此结束, 通过此实验我们知道:

1. 如果事务用到了很多锁, 那么锁结构使用的内存是从 Buffer pool 中分配的
2. Buffer pool 使用率比较高时, 就会报错: The total number of locks exceeds the lock table size

关于 Buffer pool 使用到多少, 会进行报错, 我们参考如下 MySQL 源码:

我们可以看到这个理论阈值是75%, 当 Buffer pool 中 非数据页的大小超过了75%, 我们就会再无法分配更多的锁.

(实验中测定的73%与这个理论阈值相差不大)

小贴士 当 Buffer pool 的大小不足时, Buffer pool 可以回收数据页 (干净页可直接回收, 脏页可刷脏后回收), 而不能直接回收 非数据页. MySQL 设定这个75%的阈值, 也是为了让 Buffer pool 中留一部分空间给数据页, 毕竟 Buffer pool 的最大作用是给数据页做缓存.