mysql 锁问题

Question

update更新索引导致的死锁。从上面的信息看出是update数据的时候更新索引导致索引有锁，在insert的时候导致了死锁。

update 和 insert 并非一条记录。 而且update更新也只是更新了一条记录。为什么会导致死锁呢 ， 麻烦大神们给解惑下 、 谢谢。

TRANSACTION 9154315, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 18818668, OS thread handle 0x7fb08a083700, query id 192707369 172.17.140.195 ums updating
UPDATE t_account_asset SET c_account = '286379', n_asset_type = 5 WHERE c_asset_identify = '78646a6144344b373732303800000d63'
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
Record lock, heap no 89 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 373836343661363134343334346233373337333233303338303030303064; asc 78646a6144344b373732303800000d; (total 32 bytes);
1: len 8; hex 80000000000271ac; asc q ;;

* (2) TRANSACTION:
TRANSACTION 9153441, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
1193 lock struct(s), heap size 194088, 33519 row lock(s), undo log entries 13391
MySQL thread id 19200577, OS thread handle 0x7fb089aed700, query id 192716299 172.17.140.73 ums update
INSERT INTO t_account_asset(c_account, c_asset_identify, c_device_name, n_asset_type, c_card_no, n_time)
VALUES('263561','78646a6144344b373732303800000d03','Phone-01',6,'78646a6144344b373732303800000d03',1452304800298)
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 106 page no 1679 n bits 240 index index_asset_identify of table ums.t_account_asset trx id 9153441 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 373836343661363134343334346233373337333233303338303030303036; asc 78646a6144344b3737323038000006; (total 32 bytes);
1: len 8; hex 8000000000027181; asc q ;;

表结构：
| t_account_asset | CREATE TABLE t_account_asset (
n_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT
c_account varchar(32) NOT NULL COMMENT ' 账户',
c_asset_identify varchar(64) NOT NULL COMMENT
c_device_name varchar(64) DEFAULT NULL COMMENT
n_asset_type int(4) NOT NULL COMMENT
c_card_no varchar(64) NOT NULL COMMENT
n_time bigint(20) NOT NULL COMMENT
PRIMARY KEY (n_id),
UNIQUE KEY index_asset_identify (c_asset_identify),
UNIQUE KEY index_card_no (c_card_no),
KEY index_account (c_account) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=164876 DEFAULT CHARSET=utf8

Answer 1

你发出来的信息是不是少了东西？例如事务1中WAITING FOR THIS LOCK TO BE GRANTED:下面少了东西？（应该有说明事务1等待什么锁的）。另外事务2中只描述了HOLDS THE LOCK(S)，没有显示WAITING FOR THIS LOCK TO BE GRANTED？

http://www.aneasystone.com/ar...
这里有很详细的加锁分析（因为我也没有理解透，所以就分享链接好了）