GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

deadlock mysql

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • deadlock mysql

    I run out of mysql connections during heavy load.

    Below is the innodb status

    mysql> show innodb status\G;
    *************************** 1. row ***************************
    Status:
    =====================================
    070125 21:30:39 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 54 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 2105275, signal count 2079336
    Mutex spin waits 356003404, rounds 430389184, OS waits 745071
    RW-shared spins 7349358, OS waits 656984; RW-excl spins 3829780, OS waits 123067
    ------------------------
    LATEST FOREIGN KEY ERROR
    ------------------------
    070125 21:27:30 Transaction:
    TRANSACTION 0 1038857271, ACTIVE 0 sec, process no 22616, OS thread id 1389808560 inserting, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    10 lock struct(s), heap size 1024, undo log entries 1
    MySQL thread id 2926054, query id 1219694394 192.168.3.20 casero update
    insert into MEDIACOLLECTION_ITEM (MEDIACOLLECTIONID, MEDIAITEMID) values (275237, 1364537)
    Foreign key constraint fails for table `GEMINI/MEDIACOLLECTION_ITEM`:
    ,
    CONSTRAINT `FK1B5F0BD0A32E98CC` FOREIGN KEY (`MEDIAITEMID`) REFERENCES `MEDIAITEM` (`MEDIAITEMID`)
    Trying to add in child table, in index `FK1B5F0BD0A32E98CC` tuple:
    DATA TUPLE: 2 fields;
    0: len 4; hex 8014d239; asc 9;; 1: len 4; hex 80043325; asc 3%;;

    But in parent table `GEMINI/MEDIAITEM`, in index `PRIMARY`,
    the closest match we can find is record:
    PHYSICAL RECORD: n_fields 37; compact format; info bits 0
    0: len 4; hex 8014d24a; asc J;; 1: len 6; hex 00003bb61711; asc ; ;; 2: len 7; hex 0000003e8c25dc; asc > % ;; 3: len 1; hex 4d; asc M;; 4: len 26; hex 33306231302d31306666366337323537642d67656d696e6930 31; asc 30b10-10ff6c7257d-gemini01;; 5: len 18; hex 6170706c69636174696f6e2f6d73776f7264; asc application/msword;; 6: len 17; hex 53656374696f6e2034202d2030332d3031; asc Section 4 - 03-01;; 7: len 8; hex 80001240efe8de60; asc @ `;; 8: len 8; hex 80001240efe8de60; asc @ `;; 9: SQL NULL; 10: SQL NULL; 11: len 30; hex 66696c653a2f2f2f73746f726167652f70726f64756374696f 6e2f737562 ; asc file:///storage/production/sub;...(truncated); 12: len 1; hex 44; asc D;; 13: len 21; hex 53656374696f6e2034202d2030332d30312e646f63; asc Section 4 - 03-01.doc;; 14: len 17; hex 313136383633373233343736382e646f63; asc 1168637234768.doc;; 15: len 0; hex ; asc ;; 16: len 1; hex 00; asc ;; 17: len 1; hex 00; asc ;; 18: len 1; hex 00; asc ;; 19: len 4; hex 80008200; asc ;; 20: len 1; hex 54; asc T;; 21: len 1; hex 53; asc S;; 22: len 4; hex 804ea079; asc N y;; 23: len 4; hex 800084d8; asc ;; 24: len 30; hex aced0005737200176a6176612e7574696c2e4c696e6b656448 6173684d61 ; asc sr java.util.LinkedHashMa;...(truncated); 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;

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    070125 21:27:29
    *** (1) TRANSACTION:
    TRANSACTION 0 1038857248, ACTIVE 0 sec, process no 22616, OS thread id 1334033328 inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 6 lock struct(s), heap size 1024
    MySQL thread id 2926081, query id 1219694112 192.168.3.20 casero update
    insert into MEDIACOLLECTION_ITEM (MEDIACOLLECTIONID, MEDIAITEMID) values (275237, 1363308)
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 294220 n bits 272 index `PRIMARY` of table `GEMINI/MEDIACOLLECTION` trx id 0 1038857248 lock mode S locks rec but not gap waiting
    Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
    0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb3ff; asc = ;; 2: len 7; hex 0000004b80339b; asc K 3 ;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;

    *** (2) TRANSACTION:
    TRANSACTION 0 1038857215, ACTIVE 0 sec, process no 22616, OS thread id 1162513328 starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 320, undo log entries 57
    MySQL thread id 2925879, query id 1219694181 192.168.3.20 casero Updating
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 20:52:56' where MEDIACOLLECTIONID=275238
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 294220 n bits 272 index `PRIMARY` of table `GEMINI/MEDIACOLLECTION` trx id 0 1038857215 lock_mode X locks rec but not gap
    Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
    0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb3ff; asc = ;; 2: len 7; hex 0000004b80339b; asc K 3 ;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 294220 n bits 272 index `PRIMARY` of table `GEMINI/MEDIACOLLECTION` trx id 0 1038857215 lock_mode X locks rec but not gap waiting
    Record lock, heap no 17 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
    0: len 4; hex 80043326; asc 3&;; 1: len 6; hex 00003debb418; asc = ;; 2: len 7; hex 0000004b801c5d; asc K ];; 3: len 7; hex 53686f65626f78; asc Shoebox;; 4: len 7; hex 53686f65626f78; asc Shoebox;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: len 8; hex 80001240efbac79f; asc @ ;; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;

    *** WE ROLL BACK TRANSACTION (1)
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 1038865717
    Purge done for trx's n < 0 1038862273 undo n < 0 1
    History list length 145
    Total number of lock structs in row lock hash table 8
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 22616, OS thread id 1184779184
    MySQL thread id 2926288, query id 1219787608 localhost root
    show innodb status
    ---TRANSACTION 0 1038865715, not started, process no 22616, OS thread id 1322253232
    MySQL thread id 2926337, query id 1219787595 192.168.3.41 casero
    ---TRANSACTION 0 1038865716, not started, process no 22616, OS thread id 1596824496
    MySQL thread id 2926333, query id 1219787607 192.168.3.40 casero
    ---TRANSACTION 0 1038865651, not started, process no 22616, OS thread id 1250347952
    MySQL thread id 2926326, query id 1219786733 192.168.3.41 casero
    ---TRANSACTION 0 1038865649, not started, process no 22616, OS thread id 1243409328
    MySQL thread id 2926310, query id 1219786686 192.168.3.40 casero
    ---TRANSACTION 0 0, not started, process no 22616, OS thread id 1290038192
    mysql tables in use 1, locked 1
    MySQL thread id 2926298, query id 1219769438 192.168.3.20 casero Table lock
    insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values ('Library', 'Library', '', 'date', 1, 0, 'D', 'I', 'T', 49570)
    ---TRANSACTION 0 1038864275, not started, process no 22616, OS thread id 1537239984
    mysql tables in use 1, locked 1
    MySQL thread id 2926297, query id 1219769456 192.168.3.20 casero Table lock
    insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values ('Library', 'Library', '', 'date', 1, 0, 'D', 'I', 'T', 49569)
    ---TRANSACTION 0 1038864165, not started, process no 22616, OS thread id 1267125168
    mysql tables in use 1, locked 1
    MySQL thread id 2926267, query id 1219769414 192.168.3.21 casero Table lock
    insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values ('Library', 'Library', '', 'date', 1, 0, 'D', 'I', 'T', 49569)
    ---TRANSACTION 0 1038864268, not started, process no 22616, OS thread id 1180965808
    mysql tables in use 1, locked 1
    MySQL thread id 2926263, query id 1219769431 192.168.3.20 casero Table lock
    insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values ('Library', 'Library', '', 'date', 1, 0, 'D', 'I', 'T', 49568)
    ---TRANSACTION 0 1038861362, not started, process no 22616, OS thread id 1326447536
    mysql tables in use 1, locked 1
    MySQL thread id 2926203, query id 1219769303 192.168.3.21 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 21:30:03' where MEDIACOLLECTIONID=266711
    ---TRANSACTION 0 1038862247, not started, process no 22616, OS thread id 1334033328
    mysql tables in use 1, locked 1
    MySQL thread id 2926081, query id 1219769551 192.168.3.20 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED=? where MEDIACOLLECTIONID=?
    ---TRANSACTION 0 1038857290, not started, process no 22616, OS thread id 1351412656
    mysql tables in use 1, locked 1
    MySQL thread id 2926058, query id 1219769434 192.168.3.20 casero Table lock
    insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values ('Library', 'Library', '', 'date', 1, 0, 'D', 'I', 'T', 49567)
    ---TRANSACTION 0 1038865713, not started, process no 22616, OS thread id 1250749360
    MySQL thread id 2926036, query id 1219787571 192.168.3.21 casero
    ---TRANSACTION 0 1038807031, not started, process no 22616, OS thread id 1176746928
    MySQL thread id 2733303, query id 1219784813 192.168.3.50 casero
    ---TRANSACTION 0 1038865290, not started, process no 22616, OS thread id 1185381296
    MySQL thread id 2733302, query id 1219782096 192.168.3.50 casero
    ---TRANSACTION 0 1038381180, not started, process no 22616, OS thread id 1288231856
    MySQL thread id 2393087, query id 1219787514 192.168.3.51 casero
    ---TRANSACTION 0 1038865321, not started, process no 22616, OS thread id 1515682736
    MySQL thread id 2393086, query id 1219782421 192.168.3.51 casero
    ---TRANSACTION 0 1038806720, not started, process no 22616, OS thread id 1257040816
    MySQL thread id 2129399, query id 1219787355 192.168.3.52 casero
    ---TRANSACTION 0 1038865283, not started, process no 22616, OS thread id 1258937264
    MySQL thread id 2129411, query id 1219782042 192.168.3.52 casero
    ---TRANSACTION 0 1038547335, not started, process no 22616, OS thread id 1341217712
    MySQL thread id 1977835, query id 1219787433 192.168.3.50 casero
    ---TRANSACTION 0 1038731303, not started, process no 22616, OS thread id 1239415728
    MySQL thread id 1977830, query id 1219787532 192.168.3.51 casero
    ---TRANSACTION 0 1038697607, not started, process no 22616, OS thread id 1522252720
    MySQL thread id 1977839, query id 1219787535 192.168.3.51 casero
    ---TRANSACTION 0 1038713191, not started, process no 22616, OS thread id 1271720880
    MySQL thread id 1977848, query id 1219787466 192.168.3.50 casero
    ---TRANSACTION 0 1038865365, not started, process no 22616, OS thread id 1577307056
    MySQL thread id 1977861, query id 1219782974 192.168.3.50 casero
    ---TRANSACTION 0 1038865327, not started, process no 22616, OS thread id 1534684080
    MySQL thread id 1977846, query id 1219782483 192.168.3.51 casero
    ---TRANSACTION 0 1038865705, not started, process no 22616, OS thread id 1514077104
    MySQL thread id 1977842, query id 1219787463 192.168.3.50 casero
    ---TRANSACTION 0 1038865626, not started, process no 22616, OS thread id 1457658800
    MySQL thread id 1977837, query id 1219786368 192.168.3.51 casero
    ---TRANSACTION 0 1038716303, not started, process no 22616, OS thread id 1516886960
    MySQL thread id 332, query id 1219775003 192.168.3.50 casero
    ---TRANSACTION 0 1038727519, not started, process no 22616, OS thread id 1462221744
    MySQL thread id 237, query id 1219782716 192.168.3.52 casero
    ---TRANSACTION 0 1038749044, not started, process no 22616, OS thread id 1491151792
    MySQL thread id 286, query id 1219785405 192.168.3.52 casero
    ---TRANSACTION 0 1038832667, not started, process no 22616, OS thread id 1459538864
    MySQL thread id 232, query id 1219787511 192.168.3.52 casero
    ---TRANSACTION 0 1038806431, not started, process no 22616, OS thread id 1513675696
    MySQL thread id 313, query id 1219772785 192.168.3.51 casero
    ---TRANSACTION 0 1038745479, not started, process no 22616, OS thread id 1465781168
    MySQL thread id 242, query id 1219761604 192.168.3.51 casero
    ---TRANSACTION 0 1038839293, not started, process no 22616, OS thread id 1468062640
    MySQL thread id 275, query id 1219778312 192.168.3.50 casero
    ---TRANSACTION 0 1038832435, not started, process no 22616, OS thread id 1571613616
    MySQL thread id 30, query id 1219787436 192.168.3.51 casero
    ---TRANSACTION 0 1038666195, not started, process no 22616, OS thread id 1495935920
    MySQL thread id 291, query id 1219782533 192.168.3.50 casero
    ---TRANSACTION 0 1038483004, not started, process no 22616, OS thread id 1482300336
    MySQL thread id 264, query id 1219787124 192.168.3.50 casero
    ---TRANSACTION 0 1038717320, not started, process no 22616, OS thread id 1484381104
    MySQL thread id 269, query id 1219783364 192.168.3.51 casero
    ---TRANSACTION 0 1038856344, not started, process no 22616, OS thread id 1494330288
    MySQL thread id 227, query id 1219781030 192.168.3.51 casero
    ---TRANSACTION 0 1038862736, not started, process no 22616, OS thread id 1539742640
    MySQL thread id 668, query id 1219752209 192.168.3.52 casero
    ---TRANSACTION 0 1038856075, not started, process no 22616, OS thread id 1489071024
    MySQL thread id 284, query id 1219785587 192.168.3.52 casero
    ---TRANSACTION 0 1038858150, not started, process no 22616, OS thread id 1481497520
    MySQL thread id 272, query id 1219787478 192.168.3.52 casero
    ---TRANSACTION 0 1038806259, not started, process no 22616, OS thread id 1455778736
    MySQL thread id 229, query id 1219787349 192.168.3.52 casero
    ---TRANSACTION 0 1038863542, not started, process no 22616, OS thread id 1568066480
    MySQL thread id 425, query id 1219760606 192.168.3.52 casero
    ---TRANSACTION 0 1038739922, not started, process no 22616, OS thread id 1494731696
    MySQL thread id 225, query id 1219787565 192.168.3.52 casero
    ---TRANSACTION 0 1038738836, not started, process no 22616, OS thread id 1456180144
    MySQL thread id 222, query id 1219787529 192.168.3.50 casero
    ---TRANSACTION 0 1038864252, not started, process no 22616, OS thread id 1528622000
    MySQL thread id 331, query id 1219769242 192.168.3.50 casero
    ---TRANSACTION 0 1038863389, not started, process no 22616, OS thread id 1513876400
    MySQL thread id 320, query id 1219758839 192.168.3.51 casero
    ---TRANSACTION 0 1038865182, not started, process no 22616, OS thread id 1495735216
    MySQL thread id 292, query id 1219780899 192.168.3.50 casero
    ---TRANSACTION 0 1038865559, not started, process no 22616, OS thread id 1495333808
    MySQL thread id 285, query id 1219785510 192.168.3.52 casero
    ---TRANSACTION 0 1038864605, not started, process no 22616, OS thread id 1491352496
    MySQL thread id 276, query id 1219773310 192.168.3.50 casero
    ---TRANSACTION 0 1038863650, not started, process no 22616, OS thread id 1482099632
    MySQL thread id 274, query id 1219761843 192.168.3.50 casero
    ---TRANSACTION 0 1038865701, not started, process no 22616, OS thread id 1490951088
    MySQL thread id 273, query id 1219787418 192.168.3.52 casero
    ---TRANSACTION 0 1038864327, not started, process no 22616, OS thread id 1482501040
    MySQL thread id 268, query id 1219769982 192.168.3.51 casero
    ---TRANSACTION 0 1038864651, not started, process no 22616, OS thread id 1467861936
    MySQL thread id 248, query id 1219773893 192.168.3.51 casero
    ---TRANSACTION 0 1038865542, not started, process no 22616, OS thread id 1459739568
    MySQL thread id 244, query id 1219785325 192.168.3.52 casero
    ---TRANSACTION 0 1038863038, not started, process no 22616, OS thread id 1457257392
    MySQL thread id 243, query id 1219755432 192.168.3.51 casero
    ---TRANSACTION 0 1038862041, not started, process no 22616, OS thread id 1599847344
    MySQL thread id 231, query id 1219743944 192.168.3.52 casero
    ---TRANSACTION 0 1038865652, not started, process no 22616, OS thread id 1494932400
    MySQL thread id 224, query id 1219786738 192.168.3.52 casero
    ---TRANSACTION 0 1038865171, not started, process no 22616, OS thread id 1600449456
    MySQL thread id 1, query id 1219780761 192.168.3.51 casero
    ---TRANSACTION 0 1038865105, not started, process no 22616, OS thread id 1600048048
    MySQL thread id 3, query id 1219779935 192.168.3.50 casero
    ---TRANSACTION 0 1038865630, ACTIVE 2 sec, process no 22616, OS thread id 1179761584
    5 lock struct(s), heap size 320, undo log entries 7
    MySQL thread id 2926294, query id 1219786706 192.168.3.40 casero
    Trx read view will not see trx with id >= 0 1038865631, sees < 0 1038857288
    ---TRANSACTION 0 1038864286, ACTIVE 36 sec, process no 22616, OS thread id 1316006832
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 320, undo log entries 1
    MySQL thread id 2924933, query id 1219769624 192.168.3.21 casero Table lock
    delete from MEDIACOLLECTION where MEDIACOLLECTIONID=?
    Trx read view will not see trx with id >= 0 1038864287, sees < 0 1038857288
    ---TRANSACTION 0 1038864278, ACTIVE 36 sec, process no 22616, OS thread id 1270918064
    mysql tables in use 1, locked 1
    MySQL thread id 2926193, query id 1219769461 192.168.3.20 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED=? where MEDIACOLLECTIONID=?
    Trx read view will not see trx with id >= 0 1038864279, sees < 0 1038857288
    ---TRANSACTION 0 1038864262, ACTIVE 36 sec, process no 22616, OS thread id 1389808560 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 2926054, query id 1219769295 192.168.3.20 casero Updating
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 21:33:47' where MEDIACOLLECTIONID=275237
    ------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 294220 n bits 272 index `PRIMARY` of table `GEMINI/MEDIACOLLECTION` trx id 0 1038864262 lock_mode X locks rec but not gap waiting
    Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
    0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb448; asc = H;; 2: len 7; hex 0000004b802760; asc K '`;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0aef70a; asc @ ;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;

    ------------------
    ---TRANSACTION 0 1038862284, ACTIVE 86 sec, process no 22616, OS thread id 1499696048
    mysql tables in use 1, locked 1
    MySQL thread id 2926227, query id 1219769319 192.168.3.21 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 21:30:29' where MEDIACOLLECTIONID=90114
    Trx read view will not see trx with id >= 0 1038862285, sees < 0 1038857288
    ---TRANSACTION 0 1038862276, ACTIVE 86 sec, process no 22616, OS thread id 1340214192
    mysql tables in use 1, locked 1
    MySQL thread id 2926059, query id 1219769312 192.168.3.21 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 21:30:28' where MEDIACOLLECTIONID=90114
    Trx read view will not see trx with id >= 0 1038862277, sees < 0 1038857288
    ---TRANSACTION 0 1038862258, ACTIVE 87 sec, process no 22616, OS thread id 1283054512
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 320, undo log entries 1
    MySQL thread id 2925235, query id 1219769310 192.168.3.21 casero Table lock
    delete from MEDIACOLLECTION where MEDIACOLLECTIONID=217252
    Trx read view will not see trx with id >= 0 1038862259, sees < 0 1038857288
    ---TRANSACTION 0 1038862251, ACTIVE 87 sec, process no 22616, OS thread id 1517288368
    mysql tables in use 1, locked 1
    MySQL thread id 2923805, query id 1219769306 192.168.3.20 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 21:34:39' where MEDIACOLLECTIONID=275237
    Trx read view will not see trx with id >= 0 1038862252, sees < 0 1038857288
    ---TRANSACTION 0 1038857288, ACTIVE 189 sec, process no 22616, OS thread id 1162513328
    mysql tables in use 1, locked 1
    2 lock struct(s), heap size 320, undo log entries 5
    MySQL thread id 2925879, query id 1219769328 192.168.3.20 casero Table lock
    update MEDIACOLLECTION set DATEUPDATED='2007-01-25 20:52:59' where MEDIACOLLECTIONID=275237
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0, aio writes: 0,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    1755403 OS file reads, 44717711 OS file writes, 5540150 OS fsyncs
    0.02 reads/s, 16384 avg bytes/read, 10.56 writes/s, 1.22 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf for space 0: size 1, free list len 12, seg size 14, is empty
    Ibuf for space 0: size 1, free list len 12, seg size 14,
    376007 inserts, 376007 merged recs, 208778 merges
    Hash table size 4980539, used cells 833255, node heap has 895 buffer(s)
    321.33 hash searches/s, 597.71 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 4 3914204403
    Log flushed up to 4 3914204403
    Last checkpoint at 4 3914199168
    0 pending log writes, 0 pending chkp writes
    23601495 log i/o's done, 3.87 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 1375224705; in additional pool allocated 10532352
    Buffer pool size 76800
    Free buffers 2
    Database pages 75903
    Modified db pages 43
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 7479502, created 160954, written 22697597
    0.02 reads/s, 0.00 creates/s, 7.11 writes/s
    Buffer pool hit rate 1000 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    8 read views open inside InnoDB
    Main thread process no. 22616, id 1644698544, state: sleeping
    Number of rows inserted 5701927, updated 8778584, deleted 1611810, read 1599079593
    0.61 inserts/s, 1.44 updates/s, 0.26 deletes/s, 19991.07 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

    1 row in set, 1 warning (0.01 sec)

    ERROR:
    No query specified

    mysql>

  • #2
    Your deadlock happens due to next-key locking in Innodb tables.

    Deadlocks are normal and if you do not have too high number of them you can just design your application to repeat operation.

    If it starts to hurt performance you need to find a way to avoid conflicting operations which cause deadlock.

    Comment


    • #3
      According to what I understand, the locking occurs because
      "page no 294220 n bits 272" holds both records.
      Is that correct?
      I have a trigger on the table which inserts into another table.
      Could the trigger be locking up my table?
      I need more info as to what to do next.
      I also need how to decipher the output from the show engine innodb status command!

      Comment

      Working...
      X