2 u010523218 u010523218 于 2016.05.03 15:24 提问

insert into 并发死锁 问题 2C

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

3个回答

CSDNXIAON
CSDNXIAON   2016.05.03 15:32

sql server死锁的问题
insert into时注意的问题
ORA-01461: can bind a LONG value only for insert into a LONG column 问题解决方案
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

NK_test
NK_test   Rxr 2016.05.04 15:04

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

sxwws
sxwws   2016.05.04 15:50

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!