问题描述

insert into 并发死锁 问题

同时向同一个数据库的同一张表插入数据，用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)
?

解决方案

sql server死锁的问题
insert into时注意的问题
ORA-01461: can bind a LONG value only for insert into a LONG column 问题解决方案

解决方案二：

并发读写是不允许的，在程序中要使用加锁的方式来避免资源竞争。
另外，你也可以使用mongodb等no-sql数据库，已经做了这些处理

解决方案三：

如果你是insert一批数据进入一个table，当然会有比较长时间的锁。要解决锁的问题，要控制好事务的大小，如果是并行写入的话，数据不要产生冲突，特别是索引关键字段，否则会有锁产生。