记录一次脑残 Bug

记录一次脑残 Bug

java 伪代码

@Transactional(rollbackFor = Throwable.class)
public CallResult<? extends ServiceError, AccountMemberDTO> transfer(TransferTradeCmd cmd) {
	
	// 第一次查 account_member 表
	Preparing: select * from account_member where member_id = ? and acc_type = ?
	Parameters: 20248(String), 10(Integer)
	
	Row: 1557928740894986241, 20220811061557928740781740034, 5f4dcc3b5aa765d61d8327deb882cf99, 20248, 美2, 00000000, 10, 0, 0, 0, 0, null, 0, , 23, 2022-08-12 11:15:31, hp, 2022-08-12 23:41:53, 13891795035

   	// 第二次查 account_member 表
	Preparing: select * from account_member where member_id = ? and acc_type = ?
	Parameters: 0(String), 1(Integer)
	Row: 1557928740894986241, 20220811061557928740781740034, 5f4dcc3b5aa765d61d8327deb882cf99, 0, 公司公户, 公司电话, 1, 90130, 44230, 0, 45900, null, 0, , 23, 2022-08-12 11:15:31, hp, 2022-08-12 23:41:53, 13891795035

	Preparing: UPDATE account_member SET acc_code=?, trade_pwd=?, member_id=?, member_real_name=?, member_phone=?, acc_type=?, income=?, expenditure=?, locked_balance=?, balance=?, locked=?, ext=?, version=?, created_time=?, updated_time=?, creator=?, updater=? WHERE id=? AND version=?
	Parameters: 20220811061557928740781740034(String), 5f4dcc3b5aa765d61d8327deb882cf99(String), 0(String), 公司公户(String), 公司电话(String), 1(Integer), 90130(Long), 44230(Long), 0(Long), 45900(Long), false(Boolean), (String), 24(Integer), 2022-08-12 11:15:31.0(Timestamp), 2022-08-12 23:44:25.864(Timestamp), hp(String), 13891795035(String), 1557928740894986241(Long), 23(Integer)
	Updates: 2

	Preparing: INSERT INTO account_trade_details ( id, trade_serial_no, member_id, member_real_name, acc_id, acc_code, biz_id, `from`, `to`, trade_type, sub_trade_type, trade_money, balance, ext, created_time, updated_time, creator, updater ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
	Parameters: 1558117210196267010(Long), 20220823621558117210196267009(String), 0(String), 公司公户(String), 1557928740894986241(Long), 20220811061557928740781740034(String), S2022081221000344(String), 1557928740894986241(String), 1557928740894986241(String), 20(Integer), 30(Integer), 0(Long), 45900(Long), 给医生分账(String), 2022-08-12 23:44:25.985(Timestamp), 2022-08-12 23:44:25.985(Timestamp), 13891795035(String), 13891795035(String)
	Updates: 1
	
	// 第三次查 account_member 表使用和第一次查询时同样的参数
	Preparing: select * from account_member where member_id = ? and acc_type = ?
	Parameters: 20248(String), 10(Integer)
	Total: 0

}

从上面的伪代码 sql 执行结果上来看，整个执行过程可以确定是执行在同一个事务当中，但是当第三次去查 account_member 表，并且使用的是和第一次查询同样的参数时出现了查不到行的结果。这一点很奇怪，经过排查后发现是因为建表的时候忘记将 id 列指定为 primary key ， 并且表中存在这样的两条数据。

id	acc_code	trade_pwd	member_id	member_real_name	member_phone	acc_type	income	expenditure	locked_balance	balance	last_cash_out_time	locked	ext	version	created_time	creator	updated_time	updater

1557928740894986241	20220811061557928740781740034	5f4dcc3b5aa765d61d8327deb882cf99	0	公司公户	公司电话	1	90130	44230	0	45900		0		23	2022-08-12 11:15:31	hp	2022-08-12 23:41:53	13891795035

1557928740894986241	20220811061557928740781740034	5f4dcc3b5aa765d61d8327deb882cf99	20248	美2	00000000	10	0	0	0	0		0		23	2022-08-12 11:15:31	hp	2022-08-12 23:41:53	13891795035

---

用 sql 去在一个事务中模拟 :

SET AUTOCOMMIT = 0;
begin ;


select * from jbh_account_member where member_id = '20248' and acc_type = 10;


select * from jbh_account_member where member_id = '0' and acc_type = 1;

UPDATE jbh_account_member SET acc_code='20220811061557928740781740034', trade_pwd='5f4dcc3b5aa765d61d8327deb882cf99', member_id='0', member_real_name='公司公户', member_phone='公司电话', acc_type=1, income=90130, expenditure=44230, locked_balance=0, balance=45900, locked=false, ext='', version=24, created_time='2022-08-12 11:15:31', updated_time='2022-08-12 23:44:25.864', creator='hp', updater='13891795035' WHERE id=1557928740894986241 AND version=23;

INSERT INTO jbh_account_trade_details ( id, trade_serial_no, member_id, member_real_name, acc_id, acc_code, biz_id, `from`, `to`, trade_type, sub_trade_type, trade_money, balance, ext, created_time, updated_time, creator, updater ) VALUES ( 1558117210196267010, '20220823621558117210196267009', '0', '公司公户', 1557928740894986241, '20220811061557928740781740034', '20220811061557928740781740034', '1557928740894986241', '1557928740894986241', 20, 30, 0, 45900, '给医生分账', '2022-08-12 23:44:25', '2022-08-12 23:44:25', '13891795035', '13891795035' ) ;

select * from jbh_account_member where member_id = '20248' and acc_type = 10;

-- commit ;

-- rollback ;SET AUTOCOMMIT = 0;
begin ;


select * from jbh_account_member where member_id = '20248' and acc_type = 10;


select * from jbh_account_member where member_id = '0' and acc_type = 1;

UPDATE jbh_account_member SET acc_code='20220811061557928740781740034', trade_pwd='5f4dcc3b5aa765d61d8327deb882cf99', member_id='0', member_real_name='公司公户', member_phone='公司电话', acc_type=1, income=90130, expenditure=44230, locked_balance=0, balance=45900, locked=false, ext='', version=24, created_time='2022-08-12 11:15:31', updated_time='2022-08-12 23:44:25.864', creator='hp', updater='13891795035' WHERE id=1557928740894986241 AND version=23;

INSERT INTO jbh_account_trade_details ( id, trade_serial_no, member_id, member_real_name, acc_id, acc_code, biz_id, `from`, `to`, trade_type, sub_trade_type, trade_money, balance, ext, created_time, updated_time, creator, updater ) VALUES ( 1558117210196267010, '20220823621558117210196267009', '0', '公司公户', 1557928740894986241, '20220811061557928740781740034', '20220811061557928740781740034', '1557928740894986241', '1557928740894986241', 20, 30, 0, 45900, '给医生分账', '2022-08-12 23:44:25', '2022-08-12 23:44:25', '13891795035', '13891795035' ) ;

select * from jbh_account_member where member_id = '20248' and acc_type = 10;

-- commit ;

-- rollback ;

因为两条记录 id 和 version 相同，所以在 update 时已经将 member_id , acc_type 两列更新为 '0' 和 10 , 所以第三次查询使用第一次查询时的参数是不可能查到记录的，因为记录在同一个事务中已经被更改了。