多线程开启事务insert导致mysql死锁

主键自增,唯一索引

表结构:## 多线程开启事务insert导致mysql死锁
CREATE TABLE account (
account_id bigint NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
user_id bigint NOT NULL DEFAULT ‘0’ COMMENT ‘用户Id’,
balance varchar(255) NOT NULL DEFAULT ‘0.00’ COMMENT ‘余额’,
status tinyint NOT NULL DEFAULT ‘1’ COMMENT ‘状态’,
created datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’ COMMENT ‘创建时间’,
modified datetime NOT NULL DEFAULT ‘1970-01-01 08:00:00’ COMMENT ‘修改时间’,
PRIMARY KEY (red_packet_account_id),
UNIQUE KEY uniq_userId (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT=‘余额表’;

T1:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

ROLLBACK;
1
T2:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

COMMIT;
T3:
begin;
insert into account (user_id, balance,status )values( ‘21222’, 12, 1);

COMMIT;

执行顺序:T1 开启事务,执行insert;
T2开启事务,执行insert;
T3开启事务,执行insert;
T1 ROLLBACK 回滚,此时,T2插入成功,T3报死锁错误;
T2,T3执行COMMIT。
查看死锁日志:show engine innodb status;
LATEST DETECTED DEADLOCK
2020-07-31 14:31:52 0x4c14
*** (1) TRANSACTION:
TRANSACTION 4223, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 27912, query id 492 localhost ::1 root update
insert into red_packet_account (user_id, balance,status )values( ‘21222’, 12, 1)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4223 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4223 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 4224, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 32, OS thread handle 9240, query id 498 localhost ::1 root update
insert into red_packet_account (user_id, balance,status )values( ‘21222’, 12, 1)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4224 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uniq_userId of table rcp-test.account trx id 4224 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
总结:
T1先加入X锁,T2,T3等待。T1回滚了,T2,T3都加入意向插入锁S,判断这条数据库里没有,就准备升级S到X。因为S,X互斥。此时T2,T3各自持有的S锁都在等待对方释放升级成X锁。

同时向同一个数据库的同一张表插入数据,用insert into 或者 load data infile 都会出现 锁索引的情况,造成死锁,哪些大神能够帮忙解决下。大大有赏。在线等,急! 下面是死锁的具体信息: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 160503 14:58:26 *** (1) TRANSACTION: TRANSACTION FC644, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 81 lock struct(s), heap size 14776, 165 row lock(s), undo log entries 104 MySQL thread id 8686, OS thread handle 0xd1c, query id 2055688 116.226.215.243 wangmeng update INSERT INTO `mayi-bg`.pes_trade_2016_5(shop_id, date, seller_nick, payment, post_fee, consign_time, num, status, total_fee, created, pay_time, modified, end_time, buyer_nick, trade_id,seller_flag) VALUES(58583652,'2016-05-01','chengxuepin',69.00,0.00,'0000-00-00 00:00:00',1,'TRADE_CLOSED_BY_TAOBAO',89.00,'2016-05-01 16:07:22','0000-00-00 00:00:00','2016-05-02 06:38:44','2016-05-02 06:38:45','丑女芸儿',1856014565898906,0) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2653 page no 2512 n bits 584 index `index_trade_id` of table `mayi-bg`.`pes_trade_2016_5` trx id FC644 lock mode S waiting Record lock, heap no 472 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 8006994b75944172; asc Ku Ar;; 1: len 8; hex 0000000000019a85; asc ;; *** (2) TRANSACTION: TRANSACTION FC63F, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 182 lock struct(s), heap size 31160, 1932 row lock(s), undo log entries 2121 MySQL thread id 8687, OS thread handle 0x470, query id 2055706 116.226.215.243 wangmeng update INSERT INTO `mayi-bg`.pes_trade_2016_5(shop_id, date, seller_nick, payment, post_fee, consign_time, num, status, total_fee, created, pay_time, modified, end_time, buyer_nick, trade_id,seller_flag) VALUES(68285317,'2016-05-02','联新办公专营店',17.00,0.00,'0000-00-00 00:00:00',1,'TRADE_CLOSED_BY_TAOBAO',17.00,'2016-05-02 00:54:24','0000-00-00 00:00:00','2016-05-02 00:55:39','2016-05-02 00:55:05','润0827',1857253469651417,0) ​
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页