由于业务原因,要进行大量新增或修改数据,创建唯一索引使用ON DUPLICATE KEY UPDATE来修改
<insert id="saveOrUpdate" parameterType="domain"
useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO `t_domain`(`name`, `create_by`, `create_time`,
`status`, `deleted`)
VALUE (#{name}, #{createBy}, #{createTime}, #{status}, #{deleted})
ON DUPLICATE KEY UPDATE
`name` = values(`name`),
`update_by` = values(`create_by`),
`update_time` = values(`create_time`),
`status` = values(`status`),
`deleted` = values(`deleted`)
</insert>
需要先新增父级表获得ID再存入子表,但是数据在多线程时返回的key为null,放出部分SQL代码
2020-12-21 14:59:14.802 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain`(`name`, `create_by`, `create_time`,
`status`, `deleted`
2020-12-21 14:59:14.803 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Parameters: name(String), 1(Integer), 1608533954(Long), 0(Integer)
2020-12-21 14:59:14.847 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : <== Updates: 2
2020-12-21 14:59:14.849 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain`(`name`, `create_by`, `create_time`,
`status`, `deleted`
2020-12-21 14:59:14.851 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Parameters: name(String), 1(Integer), 1608533954(Long), 0(Integer)
2020-12-21 14:59:14.880 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : <== Updates: 1
2020-12-21 14:59:14.881 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain_info`(`comm_id`, `name`
2020-12-21 14:59:14.882 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Parameters: 73(Integer), (String)
2020-12-21 14:59:14.937 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : <== Updates: 1
2020-12-21 14:59:14.938 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain_info`(`comm_id`, `name`
2020-12-21 14:59:14.939 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Parameters: null, (String)
这里有两个线程同时执行,但是由于测试数据获取数据一致,但是有一个线程([pool-2-thread-2])获取的主键为空。因为进行过测试就算是主键冲突所以变成修改也会返回ID
然后注意到两条线程新增主表时的Updates不同,同时数据两条线程新增主表数据都是一致,包括时间戳,由于是秒级别所以也是一致,先执行的返回了主键,后执行的为空
所以猜测第二条线程([pool-2-thread-2])是由于主键冲突变成修改但是数据一致并没有修改成功所以返回的主键为空
测试换成毫秒级别的时间戳导致两个线程数据不一致没有发生该问题
2020-12-21 14:59:14.802 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain`(`name`, `create_by`, `create_time`,
2020-12-21 14:59:14.803 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Parameters: name(String), 1(Integer), 1608536834924(Long), 0(Integer)
2020-12-21 14:59:14.847 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : <== Updates: 2
2020-12-21 14:59:14.849 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain`(`name`, `create_by`, `create_time`,
2020-12-21 14:59:14.851 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Parameters: name(String), 1(Integer), 1608536834984(Long), 0(Integer)
2020-12-21 14:59:14.880 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : <== Updates: 1
2020-12-21 14:59:14.881 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain_info`(`comm_id`, `name`
2020-12-21 14:59:14.882 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : ==> Parameters: 73(Integer), (String)
2020-12-21 14:59:14.937 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : <== Updates: 1
2020-12-21 14:59:14.938 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Preparing: INSERT INTO `t_domain_info`(`comm_id`, `name`
2020-12-21 14:59:14.939 DEBUG 13000 --- [pool-2-thread-2] c.m.d.m.C.saveOrUpdate : ==> Parameters: 73(Integer), (String)
2020-12-21 14:59:14.939 DEBUG 13000 --- [pool-2-thread-1] c.m.d.m.C.saveOrUpdate : <== Updates: 1