4.6 TiDB 中事务限制及应对方案
在 2.1 及之前的 TiDB 版本中，对于事务的限制是和其他关系型数据库而言比较特殊的地方，很多用户在使用过程中总是会感觉比较困惑，本文针对事务限制做一些详细的说明，希望能够帮助大家理解。
4.6.1 官方定义
>
由于 TiDB 分布式两阶段提交的要求，修改数据的大事务可能会出现一些问题。因此，TiDB 特意对事务大小设置了一些限制以减少这种影响：
每个键值对不超过 6MB
键值对的总数不超过 300,000
键值对的总大小不超过 100MB
单个事务包含的 SQL 语句不超过 5000 条（默认）
键值对应该比较容易理解，毕竟 TiDB 底层存储选用的是 RocksDB 引擎，一种基于 Key-Value 的存储结构。而每个键值对的大小和总大小限制分别是 6MB 和 100MB，这个应该也比较容易理解。关键在于每个事务包含键值对的总数不超过 30W，这个经常会引起一些误解，下面做一些详细说明。
4.6.2 如何理解 30W
很多人第一眼看上去，以为是一个事务涉及的行数不能超过 30W，但其实不是这样的，首先需要了解 TiKV 是如何将结构化数据转化为 Key-Value 结构存储的。
对于 Key-Value 结构的数据，结构如下：
| Key | Value | Flag |
|---|---|---|
- Insert操作
插入数据时，TiKV 的处理包含以下几个步骤：
(1) 插入数据本身
| Key: PK + TSO | Value: Fields | Flag: Put |
|---|---|---|
(2) 插入唯一索引
| Key: Index (UK) + TSO | Value: PK | Flag: Put |
|---|---|---|
(3) 插入普通索引
| Key: Index + PK + TSO | Value: Null | Flag: Put |
|---|---|---|
综上，当执行 Insert 事务时，30W 限制需要除以 （1 + 所有索引的数量 (包含唯一索引))。
- Delete操作
下面考虑当删除一条数据时，TiKV 是如何处理的。首先需要明确，RocksDB 引擎所有的操作都是新增，所以删除也是插入，相当于插入了一条 Flag = Del 的记录。具体步骤如下：
(1) 插入数据本身的删除标记
| Key: PK + TSO | Value: Null | Flag: Del |
|---|---|---|
(2) 插入唯一索引的删除标记
| Key: Index (UK) + TSO | Value: Null | Flag: Del |
|---|---|---|
(3) 插入普通索引的删除标记
| Key: Index + PK + TSO | Value: Null | Flag: Del |
|---|---|---|
综上，当执行 Delete 事务时，30W 限制需要除以 (1 + 所有索引的数量 (包含唯一索引))。
- Update操作
首先，我们看看如果更新的是非主键且无索引字段的情况。这种情况，只需要修改记录本身的内容即可，也就是下面一步：
(1) 插入数据本身即可
| Key: PK + TSO | Value: Fields | Flag: Put |
|---|---|---|
综上，非主键且无索引字段更新，30W 限制就是 30W。
其次，来看更新的是非主键，但包含索引的字段情况。
(1) 数据本身
| Key: PK + TSO | Value: Fields | Flag: Put |
|---|---|---|
(2) 如果更新字段上有唯一索引
| Key: Index (UK) + TSO | Value: Null | Flag: Del |
|---|---|---|
| Key: Index (UK) + TSO | Value: PK | Flag: Put |
|---|---|---|
(3) 如果更新字段上有普通索引
| Key: Index + PK + TSO | Value: Null | Flag: Del |
|---|---|---|
| Key: Index + PK + TSO | Value: Null | Flag: Put |
|---|---|---|
综上，非主键但索引相关字段的更新，30W 限制需要除以 (1 + 字段涉及索引数量 * 2)。
最后来看当更新的是主键字段的情况。
从上面的插入描述中可以看出，无论是数据本身，还是索引，都包含了 PK ，所以主键更新会触发所有的 Key 更新，具体如下：
(1) 数据本身
| Key: PK + TSO | Value: Null | Flag: Del |
|---|---|---|
| Key: PK + TSO | Value: Fields | Flag: Put |
|---|---|---|
(2) 所有的唯一索引
| Key: Index (UK) + TSO | Value: PK | Flag: Put |
|---|---|---|
(3) 所有的普通索引
| Key: Index + PK + TSO | Value: Null | Flag: Del |
|---|---|---|
| Key: Index + PK + TSO | Value: Null | Flag: Put |
|---|---|---|
综上，主键字段的更新，30W 限制需要除以 ((1 + 普通索引数量) * 2 + 唯一索引数量) ，Update 主键的时候，唯一索引当做 1 个 KV，普通索引和主键当做 2 个 KV（唯一索引在对应的 Key-Value 中，Key 是 UK 的值，Value 是 PK，Update PK 的时候，Key 值不变，所以只需一次 Put kv 操作；其他的，比如普通索引，Key 里面就存了 PK 的值，这样 Update 的时候记录的 Del 是一个 kv，Put 是一个新的 kv，所以当做两次处理）。
4.6.3 30W 键值对的转换
总结如下： |操作|键值对转换公式| |:----:|:----:| | Insert | 30W/(1+Idx_Count) | | Delete | 30W/(1+Idx_Count) | | Update_On_PK | 30W/((1+Non_UK)*2+UK*1) | | Update_non_PK | 30W/(1+Involved_Idx_Count*2) |
具体案例：
CREATE TABLE t1 (
id int(11) NOT Null AUTO_INCREMENT,
name char(10) CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT Null,
age int(11) DEFAULT Null,
PRIMARY Key (id),
Key idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
以上面的简单表结构为例，该表有自增主键，外加 1 个普通索引，那么上面的事务限制对应的记录数为 ：
| 操作 | 键值对转换公式 | 最大操作行数 |
|---|---|---|
| Insert | 30W/(1+1) | 15W |
| Delete | 30W/(1+1) | 15W |
| Update_On_id | 30W/((1+1)*2 + 0) | 7.5W |
| Update_On_name | 30W/(1+1*2) | 10W |
| Update_On_age | 30W/(1+0*2) | 30W |
对于 TiDB 来说，有一个特殊之处，就是当主键是非 int 类型时，会有一个隐藏 int 类型主键，同时，本身定义的这个主键变成了唯一索引。所以，修改下上面表定义为如下：
CREATE TABLE t1 (
id varchar(11) NOT Null,
name char(10) CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT Null,
age int(11) DEFAULT Null,
PRIMARY Key (id),
Key idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
那么，该表有一个隐藏主键，外加 1 个唯一索引 (用户定义的主键)，外加 1 个普通索引，那么上面的事务限制对应的记录数为：
| 操作 | 键值对转换公式 | 最大操作行数 |
|---|---|---|
| Insert | 30W/(1+2) | 10W |
| Delete | 30W/(1+2) | 10W |
| Update_On_id | 30W/(1+1*2) | 10W |
| Update_On_name | 30W/(1+1*2) | 10W |
| Update_On_age | 30W/(1+0*2) | 30W |
4.6.4 事务的其他限制
除了上述限制以外，TiDB 中对于事务还有另外一个限制
(1) 参数 stmt-count-limit，默认值是 5000。
StmtCountLimit limits the max count of statement inside a transaction.
也就是一个事务里面，默认最多包含 5000 条 SQL statement，在不超过上面几个限制的前提下，这个参数可以修改 TiDB 的配置文件进行调整。
(2) 另外在某些场景下，例如执行 Insert Into Select 的时候，可能会遇到下面的报错
ERROR 1105 (HY000): BatchInsert failed with error: [try again later]: con:3877 txn takes too much time, start: 405023027269206017, commit: 405023312534306817
这个主要是有一个隐藏参数，max-txn-time-use，默认值是 gc_life_time - 10s，也就是 590。
具体参考 PingCAP GitHub 上的文档：https://github.com/pingcap/TiDB/blob/master/config/config.toml.example#L240
# The max time a Txn may use (in seconds) from its startTS to commitTS. # We use it to guarantee GC worker will not influence any active txn. Please make sure that this# Value is less than gc_life_time - 10s.
所以我们要尽量保证一个事务在这个 gc_life_time - 10s 的时间内完成，也可以通过调整 gc 时间 + 修改这个参数来避免这个问题，可能 TiDB 的配置文件中没有放出这个参数，可以手动编辑加入这个值。当然，更好的办法应该是开启 Batch 功能来规避单个事务过大的问题。
4.6.5 如何绕开大事务的限制
官方提供内部 Batch 的方法来绕过大事务的限制，分别由三个参数来控制：
- tidb_batch_insert
作用域: SESSION 默认值: 0 这个变量用来设置是否自动切分插入数据。仅在 Autocommit 开启时有效。 当插入大量数据时，可以将其设置为 True，这样插入数据会被自动切分为多个 Batch，每个 Batch 使用一个单独的事务进行插入。
- tidb_batch_delete
作用域: SESSION 默认值: 0 这个变量用来设置是否自动切分待删除的数据。仅在 Autocommit 开启时有效。 当删除大量数据时，可以将其设置为 True，这样待删除数据会被自动切分为多个 Batch，每个 Batch 使用一个单独的事务进行删除。
- tidb_dml_batch_size
作用域: SESSION 默认值: 20000 这个变量用来设置自动切分插入 / 待删除数据的的 Batch 大小。仅在 tidb_batch_insert 或 tidb_batch_delete 开启时有效。 需要注意的是，当单行数据大小很大时，20k 行总数据量有可能会超过单个事务大小限制，因此在这种情况下，用户应当将该参数设置为一个较小的合适值。
针对 Update 场景，官方还是建议通过 limit 的方式来循环操作，目前并未提供内部 Batch Update 的参数开关。
需要注意的是，开启了 Batch 功能之后，大事务的完整性就没法保证，只能保证每个批次的事务完整性。当然，数据库的最佳实践依然是由程序或 DBA 来控制事务的大小，尤其是针对分布式数据库，建议每个 Batch 控制在 100 条左右，高并发的写入，同时避免热点现象，才能发挥 TiDB 分布式的优势。