mybatis-mysql-自增id获取坑

mybatis
LAST_INSERT_ID, mybatis, mysql, spring, 坑

坑场景描述

实际场景可能复杂，简单还原场景

环境

  • mybatis 3.2.8
  • tddl 3.1.0.4
  • mysql 5.6.16-log
  • spring 3.1.2.RELEASE

表

user
字段名称 | 类型
—|—
id | int(10) auto_increment
name | varchar(50)

score
字段名称 | 类型
—|—
id | int(10) auto_increment
user_id | int(10)
score | int(3)

mybatis配置

user.mapper 非相关 省略

1
2
3
4
5
6
7
8
<insert id="insert" parameterType="User" >
  	<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
	SELECT LAST_INSERT_ID() AS id
	</selectKey>
    insert into user (id, name)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=CHAR}
      )
  </insert>

score.mapper 非相关 省略

1
2
3
4
5
6
7
8
9
<insert id="insert" parameterType="User" >
  	<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
	SELECT LAST_INSERT_ID() AS id
	</selectKey>
    insert into score (id, user_id,score)
    values (#{id,jdbcType=INTEGER}, #{userId,jdbcType=INTEGER},
    #{score,jdbcType=INTEGER}
      )
  </insert>

需求场景

插入user后需要插入sore需要获取到user_id,非同一事物中

存在的问题

在线上出现插入user后获取到id不为当前插入后的id而为score表的id(系统存在并发情况可能存在多个插入同时操作)

问题定位

LAST_INSERT_ID()定义

mysql5.6官方文档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 LAST_INSERT_ID()， LAST_INSERT_ID(expr)
没有参数， LAST_INSERT_ID()返回一个64位值，表示AUTO_INCREMENT由于最近执行的INSERT 语句而导致为列成功插入的第一个自动生成的值 。在此之前，该值具有BIGINT UNSIGNEDMySQL 5.6.9 类型BIGINT（签名）。LAST_INSERT_ID()如果没有成功插入行，则该值 保持不变。
使用一个参数， LAST_INSERT_ID()返回MySQL 5.6.9之前的无符号整数，一个有符号的整数。
例如，在插入生成AUTO_INCREMENT值的行之后 ，可以得到如下值：
mysql> SELECT LAST_INSERT_ID();
        -> 195
当前执行的语句不影响其值 LAST_INSERT_ID()。假设您AUTO_INCREMENT使用一个语句生成值，然后LAST_INSERT_ID()在多行INSERT语句中引用 ，该行将行插入到具有其自身AUTO_INCREMENT列的表中 。LAST_INSERT_ID()第二个声明中的价值 将保持稳定; 其第二行和更后一行的值不受早期行插入的影响。（但是，如果混合引用 LAST_INSERT_ID()和 效果未定义。） LAST_INSERT_ID(expr)
如果以前的语句返回错误，则值为 LAST_INSERT_ID()undefined。对于事务表，如果语句由于错误而回滚，则该值将 LAST_INSERT_ID()保持未定义。对于手动 ROLLBACK，LAST_INSERT_ID() 交易前的值不会恢复; 它仍然是在它的位置 ROLLBACK。
在MySQL 5.6.15之前，如果使用复制过滤规则，则此函数未正确复制。（Bug＃17234370，Bug＃69861）
在存储的例程（过程或函数）或触发器LAST_INSERT_ID()的正文内，更改的值与 在这些对象的体外执行的语句相同。存储的例程或触发器对其值的影响 LAST_INSERT_ID()由以下语句所看到取决于例程的种类：
如果存储过程执行更改值的语句，则更改的值LAST_INSERT_ID()将由过程调用后面的语句看到。
对于存储的功能和更改值的触发器，当函数或触发器结束时，该值将被恢复，因此以下语句将不会看到更改的值。
生成的ID在每个连接的基础上维护在服务器中 。这意味着函数返回给给定客户端的AUTO_INCREMENT值是为该客户端影响AUTO_INCREMENT列的最新语句生成的第一个 值 。即使这些AUTO_INCREMENT值生成了自己的值，也不会受到其他客户端的影响 。此行为确保每个客户端可以检索自己的ID，而不用担心其他客户端的活动，并且不需要锁或事务。

其中

1
生成的ID在每个连接的基础上维护在服务器中 。这意味着函数返回给给定客户端的AUTO_INCREMENT值是为该客户端影响AUTO_INCREMENT列的最新语句生成的第一个 值 。即使这些AUTO_INCREMENT值生成了自己的值，也不会受到其他客户端的影响 。此行为确保每个客户端可以检索自己的ID，而不用担心其他客户端的活动，并且不需要锁或事务

sprig-mybatis

一个insert执行流程

1
SqlSessionTemplate.insert-->DefaultSqlSession.insert-->SimpleExecutor.update-->PreparedStatementHandler.update

1
2
其中insert into user 先执行，其后执行 select last_inset id
(1.ps.execute() 2.keyGenerator.processAfter)

image
image

通过debug可以看到通过SpringManagedTransaction.getConnection两次open一次直接返回一次可以确定两次操作为同一Connection(非并发环境)

同时模拟insrt后通过mysql工具连接插入数据后返回id正确

对比

1
生成的ID在每个连接的基础上维护在服务器中 。这意味着函数返回给给定客户端的AUTO_INCREMENT值是为该客户端影响AUTO_INCREMENT列的最新语句生成的第一个 值 。即使这些AUTO_INCREMENT值生成了自己的值，也不会受到其他客户端的影响 。此行为确保每个客户端可以检索自己的ID，而不用担心其他客户端的活动，并且不需要锁或事务

以及

通过debug可以看到通过SpringManagedTransaction.getConnection两次open一次直接返回一次可以确定两次操作为同一Connection

结论

无（对比仅供参考）

解决方案

替换last_inset id 通过插入后再查询name获取id