记录一次脑残 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 , 所以第三次查询使用第一次查询时的参数是不可能查到记录的,因为记录在同一个事务中已经被更改了。