死锁信息如下,product_id和date都是非主键索引,id是主键索引,报的死锁好像是主键索引那思索了,但是怎么跑都重现不了,求大虾分析
*** (1) TRANSACTION:
TRANSACTION 0 36430242, ACTIVE 0 sec, process no 4500, OS thread id 140672746501888 fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 2687 lock struct(s), heap size 325616, 6543 row lock(s), undo log entries 1
MySQL thread id 1416072, query id 221746938 localhost ped preparing
update price set rate1 = 328,rate2 = 328,rate3 = 403,rate4 = 498 where product_id = 1472 and date <= '2014-02-07' and date >= '2014-02-07' and date not in (select date from store where product_id = 1472 and index = 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5240 page no 8531 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430242 lock_mode X locks rec but not gap waiting
Record lock, heap no 127 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e8b27; asc ';; 1: len 6; hex 0000022ac2a2; asc * ;; 2: len 7; hex 000000010305b0; asc ;; 3: len 4; hex 800005c0; asc ;; 4: len 3; hex 8fbc8e; asc ;; 5: len 6; hex 323031345f34; asc 2014_4;; 6: len 6; hex 800000013500; asc 5 ;; 7: len 6; hex 800000013500; asc 5 ;; 8: len 6; hex 800000018000; asc ;; 9: len 6; hex 80000001ea00; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** (2) TRANSACTION:
TRANSACTION 0 36430239, ACTIVE 0 sec, process no 4500, OS thread id 140674000840448 fetching rows, thread declared inside InnoDB 441
mysql tables in use 4, locked 4
2144 lock struct(s), heap size 260080, 6387 row lock(s)
MySQL thread id 1416071, query id 221746940 localhost ped Sending data
update price set rate1 = 419,rate2 = 419 where product_id = 1963 and date <= '2014-04-14' and date >= '2014-04-14' and date not in (select date from store where product_id = 1963 and index = 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5240 page no 8531 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430239 lock_mode X locks rec but not gap
Record lock, heap no 127 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e8b27; asc ';; 1: len 6; hex 0000022ac2a2; asc * ;; 2: len 7; hex 000000010305b0; asc ;; 3: len 4; hex 800005c0; asc ;; 4: len 3; hex 8fbc8e; asc ;; 5: len 6; hex 323031345f34; asc 2014_4;; 6: len 6; hex 800000013500; asc 5 ;; 7: len 6; hex 800000013500; asc 5 ;; 8: len 6; hex 800000018000; asc ;; 9: len 6; hex 80000001ea00; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5240 page no 8130 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430239 lock_mode X locks rec but not gap waiting
Record lock, heap no 94 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e02e0; asc ;; 1: len 6; hex 0000022be169; asc + i;; 2: len 7; hex 000000002d1f9d; asc - ;; 3: len 4; hex 800007ab; asc ;; 4: len 3; hex 8fbc47; asc G;; 5: len 6; hex 323031345f32; asc 2014_2;; 6: len 6; hex 800000018f00; asc ;; 7: len 6; hex 800000018f00; asc ;; 8: SQL NULL; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** WE ROLL BACK TRANSACTION (2)
*** (1) TRANSACTION:
TRANSACTION 0 36430242, ACTIVE 0 sec, process no 4500, OS thread id 140672746501888 fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 2687 lock struct(s), heap size 325616, 6543 row lock(s), undo log entries 1
MySQL thread id 1416072, query id 221746938 localhost ped preparing
update price set rate1 = 328,rate2 = 328,rate3 = 403,rate4 = 498 where product_id = 1472 and date <= '2014-02-07' and date >= '2014-02-07' and date not in (select date from store where product_id = 1472 and index = 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5240 page no 8531 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430242 lock_mode X locks rec but not gap waiting
Record lock, heap no 127 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e8b27; asc ';; 1: len 6; hex 0000022ac2a2; asc * ;; 2: len 7; hex 000000010305b0; asc ;; 3: len 4; hex 800005c0; asc ;; 4: len 3; hex 8fbc8e; asc ;; 5: len 6; hex 323031345f34; asc 2014_4;; 6: len 6; hex 800000013500; asc 5 ;; 7: len 6; hex 800000013500; asc 5 ;; 8: len 6; hex 800000018000; asc ;; 9: len 6; hex 80000001ea00; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** (2) TRANSACTION:
TRANSACTION 0 36430239, ACTIVE 0 sec, process no 4500, OS thread id 140674000840448 fetching rows, thread declared inside InnoDB 441
mysql tables in use 4, locked 4
2144 lock struct(s), heap size 260080, 6387 row lock(s)
MySQL thread id 1416071, query id 221746940 localhost ped Sending data
update price set rate1 = 419,rate2 = 419 where product_id = 1963 and date <= '2014-04-14' and date >= '2014-04-14' and date not in (select date from store where product_id = 1963 and index = 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5240 page no 8531 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430239 lock_mode X locks rec but not gap
Record lock, heap no 127 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e8b27; asc ';; 1: len 6; hex 0000022ac2a2; asc * ;; 2: len 7; hex 000000010305b0; asc ;; 3: len 4; hex 800005c0; asc ;; 4: len 3; hex 8fbc8e; asc ;; 5: len 6; hex 323031345f34; asc 2014_4;; 6: len 6; hex 800000013500; asc 5 ;; 7: len 6; hex 800000013500; asc 5 ;; 8: len 6; hex 800000018000; asc ;; 9: len 6; hex 80000001ea00; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5240 page no 8130 n bits 304 index `PRIMARY` of table `ped2013`.`price` trx id 0 36430239 lock_mode X locks rec but not gap waiting
Record lock, heap no 94 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
0: len 4; hex 800e02e0; asc ;; 1: len 6; hex 0000022be169; asc + i;; 2: len 7; hex 000000002d1f9d; asc - ;; 3: len 4; hex 800007ab; asc ;; 4: len 3; hex 8fbc47; asc G;; 5: len 6; hex 323031345f32; asc 2014_2;; 6: len 6; hex 800000018f00; asc ;; 7: len 6; hex 800000018f00; asc ;; 8: SQL NULL; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 1; hex 81; asc ;;*** WE ROLL BACK TRANSACTION (2)
update price set rate1 = 328,rate2 = 328,rate3 = 403,rate4 = 498 where product_id = 1472 and date <= '2014-02-07' and date >= '2014-02-07' and date not in (select date from store where product_id = 1472 and index = 1)update price a
left join store b on a.date=b.date
set a.rate1 = 328,a.rate2 = 328,a.rate3 = 403,a.rate4 = 498 where a.product_id = 1472 and a.date <= '2014-02-07' and a.date >= '2014-02-07' and
b.product_id = 1472 and b.index = 1 and b.date is null
嗯,有考虑过,但是因为数据比较多可能有些语句还是会执行比较久,而且并发性也很高,最主要是不能重现,改了不能测试效果