对Oracle数据库表加行锁控制并发时重复交易

最近遇到一个比较棘手的问题，交易时出现重复交易，并且这个问题是偶尔才出现，公司的产品主要是针对餐饮行业的CRM管理系统，类似于开卡，做消费奖励活动等 ，一天的交易量大，商户有几百家，门店数千个，至于为什么为出现重复交易，虽然在程序里面已经控制了是否重复提交的限制（也就是根据transId去查是否已经存在），但是仍然会出现重复交易的现象。在追究为什么在有重复提交限制还出现这种问题上，答案很模糊，连技术总监也直言，重复交易的原因很不确定，可能由于网络原因造成多次发出请求，操作失误等（比如多次点击鼠标）等 。

???? 程序中判断是否是重复提交的代码：

交易时间相同，或者是只相差几秒，bizId，posId相同。
?
? 我处理的方式就是加行锁，本来在这里判断是否有重复提交，是查交易表，以posId和bizId为条件，本来考虑是将trans_record的某个记录加锁，但是后来发现有一个问题，如果是一笔新交易，那么在交易表中是不存在的，那么这一条记录就锁不住，加锁了也是没用的。所以我考虑了业务需求，找了影响最小的一个表，也就是挂账交易账户表，并且只锁这个用户。在判断重复交易前加行锁，然后处理后面的业务，等处理完业务后，再释放锁。并且，要考虑处理业务的阶段，如果任何一个地方出了错，就得抛出异常，这个时候需要rollback。
@Transactional(readOnly = false, propagation = Propagation.REQUIRED)public Map<String, Object> creditConsume(Map<String, String> parameter) {String posId = parameter.get(ApiConstants.PARAM_POS_ID);String posPwd = parameter.get(ApiConstants.PARAM_POS_PWD);String storeId = parameter.get(ApiConstants.PARAM_STORE_ID);String cardNum = parameter.get(ApiConstants.PARAM_CARD_ID);String transMoney = parameter.get(ApiConstants.PARAM_TRANS_MONEY);String bizId = parameter.get(ApiConstants.PARAM_BIZ_ID);String batchId = parameter.get(ApiConstants.PARAM_BATCH_ID);String transId=null;long totalMoney =0;Long creditLimit = null;Long creditBalance = null;Pos pos = apiAuthenticate.posCheck(posId, posPwd);apiAuthenticate.isPosAvailable(posId);Store store = apiAuthenticate.storeCheck(pos, storeId);String merchantId = store.getMerchantId();Card card = apiAuthenticate.cardCheck(cardNum, store, false);String customerId = card.getCustomerId();String cardId = card.getId();//加锁【锁住MEMBER_CREDIT_ACCOUNT，因为挂账消费，要修改挂账用户表，这里根据merchantId,customerId两个条件可以锁住这一条】        Connection con = null;        Statement statement = null;        try {con = this.getSqlMapClient().getDataSource().getConnection();con.setAutoCommit(false);statement = con.createStatement();statement.execute("select customer_id from MEMBER_CREDIT_ACCOUNT where merchant_id='"+merchantId+"' and customer_id='"+customerId+"' for update");} catch (SQLException e) {e.printStackTrace();}try {//判断是否为重复交易apiAuthenticate.checkRepeatTrans(bizId, posId);MerchantMember merchantMember = apiAuthenticate.memberCheck(customerId, card, store, false);//选择主卡帐户String masterCustomerId = null;String masterRecordId = null;boolean isTeamAccount = certification.isTeamAccount(cardId, storeId);if (isTeamAccount) {masterCustomerId = certification.getMasterCustomerId(customerId, merchantId);apiAuthenticate.memberCheck(masterCustomerId, card, store, false);masterRecordId = masterCustomerId;} else {masterCustomerId = customerId;}// 修改账户交易值totalMoney = RequestUtil.toSafeDigit(transMoney);creditService.consumeAccount(masterCustomerId, merchantId, storeId, totalMoney);// 增加交易记录    transId = StringUtils.generateTransId();operateRecord.insertTransRecord(customerId, masterRecordId, merchantId, storeId, transId,cardId, posId, TransConstants.TRANS_TYPE_CREDIT_CONSUME, null, GlobalConstants.TRANS_WAY_MANUAL, bizId, batchId,null,null);MemberCreditAccount account = creditService.findMemberCreditAccount(masterCustomerId, merchantId, storeId);operateRecord.addTransCreditRecord(transId, totalMoney, null, merchantMember.getStoreId(), storeId, merchantId, customerId, masterCustomerId, TransConstants.TRANS_TYPE_CREDIT_CONSUME, posId, cardId, null, "api-pos", GlobalConstants.TRANS_WAY_MANUAL, bizId, account.getBalance(), null);// 挂帐信息MemberCreditAccount creditAccount = creditService.findMemberCreditAccount(masterCustomerId, merchantId, storeId);if(null != creditAccount) {creditLimit = creditAccount.getCreditLimit();creditBalance = creditAccount.getBalance();}} catch (Exception e1) {// TODO: handle exceptione1.printStackTrace();if(con != null){try {con.rollback();con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(statement != null){try {statement.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}finally{  //假如判断到中间某些地方有异常，则回滚当前对数据库的操作。// 解锁try {if(con != null) {con.commit();con.close();}if(statement != null) {statement.close();}} catch (SQLException e) {e.printStackTrace();}}//返回结果Map<String, Object> result = new HashMap<String, Object>(); result.put(ApiConstants.RETURN_STATUS, PosErrors.SUCCESS);result.put(ApiConstants.RETURN_CARD_ID, cardId);result.put(ApiConstants.RETURN_TRANS_ID, transId);result.put(ApiConstants.RETURN_TRANS_MONEY, totalMoney);result.put(ApiConstants.RETURN_CREDIT_LIMIT, creditLimit);result.put(ApiConstants.RETURN_CREDIT_BALANCE, creditBalance);//apiOperationLog.addLog(ApiConstants.CREDITCONSUME, "卡号"+cardId, ApiConstants.API, posId, storeId, merchantId);return result;}
?
?
? 
    1 楼    hxai11    2011-12-21              <script>alert("跨站")</script>    2 楼    virusfu    2011-12-31              恩 ，很好