一、背景

最近我们在做线上的数据迁移测试（可以理解就是把A数据中心的数据迁移到B数据中心，A和B数据中心的MySQL是同构的，迁移过程中，A、B的MySQL都有正常的业务数据写入。每次我们触发迁移的时候，就有业务方反馈他们写入数据的时候就会有Error 1062: Duplicate entry 'xxx' for key 'PRIMARY'这样的错误。业务方同学还反馈他们写数据的时候并没有指定ID，所以他们对这样的报错比较困惑，具体他们的数据写入的伪代码如下：

type Data struct {
    ID           int64     `gorm:"primaryKey;column:id"`
    PageID       string    `gorm:"column:page_id`
    CreateTime   time.Time `gorm:"column:create_time"`
    ModifiedTime time.Time `gorm:"column:modified_time"`
}

data := &Data{
                PageID:       uuid.NewString(),
                CreateTime:   now,
                ModifiedTime: now,
            }

err := db.Create(data).Error
if err != nil {
    return err
}

再交代一下其他的背景。

业务上这个表的写入的TPS相对比较高，迁移的数据量也比较大。
我们做数据迁移的时候，从A数据中心迁移到B数据中心的时候，会抹掉数据中的ID数据，然后用一个中心的发号器IDGenerator生成一个新的ID，然后再插入这个数据。

由于，每次都是在数据迁移的时候，报这个PK Duplicate Error的错误，基本肯定是我们做数据迁移导致的。引出几个问题：

生成自增ID实现方式？并发生成ID会不会冲突？
生成自增ID加锁机制粒度，锁的释放机制是啥？
生成自增ID和唯一索引冲突检查流程是怎么样的？

其实已知的问题上看，基本猜想出，具体出现问题的场景如下：

TimeLine	Session 1	Session 2
时刻1	生成自增ID	用IDgen生成ID
时刻2		唯一索引冲突检查（Pass）
时刻3		写入成功
时刻4	唯一索引冲突检查（报错Duplicate Error）

结论我们知道，但是MySQL的Insert流程到底是如何做的，我并不清楚，问了一圈身边的朋友，好像大家对Insert过程这些细节都不太了解，所以决定自己简单撸下源码验证一下上面的结论。

二、Auto-Incr 背景知识

MySQL的《AUTO_INCREMENT Handling in InnoDB》这篇官方文档，其实把AUTO_INCREMENT相关特性都介绍很清楚了，我们做个简单总结。

InnoDB提供了一种可配置的锁定机制，可以显着提高向具有AUTO_INCREMENT列的表添加行的SQL语句的可伸缩性和性能。
定义为AUTO_INCREMENT的列，必须是索引的第一列或者是唯一列，因为需要使用SELECT MAX(ai_col)查找以获得最大值列值。不这样定义，Create Table的时候会报1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key错误。
AUTO_INCREMENT的列，可以只定义为普通索引，不一定要是PRIMARY KEY或者UNIQUE，但是为了保证AUTO_INCREMENT的唯一性，建议定义为PK或者UNIQUE

2.1 MySQL插入语句的几种类型

在介绍AUTO_INCREMENT的锁模式之前，先介绍下，MySQL插入的几种类型：

Simple inserts，可以预先确定要插入的行数（当语句被初始处理时）的语句。这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句。如下：
```
  INSERT INTO t1 (c2) VALUES ('xxx');
  
```
Bulk inserts，事先不知道要插入的行数（和所需自动递增值的数量）的语句。这包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。 InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。
```
  INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
```
Mixed-mode inserts，这些是Simple inserts语句但是指定一些（但不是全部）新行的自动递增值。示例如下，其中c1是表t1的AUTO_INCREMENT列：
```
  INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  
```
另一种类型的Mixed-mode inserts是INSERT ... ON DUPLICATE KEY UPDATE，其在最坏的情况下实际上是INSERT语句随后又跟了一个UPDATE，其中AUTO_INCREMENT列的分配值不一定会在UPDATE阶段使用。
INSERT-like ，以上所有插入语句的统称。

2.2 AUTO_INCREMENT 锁模式

MySQL可以通过设置innodb_autoinc_lock_mode 变量来配置AUTO_INCREMENT列的锁模式，分别可以设置为0、1、2 三种模式。

0：传统模式（traditional）

传统的锁定模式提供了与引入innodb_autoinc_lock_mode变量之前相同的行为。由于语义上可能存在差异，提供传统锁定模式选项是为了向后兼容、性能测试和解决“混合模式插入”问题。
在这一模式下，所有的insert语句(insert like) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

1：连续模式（consecutive）

在这种模式下，对于simple insert语句，MySQL会在语句执行的初始阶段将一条语句需要的所有自增值会一次性分配出来，并且通过设置一个互斥量来保证自增序列的一致性，一旦自增值生成完毕，这个互斥量会立即释放，不需要等到语句执行结束。所以，在consecutive模式，多事务并发执行simple insert这类语句时，相对traditional模式，性能会有比较大的提升。
由于一开始就为语句分配了所有需要的自增值，那么对于像Mixed-mode insert这类语句，就有可能多分配了一些值给它，从而导致自增序列出现空隙。而traditional模式因为每一次只会为一条记录分配自增值，所以不会有这种问题。
另外，对于Bulk inserts语句，依然会采取AUTO-INC锁。所以，如果有一条Bulk inserts语句正在执行的话，Simple inserts也必须等到该语句执行完毕才能继续执行。

2：交错模式（interleaved）

在这种模式下，对于所有的insert-like语句，都不会存在表级别的AUTO-INC锁，意味着同一张表上的多个语句并发时阻塞会大幅减少，这时的效率最高。但是会引入一个新的问题：当binlog_format为statement时，这时的复制没法保证安全，因为批量的insert，比如insert ..select..语句在这个情况下，也可以立马获取到一大批的自增ID值，不必锁整个表，slave在回放这个SQL时必然会产生错乱（binlog使用row格式没有这个问题）。

其他

自增值的生成后是不能回滚的，所以自增值生成后，事务回滚了，那么那些已经生成的自增值就丢失了，从而使自增列的数据出现空隙。
正常情况下，自增列是不存在0这个值的。所以，如果插入语句中对自增列设置的值为0或者null，就会自动应用自增序列。那么，如果想在自增列中插入为0这个值，怎么办呢？可以通过将SQL Mode设置为NO_AUTO_VALUE_ON_ZERO即可。
在MySQL 5.7以及更早之前，自增序列的计数器(auto-increment counter)是保存在内存中的。auto-increment counter在每次MySQL重新启动后通过类似下面的这种语句进行初始化：
```
  SELECT MAX(AUTO_INC_COLUMN) FROM table_name FOR UPDATE
```
而从MySQL 8开始，auto-increment counter被存储在了redo log中，并且每次变化都会刷新到redo log中。另外，我们可以通过ALTER TABLE … AUTO_INCREMENT = N来主动修改auto-increment counter。

生产环境相关配置

我们生产环境配置是innodb_autoinc_lock_mode = 2，binlog_format = ROW

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
1 row in set (0.07 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.36 sec)

三、Insert 流程源码分析

3.1 Insert 执行过程

mysql_parse -> mysql_execute_command -> Sql_cmd_insert::execute -> Sql_cmd_insert::mysql_insert -> write_record -> handler::ha_write_row -> ha_innobase::write_row

这里我们主要关注innodb层的数据写入函数ha_innobase::write_row 相关的代码就好了，生成自增ID和唯一索引冲突检查都是在这个函数里面完成的。

3.2 innodb 数据插入流程

通过ha_innobase::write_row 代码我们可以知道，在innodb层写入数据主要分为7步：

1. Validation checks before we commence write_row operation.
2. Intermediate commit if original operation involves ALTER table with algorithm = copy. Intermediate commit ease pressure on recovery if server crashes while ALTER is active.
3. Handling of Auto-Increment Columns.
4. Prepare INSERT graph that will be executed for actual INSERT (This is a one time operation)
5. Execute insert graph that will result in actual insert.
6. Handling of errors related to auto-increment. 
7. Cleanup and exit.

我们主要关注，自增列相关的步骤三和步骤六，数据写入的步骤五。

自增ID的相关处理过程

先看第三步代码：Handling of Auto-Increment Columns，主要的函数栈如下：

->ha_innobase::write_row
    ->handler::update_auto_increment // 调用 update_auto_increment 函数更新auto increment的值
        ->ha_innobase::get_auto_increment // 获取 dict_tabel中的当前 auto increment 值，并根据全局参数更新下一个 auto increment 的值到数据字典中
            ->ha_innobase::innobase_get_autoinc // 读取 autoinc 值
                ->ha_innobase::innobase_lock_autoinc
                   ->dict_table_autoinc_lock(m_prebuilt->table); // lock_mode = 2 的时候
                ->dict_table_autoinc_unlock(m_prebuilt->table); // 解锁
        ->set_next_insert_id // 多行插入的时候设置下一个插入的id值

三种模式对应的加锁源码：

static const long AUTOINC_OLD_STYLE_LOCKING = 0;
static const long AUTOINC_NEW_STYLE_LOCKING = 1;
static const long AUTOINC_NO_LOCKING = 2;


dberr_t
ha_innobase::innobase_lock_autoinc(void)
/*====================================*/
{
    DBUG_ENTER("ha_innobase::innobase_lock_autoinc");
    dberr_t        error = DB_SUCCESS;
    long        lock_mode = innobase_autoinc_lock_mode;

    ut_ad(!srv_read_only_mode
          || dict_table_is_intrinsic(m_prebuilt->table));

    if (dict_table_is_intrinsic(m_prebuilt->table)) {
        /* Intrinsic table are not shared accorss connection
        so there is no need to AUTOINC lock the table. */
        lock_mode = AUTOINC_NO_LOCKING;
    }

    switch (lock_mode) {
    case AUTOINC_NO_LOCKING: // lock_mode = 2
        /* Acquire only the AUTOINC mutex. */
        dict_table_autoinc_lock(m_prebuilt->table);
        break;

    case AUTOINC_NEW_STYLE_LOCKING:
        /* For simple (single/multi) row INSERTs, we fallback to the
        old style only if another transaction has already acquired
        the AUTOINC lock on behalf of a LOAD FILE or INSERT ... SELECT
        etc. type of statement. */
        if (thd_sql_command(m_user_thd) == SQLCOM_INSERT
            || thd_sql_command(m_user_thd) == SQLCOM_REPLACE) {

            dict_table_t*    ib_table = m_prebuilt->table;

            /* Acquire the AUTOINC mutex. */
            dict_table_autoinc_lock(ib_table);

            /* We need to check that another transaction isn't
            already holding the AUTOINC lock on the table. */
            if (ib_table->n_waiting_or_granted_auto_inc_locks) {
                /* Release the mutex to avoid deadlocks. */
                dict_table_autoinc_unlock(ib_table);
            } else {
                break;
            }
        }
        /* Fall through to old style locking. */

    case AUTOINC_OLD_STYLE_LOCKING:
        DBUG_EXECUTE_IF("die_if_autoinc_old_lock_style_used",
                ut_ad(0););
        error = row_lock_table_autoinc_for_mysql(m_prebuilt);

        if (error == DB_SUCCESS) {

            /* Acquire the AUTOINC mutex. */
            dict_table_autoinc_lock(m_prebuilt->table);
        }
        break;

    default:
        ut_error;
    }

    DBUG_RETURN(error);
}

步骤六：插入成功以后，还需要更新 autoinc 值

            if (auto_inc >= m_prebuilt->autoinc_last_value) {
set_max_autoinc:
                /* This should filter out the negative
                values set explicitly by the user. */
                if (auto_inc <= col_max_value) {
                    ut_a(m_prebuilt->autoinc_increment > 0);

                    ulonglong    offset;
                    ulonglong    increment;
                    dberr_t        err;

                    offset = m_prebuilt->autoinc_offset;
                    increment = m_prebuilt->autoinc_increment;

                    auto_inc = innobase_next_autoinc(
                        auto_inc,
                        1, increment, offset,
                        col_max_value);

                    err = innobase_set_max_autoinc(
                        auto_inc);

                    if (err != DB_SUCCESS) {
                        error = err;
                    }
                }
            }

唯一索引冲突检查过程

  |-Sql_cmd_insert_values::execute_inner() // Insert one or more rows from a VALUES list into a table
    |-write_record
      |-handler::ha_write_row() // 调用存储引擎的接口
        |-ha_innobase::write_row()
          |-row_insert_for_mysql
            |-row_insert_for_mysql_using_ins_graph
              |-trx_start_if_not_started_xa
                |-trx_start_low // 激活事务，事务状态由 not_active 变为 active
              |-row_get_prebuilt_insert_row // Gets pointer to a prebuilt dtuple used in insertions
              |-row_mysql_convert_row_to_innobase // 记录格式从MySQL转换成InnoDB, 不同数据类型处理方式不同，比如整形server端是小端存储，innodb是大端存储
              |-row_ins_step
                |-trx_write_trx_id(node->trx_id_buf, trx->id)
                |-lock_table // 给表加IX锁
                |-row_ins // 插入记录
                  |-while (node->index != NULL)
                    |-row_ins_index_entry_step // 向索引中插入记录,把 innobase format field 的值赋给对应的index entry field
                      |-row_ins_index_entry_set_vals // 根据该索引以及原记录，将组成索引的列的值组成一个记录
                      |-dtuple_check_typed // 检查组成的记录的有效性
                      |-row_ins_index_entry // 插入索引项
                        |-row_ins_clust_index_entry // 插入聚集索引
                          |-row_ins_clust_index_entry_low // 先尝试乐观插入，修改叶子节点 BTR_MODIFY_LEAF
                            |-mtr_t::mtr_t()
                            |-mtr_t::start()
                            |-btr_pcur_t::open()
                              |-btr_cur_search_to_nth_level // 将cursor移动到索引上待插入的位置
                                |-buf_page_get_gen //取得本层页面，首次为根页面
                                |-page_cur_search_with_match_bytes // 在本层页面进行游标定位
                            |-row_ins_duplicate_error_in_clust // 判断插入项是否存在唯一键冲突
                              |-row_ins_set_shared_rec_lock // 对cursor 对应的已有记录加S锁（可能会等待）保证记录上的操作，包括：Insert/Update/Delete 已经提交或者回滚
                                |-lock_clust_rec_read_check_and_lock // 判断cursor对应的记录上是否存在隐式锁, 若存在，则将隐式锁转化为显示锁
                                  |-lock_rec_convert_impl_to_expl // 隐式锁转换
                                  |-lock_rec_lock //如果上面的隐式锁转化成功，此处加S锁将会等待，直到活跃事务释放锁。
                              |-row_ins_dupl_err_with_rec // S锁加锁完成之后，可以再次做判断，最终决定是否存在唯一键冲突, 
                                // 1. 判断insert记录与cursor对应的记录取值是否相同, 
                                // 2. 二级唯一键值锁引，可以存在多个 NULL 值, 
                                // 3. 最后判断记录的delete flag状态，判断记录是否被删除提交
                                |-return !rec_get_deleted_flag();
                            |-btr_cur_optimistic_insert // 乐观插入
                            |-btr_cur_pessimistic_insert // 乐观插入失败则进行悲观插入
                            |-mtr_t::commit() mtr_commit //Commit a mini-transaction.
                            |-btr_pcur_t::close()

四、总结

innodb_autoinc_lock_mode=2的时候，MySQL是申请到ID以后就会释放锁。并发生成自增ID不会冲突。
MySQL是先生成ID，再去做插入前的唯一索引冲突检查。如果一部分Client用MySQL自增ID，一部分Client用自己生成的ID，是有可能导致自增ID的Client报PK Duplicate Error的。

参考资料

AUTO_INCREMENT Handling in InnoDB

Mysql之AUTO_INCREMENT浅析

MySQL · 内核分析 · InnoDB主键约束和唯一约束的实现分析

Ryo's blog

MySQL 自增列 Duplicate Error 问题分析
点击返回顶部

首页

关于

归档

MySQL 自增列 Duplicate Error 问题分析