mysql 索引，事务锁的问题

上一条可能发的不太清楚所以导致没有回应我... 这次我重新整理了下，希望大家帮忙看看。

 

sql如下

 

索引信息如下：

 

 

 

死锁错误信息如下：

 

INSERT INTO MY_TABLE(Type, Name, Status) VALUES ('InnoDB', '', '
=====================================
2019-04-25 11:55:14 0x2208 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1039 srv_active, 0 srv_shutdown, 239294 srv_idle
srv_master_thread log flush and writes: 240333
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24040
OS WAIT ARRAY INFO: signal count 23313
RW-shared spins 0, rounds 2687, OS waits 1334
RW-excl spins 0, rounds 1221, OS waits 31
RW-sx spins 2, rounds 60, OS waits 2
Spin rounds per wait: 2687.00 RW-shared, 1221.00 RW-excl, 30.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-25 11:55:05 0x22f0
*** (1) TRANSACTION:
TRANSACTION 15065164, ACTIVE 19 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7192, OS thread handle 7424, query id 172258 MS-20181121HONT 192.168.1.176 root statistics
/* ApplicationName=DataGrip 2018.3.4 */ select *
    from t_bank_card b
    where account_id =''1053652'' and card_no=''6236682971000458441'' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4463 page no 4 n bits 88 index account_id_index of table `credit_center`.`t_bank_card` trx id 15065164 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 7; hex 31303533363532; asc 1053652;;
 1: len 19; hex 36323336363832393731303030343538343431; asc 6236682971000458441;;
 2: len 30; hex 313661326531306363306432613664376163303532653434643938393132; asc 16a2e10cc0d2a6d7ac052e44d98912; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 15065163, ACTIVE 26 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 7191, OS thread handle 8944, query id 172322 MS-20181121HONT 192.168.1.176 root updating
/* ApplicationName=DataGrip 2018.3.4 */ update  t_bank_card set status=''init'',update_time=now() where account_id = ''1053652'' and status = ''invalid''
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4463 page no 4 n bits 88 index account_id_index of table `credit_center`.`t_bank_card` trx id 15065163 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 7; hex 31303533363532; asc 1053652;;
 1: len 19; hex 36323336363832393731303030343538343431; asc 6236682971000458441;;
 2: len 30; hex 313661326531306363306432613664376163303532653434643938393132; asc 16a2e10cc0d2a6d7ac052e44d98912; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4463 page no 4 n bits 88 index account_id_index of table `credit_center`.`t_bank_card` trx id 15065163 lock_mode X waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 7; hex 31303533363532; asc 1053652;;
 1: len 19; hex 36323336363832393731303030343538343431; asc 6236682971000458441;;
 2: len 30; hex 313661326531306363306432613664376163303532653434643938393132; asc 16a2e10cc0d2a6d7ac052e44d98912; (total 32 bytes);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS