icon
Update time
Jul 21, 2022 02:59 PM
Internal status
password
背景
线上存在数据表主键重复的错误
通过阿里云日志搜索以下关键词可以查询到
"Duplicate" and "primary"
日志例子
log:2021-04-22 13:19:31,896 ERROR [http-nio2-8088-exec-100] [ac140db2-knscphtx-437669] [steam-trade-boot] c.x.s.t.s.b.i.TradeOrderLogServiceImpl - 插入日志冲突 org.springframework.dao.DuplicateKeyException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '859225024379092992' for key 'PRIMARY' ### The error may exist in com/xingchao/steam/trade/dal/mapper/TradeOrderLogMapper.java (best guess) ### The error may involve com.xingchao.steam.trade.dal.mapper.TradeOrderLogMapper.insertSelective-Inline ### The error occurred while setting parameters ### SQL: INSERT INTO trade_order_log ( id,order_asset_id,type,before_status,after_status,event,content,create_time,update_time ) VALUES( ?,?,?,?,?,?,?,?,? ) ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '859225024379092992' for key 'PRIMARY'
代码例子
//项目中所有主键使用的SnowFlakeUtil这个组件生成id logDO.setId(SnowFlakeUtil.getId()); tradeOrderLogMapper.insertSelective(logDO);
表现
针对这种频繁插入的情况,容易出现id重复的问题,与当初预想的不一样
分析
分布式id采用的twitter开源的snowflake算法,其本身是作为一个id服务集群运行的,其中关键的配置dataCenterId,workerId,需要在不同数据中心,不同的机器设置成不一样的
但是这种方案,一是需要一些额外的资源来运行此服务,我们不想耗费这个资源,二是需要远程调用,存在性能问题,于是采用的本机生成的方案
本地生成的方案关键在于如何设置这两个参数,我们看之前的实现
// 生成worker_id long workId; try { StringBuilder sbInternet = new StringBuilder(); Enumeration<NetworkInterface> enumInter = NetworkInterface.getNetworkInterfaces(); while (enumInter.hasMoreElements()) { sbInternet.append(enumInter.nextElement().toString()); } int machinePiece = sbInternet.toString().hashCode() << 16; workId = (long) (machinePiece % 32); } catch (Exception e) { // 如果获取失败，则使用随机数备用 workId = RandomUtils.nextLong(0, 31); } // 生成data_centerId int processPiece = ManagementFactory.getRuntimeMXBean().getName().hashCode() & 0xFFFF; long dataCenterId = (long) (processPiece % 32);
看代码意思是其中workId想用网卡信息来计算,datacenterId想用jvm管理器的对象来计算
bug1:
String str="wejwjeuwewewje"; int hashCode=str.hashCode(); //hashCode: -418012329 int xx=hashCode<<16; //xx:-1554579456 因为左移了16位,等于乘以2^16, int mod = xx % 32; //mod:0 //必然可以被32整除,余数永远为0
bug2:
StringBuilder sbInternet = new StringBuilder(); Enumeration<NetworkInterface> enumInter = NetworkInterface.getNetworkInterfaces(); while (enumInter.hasMoreElements()) { sbInternet.append(enumInter.nextElement().toString()); }
容器1 / # ifconfig eth0 Link encap:Ethernet HWaddr 02:42:AC:11:00:02 inet addr:172.17.0.2 Bcast:172.17.255.255 Mask:255.255.0.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:1348 errors:0 dropped:0 overruns:0 frame:0 TX packets:1686 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:218719 (213.5 KiB) TX bytes:589400 (575.5 KiB) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:0 errors:0 dropped:0 overruns:0 frame:0 TX packets:0 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:0 (0.0 B) TX bytes:0 (0.0 B) 容器2 /# ifconfig eth0 Link encap:Ethernet HWaddr 02:42:AC:11:00:03 inet addr:172.17.0.3 Bcast:172.17.255.255 Mask:255.255.0.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:776 errors:0 dropped:0 overruns:0 frame:0 TX packets:959 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:128968 (125.9 KiB) TX bytes:322602 (315.0 KiB) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:0 errors:0 dropped:0 overruns:0 frame:0 TX packets:0 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:0 (0.0 B) TX bytes:0 (0.0 B)
因为我们的应用在docker中运行,因此以上代码取到的网卡名永远一样,所以hashcode也永远一样,就算修复了第一个问题,取模的结果也一样
以上问题导致workId永远为0,造成重复的可能性大大加大
修改成如下代码
// 生成worker_id long workId; try { StringBuilder sbInternet = new StringBuilder(); Enumeration<NetworkInterface> enumInter = NetworkInterface.getNetworkInterfaces(); //改成取硬件地址,也就是mac,得到的字符串肯定不一样 while (enumInter.hasMoreElements()) { sbInternet.append(Arrays.toString(enumInter.nextElement().getHardwareAddress())); } //hashcode可能为负数,与运算去除符号 int machinePiece = sbInternet.toString().hashCode() & Integer.MAX_VALUE; workId = machinePiece % 32; } catch (Exception e) { // 如果获取失败，则使用随机数备用 workId = RandomUtils.nextLong(0, 31); }