@Transactional注解导致nextval失效的问题排查记录

今天组内一个新同事使用批量插入时出现了主键冲突的问题，经过一番排查，发现是@Transactional使用不当，导致nextval(sequence)取到的都是同一个值，序列值不会自增。特此记录下排查过程。

现象

@Transactional
public String applicationQZPC(List<String> khbhList, String userId) {
    ...
    qzpcApplicationMapper.batchInsertQzpcApplication(applicationlist);
    ...
}

<insert id="batchInsertQzpcApplication" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="sqbh">  
        INSERT INTO  mfis.trzrq_qzpc_application_management (sqbh, fzjg, khbh,khxm,zt,jzdzggp,jzdzggpmc,npcyy,npcje,fqswbb,fqszzc,fqszfz,sqqpr,sqqpsj,sqqpsjrq,zjzh,jjx,pcx)  
        VALUES  
        <foreach collection="applicationList" item="item" separator=",">  
            ('PC' || to_char(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('mfis.trzrq_qzpc_application_id_seq')::text, 5, '0'), #{item.fzjg},  
             #{item.khbh}, #{item.khxm}, 0, #{item.jzdzggp},#{item.jzdzggpmc}, #{item.npcyy}, #{item.npcje}, #{item.fqswbb}, #{item.fqszzc}, #{item.fqszfz},  
             #{item.sqqpr}, CURRENT_DATE, to_char(CURRENT_DATE,'yyyyMMdd')::INTEGER,#{item.zjzh},#{item.jjx},#{item.pcx})  
        </foreach>  
    </insert>

在执行batchInsertQzpcApplication()方法时，会报主键冲突：

Cause: org.postgresql.util.PSQLException: ERROR: node:dn001, backend_pid:2570602, nodename,backend_pid,message:dn001,2570602,duplicate key value violates unique constraint "trzrq_qzpc_application_management_pkey"  
  详细：Key (sqbh)=(PC2024062600542) already exists.

分析

先将sqbh的设置单独抽出来，观察每条记录的sqbh值

for (QzpcApplicationEntity application : applicationlist){
    String sqbh = qzpcApplicationMapper.selectSqbh();
    application.setSqbh(sqbh);
}

<select id="selectSqbh" resultType="String">
    select  'PC' || to_char(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('mfis.trzrq_qzpc_application_id_seq')::text, 5, '0') as sqbh
</select>

debug发现，所有sqbh的值都是一样的，说明'mfis.trzrq_qzpc_application_id_seq'的序列值没有自增。

猜测1

sequence有缓存，查看'mfis.trzrq_qzpc_application_id_seq'定义：

CREATE SEQUENCE mfis.trzrq_qzpc_application_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 99999
    START 1
   CACHE 1
   CYCLE;

ai建议：

1. PostgreSQL中的SEQUENCE是一种用于生成连续整数的对象。当使用SEQUENCE作为主键时，可能会导致主键冲突的问题。 当使用SEQUENCE作为主键时，如果多个事务同时尝试使用相同的SEQUENCE值作为主键，可能会导致主键冲突。这种情况下，后提交的事务可能会覆盖先提交的事务的数据，从而导致数据不一致。
2. 为了避免这个问题，可以尝试以下方法：

• 调整SEQUENCE的缓存大小：可以通过设置seq_cache_size参数来调整SEQUENCE的缓存大小。例如，可以将缓存大小设置为0，这样SEQUENCE就不会使用缓存。（这个涉及全局，pass）
• 调整事务的隔离级别：可以通过调整事务的隔离级别来避免主键冲突。例如，可以将事务的隔离级别设置为READ COMMITTED，这样后提交的事务就不会读取到先提交的事务尚未提交的数据。（这个涉及全局，pass）
• 使用RETURNING子句：在插入数据时，可以使用RETURNING子句来获取生成的唯一主键值。例如： Copy Insert INSERT INTO my_table (name) VALUES ('John') RETURNING id; 这个查询将返回一个唯一的id值，可以将其用作后续操作的主键。 （这个改动比较大，pass）
• 调整SEQUENCE的cache值：请注意，如果缓存大小设置过大，可能会导致内存不足的问题。如果缓存大小设置过小，可能会导致生成连续整数时出现重复值。因此，在调整缓存大小时，需要权衡性能和准确性之间的折衷。 总之，为了避免SEQUENCE缓存导致的主键冲突，可以尝试调整SEQUENCE的缓存大小、调整事务的隔离级别或使用RETURNING子句来获取唯一的值。

实操：将cache改成100，结果序列值仍然未更新。

猜测2

既然跟SEQUENCE的缓存无关，可能是事务的问题，于是问一下ai：

在Java中，当你使用JDBC来调用数据库（如Oracle、PostgreSQL等）中的序列并获取其下一个值（通常通过nextval('sequence_name')），并且你发现序列的值没有增加，可能有以下几个原因：

1. 事务未提交：如果你在数据库事务中调用了nextval，但在获取值后没有提交事务，那么其他事务可能看不到这个新值。确保在调用nextval后提交了事务。
2. 缓存：某些数据库驱动或连接池可能缓存了序列的值。这通常不是默认行为，但如果你使用了特定的配置或库，可能会发生这种情况。确保你使用的库或驱动没有这样的行为。
3. 代码逻辑问题：检查你的代码逻辑，确保你确实在每次需要新值时都调用了nextval。也许你的代码在某种条件下没有执行nextval调用。
4. 并发问题：如果你的应用程序是多线程的，并且多个线程同时尝试获取序列的下一个值，但使用了相同的数据库连接，那么可能会出现并发问题。确保每个线程都有自己的数据库连接或正确地同步对共享资源的访问。
5. 数据库问题：最后，但并非最不可能的是，数据库本身可能有问题。尝试在数据库管理工具（如SQL*Plus、psql等）中直接运行nextval查询，看看是否得到预期的结果。
6. 序列配置：在某些数据库中，你可以配置序列的缓存行为。如果序列被配置为缓存值，并且数据库在崩溃后没有正确重置缓存，那么可能会出现值跳跃或缺失的情况。确保你的序列配置符合你的需求。
7. 回滚：如果你在获取nextval后进行了某些操作并回滚了事务，那么序列的值可能会增加，但因为你回滚了事务，所以这些值在逻辑上并没有被使用。这可能会导致序列的值看起来比实际使用的值大。
8. 连接问题：如果你的应用程序与数据库的连接有问题（例如，连接被意外关闭或重置），那么之前的nextval调用可能没有被正确执行或记录。确保你的数据库连接是稳定和可靠的。

排查下代码，发现方法使用了@Transactional注解，可能是因为在方法内循环调用nextval时，事务未提交，所以sequence的值不会更新。 实操：暂时去掉@Transactional注解，结果显示批量插入成功。

总结

@Transactional注解的方法内循环调用nextval，如果事务未提交，sequence的值不会更新，因此，要么将事务范围缩小，将【循环调用nextval】放在事务之外，要么取消批量插入，采取单条插入。