MySQL Forums
Forum List  »  InnoDB

Explain deadlock locking the same index
Posted by: Jan Bols
Date: October 29, 2009 08:53AM

Hi, can somebody explain this deadlock to me. It looks like both transactions are locking the same primary index, but how is this possible?

The db is an innodb db with replication. The EventStore table is partitioned. Both transactions are using repeatable-read.

*** (1) TRANSACTION:
TRANSACTION 0 11263, ACTIVE 0 sec, OS thread id 676 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 162, query id 97656 localhost 127.0.0.1 tmsng updating
delete from tmsng_norestore.EventStore where id=x'24E8030B02CD277E11AD49A2E9E69A1D97A8BDC0F9EE5D8B939944228D8EC1D81812DCAFFFE9A85A5E9307382063D7A47A2E0CCC4500C361B9C855C026548198' and (start_time, start_time_ms)=('2009-10-29 13:10:34', 742)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 194 page no 12 n bits 112 index `PRIMARY` of table `tmsng_norestore`.`#mysql50#eventstore#p#p99991231` trx id 0 11263 lock_mode X waiting
Record lock, heap no 26 PHYSICAL RECORD: n_fields 52; compact format; info bits 0
0: len 30; hex 24e8030b02cd277e11ad49a2e9e69a1d97a8bdc0f9ee5d8b939944228d8e; asc $ '~ I ] D" ;...(truncated); 1: len 8; hex 80001245cea78f1a; asc E ;; 2: len 4; hex 800002e6; asc ;; 3: len 6; hex 000000002b7d; asc +};; 4: len 7; hex 800000002d0110; asc - ;; 5: len 19; hex 547261666669634576656e744d657373616765; asc TrafficEventMessage;; 6: len 8; hex 80001245cea78f1a; asc E ;; 7: len 4; hex 800002e6; asc ;; 8: len 4; hex 8000000b; asc ;; 9: len 5; hex 43414d2035; asc CAM 5;; 10: len 8; hex 4445544543544f52; asc DETECTOR;; 11: len 5; hex 4556454e54; asc EVENT;; 12: len 9; hex 4f5645525350454544; asc OVERSPEED;; 13: len 7; hex 54524146464943; asc TRAFFIC;; 14: len 9; hex 53544154454c455353; asc STATELESS;; 15: len 8; hex 8000000000002449; asc $I;; 16: SQL NULL; 17: SQL NULL; 18: len 4; hex 80000020; asc ;; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 7; hex 44454641554c54; asc DEFAULT;; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: SQL NULL; 31: SQL NULL; 32: SQL NULL; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: len 4; hex 80000002; asc ;; 41: SQL NULL; 42: SQL NULL; 43: SQL NULL; 44: SQL NULL; 45: SQL NULL; 46: SQL NULL; 47: SQL NULL; 48: len 8; hex 80001245cea78fc7; asc E ;; 49: len 4; hex 80000112; asc ;; 50: len 8; hex 8000000000000406; asc ;; 51: SQL NULL;



*** (2) TRANSACTION:
TRANSACTION 0 11261, ACTIVE 2 sec, OS thread id 4764 fetching rows, thread declared inside InnoDB 465
mysql tables in use 3, locked 3
11 lock struct(s), heap size 1024, 286 row lock(s)
MySQL thread id 120, query id 97636 localhost 127.0.0.1 tmsng Sending data
delete from tmsng_norestore.EventStore where message_source_type='DETECTOR' and message_source_id in (select message_source_id from (select distinct message_source_id from tmsng_norestore.EventStore where message_source_type='DETECTOR') a left join Detector b on a.message_source_id=b.id where b.id is null)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 194 page no 12 n bits 112 index `PRIMARY` of table `tmsng_norestore`.`#mysql50#eventstore#p#p99991231` trx id 0 11261 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 194 page no 12 n bits 112 index `PRIMARY` of table `tmsng_norestore`.`#mysql50#eventstore#p#p99991231` trx id 0 11261 lock_mode X waiting
Record lock, heap no 26 PHYSICAL RECORD: n_fields 52; compact format; info bits 0
0: len 30; hex 24e8030b02cd277e11ad49a2e9e69a1d97a8bdc0f9ee5d8b939944228d8e; asc $ '~ I ] D" ;...(truncated); 1: len 8; hex 80001245cea78f1a; asc E ;; 2: len 4; hex 800002e6; asc ;; 3: len 6; hex 000000002b7d; asc +};; 4: len 7; hex 800000002d0110; asc - ;; 5: len 19; hex 547261666669634576656e744d657373616765; asc TrafficEventMessage;; 6: len 8; hex 80001245cea78f1a; asc E ;; 7: len 4; hex 800002e6; asc ;; 8: len 4; hex 8000000b; asc ;; 9: len 5; hex 43414d2035; asc CAM 5;; 10: len 8; hex 4445544543544f52; asc DETECTOR;; 11: len 5; hex 4556454e54; asc EVENT;; 12: len 9; hex 4f5645525350454544; asc OVERSPEED;; 13: len 7; hex 54524146464943; asc TRAFFIC;; 14: len 9; hex 53544154454c455353; asc STATELESS;; 15: len 8; hex 8000000000002449; asc $I;; 16: SQL NULL; 17: SQL NULL; 18: len 4; hex 80000020; asc ;; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 7; hex 44454641554c54; asc DEFAULT;; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: SQL NULL; 31: SQL NULL; 32: SQL NULL; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: len 4; hex 80000002; asc ;; 41: SQL NULL; 42: SQL NULL; 43: SQL NULL; 44: SQL NULL; 45: SQL NULL; 46: SQL NULL; 47: SQL NULL; 48: len 8; hex 80001245cea78fc7; asc E ;; 49: len 4; hex 80000112; asc ;; 50: len 8; hex 8000000000000406; asc ;; 51: SQL NULL;

Best regards
Jan

Options: ReplyQuote


Subject
Views
Written By
Posted
Explain deadlock locking the same index
3208
October 29, 2009 08:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.