mysql事务，索引产生死锁的问题

请求并发访问这个接口

 

实现类如下

 

 

第一条sql：select <include refid="Base_Column_List" />

from t_bank_card b

where account_id = #{accountId,jdbcType=VARCHAR} and card_no=#{cardNo,jdbcType=VARCHAR} for update

 

 

 

第二条sql：<update id="updateBankCardStatus" parameterType="com.credit.account.model.BankCard" >

  update t_bank_card

  <set>

    <if test="status != null" >

      status = #{status,jdbcType=VARCHAR},

    </if>

    <if test="updateTime != null" >

      update_time = #{updateTime,jdbcType=TIMESTAMP},

    </if>

    <if test="protocolNo != null">

      protocol_no = #{protocolNo,jdbcType=VARCHAR}

    </if>

  </set>

  where account_id = #{accountId,jdbcType=VARCHAR} and status = #{updateStatus,jdbcType=VARCHAR}

</update>

 

 

组合索引

 

 

当第一个线程执行完select for update，第二个线程还在等待执行select for update， 第一个线程执行完update之后，第二个线程还没有执行完select for update就报错了：

 

org.springframework.dao.DeadlockLoserDataAccessException:

### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Unknown error 1213

### The error may exist in file [D:\new\credit-service\target\classes\mapper\account\BankCardMapper.xml]

### The error may involve com.credit.account.dao.BankCardMapper.selectByAccountIdAndCardNo-Inline

### The error occurred while setting parameters

### SQL: select       b.id, b.account_id,b.card_no, b.status, b.bank_code, b.yborderid,b.create_time,b.card_top,b.card_last,b.update_time,b.bank_name,b.protocol_no         from t_bank_card b     where account_id = ? and card_no=? for update

### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Unknown error 1213

; Unknown error 1213; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Unknown error 1213