#4159 [HHQ-4025] MySQL deadlock while inserting in HQ_METRIC_DATA_

[Hyperic Team]

http://jira.hyperic.com/browse/HHQ-4025

LATEST DETECTED DEADLOCK

---

091031 6:10:16


*** (1) TRANSACTION:


TRANSACTION 0 3769732330, ACTIVE 1 sec, process no 5485, OS thread id 1181620544 inserting


mysql tables in use 1, locked 1


LOCK WAIT 3 lock struct(s), heap size 1216, undo log entries 690


MySQL thread id 117094, query id 28135289 10.0.0.122 hqadmin update


insert into HQ_METRIC_DATA_5D_0S (timestamp, measurement_id, value) values (1256994540000, 649013, 100.00000),(1256994540000, 648974, 100.00000),(1256994540000, 648961, 100.00000),(1256994540000, 648965, 23.36000),(1256994540000, 648984, 66.23000),(1256994540000, 648987, 100.00000),(1256994540000, 648991, 17.80000),(1256994300000, 649696, 23.39000),(1256994540000, 649071, 15.77000),(1256994540000, 649066, 100.00000),(1256994300000, 649626, 100.00000),(1256994540000, 649065, 100.00000),(1256994300000, 649622, 32.98000),(1256994540000, 649058, 15.77000),(1256994540000, 649056, 21.91000),(1256994300000, 649613, 100.00000),(1256994300000, 649612, 100.00000),(1256994300000, 649609, 32.98000),(1256994540000, 649079, 100.00000),(1256994540000, 649078, 100.00000),(1256994540000, 649075, 37.04000),(1256994300000, 649603, 10.36000),(1256994300000, 649662, 57.02000),(1256994540000, 649039, 100.00000),(1256994300000, 649661, 30.23000),(1256994300000, 649655, 21.10000),(1256994540000, 649030, 16.56000),(1256994540000, 649024, 80.97000),(1256994540000, 649026, 100.00000),(1256994540000, 649027, 100.00000),(1256994540000, 649052, 100.00000),(1256994300000, 649644, 24.94000),(1256994300000, 649642, 17.80000),(1256994540000, 649045, 10.36000),(1256994300000, 649636, 19.59000),(1256994540000, 649040, 100.00000),(1256994540000, 649043, 23.39000),(1256994300000, 649121, 10.36000),(1256994300000, 649558, 59.52000),(1256994300000, 649560, 100.00000),(1256994300000, 649564, 23.39000),(1256994300000, 649538, 16.56000),(1256994300000, 649140, 12060497.87000),(1256994300000, 649545, 19.59000),(1256994300000, 649548, 100.00000),(1256994300000, 649587, 100.00000),(1256994300000, 649586, 100.00000),(1256994300000, 649088, 80.97000),(1256994300000, 649584, 18.18000),(1256994300000, 649095, 23.36000),(1256994300000, 649592, 15.77000),(1256994300000, 649101, 29.64000),(1256994300000, 649597, 59.52000),(1256994300000, 649104, 100.00000),(1256994300000, 649105, 100.00000),(1256994300000, 649571, 68.31000),(1256994300000, 649108, 21.91000),(1256994300000, 649579, 24.94000),(1256994300000, 649114, 53.10000),(1256994300000, 649117, 100.00000),(1256994300000, 649583, 68.31000),(1256994300000, 649493, 59.52000),(1256994300000, 649488, 17.80000),(1256994300000, 649496, 100.00000),(1256994300000, 649499, 24.94000),(1256994300000, 649473, 24.94000),(1256994300000, 649480, 29.64000),(1256994300000, 649483, 100.00000),(1256994300000, 649525, 17.80000),(1256994300000, 649158, 14872078.08000),(1256994300000, 649535, 100.00000),(1256994300000, 649534, 100.00000),(1256994300000, 649531, 66.23000),(1256994300000, 649509, 100.00000),(1256994300000, 649505, 29.64000),(1256994300000, 649518, 18.18000),(1256994300000, 649514, 21.10000),(1256994300000, 649417, 0.04740),(1256994300000, 649420, 0.03720),(1256994300000, 649414, 0.94140),(1256994300000, 649427, 0.82780),(1256994300000, 649449, 0.00860),(1256994300000, 649443, 0.03720),(1256994300000, 649466, 59.20000),(1256994300000, 649467, 19.59000),(


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 0 page no 4145642 n bits 360 index PRIMARY of table hqdb/HQ_METRIC_DATA_5D_0S trx id 0 3769732330 lock_mode X locks gap before rec insert intention waiting


Record lock, heap no 18 PHYSICAL RECORD: n_fields 5; compact format; info bits 0


 0: len 8; hex 80000124aaae4c80; asc $ L ;; 1: len 4; hex 80085cbb; asc \ ;; 2: len 6; hex 0000e0b18487; asc ;; 3: len 7; hex 80001400022178; asc !x;; 4: len 12; hex 80000000000000003400e290; asc 4 ;;

*** (2) TRANSACTION:


TRANSACTION 0 3769732302, ACTIVE 16 sec, process no 5485, OS thread id 1185347904 fetching rows, thread declared inside InnoDB 105


mysql tables in use 2, locked 2


38086 lock struct(s), heap size 4470768


MySQL thread id 117092, query id 28135229 10.0.0.122 hqadmin Sending data


INSERT INTO EAM_MEASUREMENT_DATA_1H (measurement_id, timestamp, value, minvalue, maxvalue) (SELECT measurement_id, 1256990400000 AS timestamp, AVG(value), MIN(value), MAX(value) FROM (SELECT * FROM HQ_METRIC_DATA_5D_0S WHERE timestamp between 1256990400000 and 1256994000000) EAM_MEASUREMENT_DATA WHERE timestamp >= 1256990400000 AND timestamp < 1256994000000 GROUP BY measurement_id)


*** (2) HOLDS THE LOCK(S):


RECORD LOCKS space id 0 page no 4145642 n bits 360 index PRIMARY of table hqdb/HQ_METRIC_DATA_5D_0S trx id 0 3769732302 lock mode