Announcement

Announcement Module
Collapse
No announcement yet.

innodb: question about deadlock

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

  • innodb: question about deadlock

    what i can do, if i see deadlock, which started long time ago?

  • #2
    Please post SHOW INNODB STATUS results here.

    Comment


    • #3
      ok, i sending copy of status (from cca 17pm)... i thing when only deadlock part enought

      Quote:


      .
      .
      .
      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      071128 14:05:16
      *** (1) TRANSACTION:
      TRANSACTION 0 125715557, ACTIVE 26 sec, process no 24189, OS thread id 2496023472 inserting
      mysql tables in use 1, locked 1
      LOCK WAIT 11 lock struct(s), heap size 1024, undo log entries 1
      MySQL thread id 526468, query id 27045614 192.168.30.23 game_user update
      insert into uzivatel_im_data (user_id, zustatek, zetony) values (25704,0,0)
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 0 page no 12378 n bits 144 index `PRIMARY` of table `game/uzivatel_im_data` trx id 0 125715557 lock_mode X insert intention waiting
      Record lock, heap no 1
      *** (2) TRANSACTION:
      TRANSACTION 0 125715374, ACTIVE 36 sec, process no 24189, OS thread id 2537634736 fetching rows, thread declared inside InnoDB 457
      mysql tables in use 1, locked 1
      1835 lock struct(s), heap size 109888, undo log entries 3
      MySQL thread id 526108, query id 27052772 192.168.30.22 game_user Updating
      UPDATE uzivatel SET los_win = 3, los_date = '2007-11-28 14:05:07', los_rule = 15 WHERE seccode = 754829331
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 0 page no 12378 n bits 144 index `PRIMARY` of table `game/uzivatel_im_data` trx id 0 125715374 lock_mode X
      Record lock, heap no 1
      Record lock, heap no 2
      Record lock, heap no 3
      Record lock, heap no 4
      Record lock, heap no 5
      Record lock, heap no 6
      Record lock, heap no 7
      Record lock, heap no 8
      Record lock, heap no 9
      Record lock, heap no 10
      Record lock, heap no 11
      Record lock, heap no 12
      Record lock, heap no 13
      Record lock, heap no 14
      Record lock, heap no 15
      Record lock, heap no 16
      Record lock, heap no 17
      Record lock, heap no 18
      Record lock, heap no 19
      Record lock, heap no 20
      Record lock, heap no 21
      Record lock, heap no 22
      Record lock, heap no 23
      Record lock, heap no 24
      Record lock, heap no 25
      Record lock, heap no 26
      Record lock, heap no 27
      Record lock, heap no 28
      Record lock, heap no 29
      Record lock, heap no 30
      Record lock, heap no 31
      Record lock, heap no 32
      Record lock, heap no 33
      Record lock, heap no 34
      Record lock, heap no 35
      Record lock, heap no 36
      Record lock, heap no 37
      Record lock, heap no 38
      Record lock, heap no 39
      Record lock, heap no 40
      Record lock, heap no 41
      Record lock, heap no 42
      Record lock, heap no 43
      Record lock, heap no 44
      Record lock, heap no 45
      Record lock, heap no 46
      Record lock, heap no 47
      Record lock, heap no 48
      Record lock, heap no 49
      Record lock, heap no 50
      Record lock, heap no 51
      Record lock, heap no 52
      Record lock, heap no 53
      Record lock, heap no 54
      Record lock, heap no 55
      Record lock, heap no 56
      Record lock, heap no 57
      Record lock, heap no 58
      Record lock, heap no 59
      Record lock, heap no 60
      Record lock, heap no 61
      Record lock, heap no 62
      Record lock, heap no 63
      Record lock, heap no 64
      Record lock, heap no 65
      Record lock, heap no 66
      Record lock, heap no 67
      Record lock, heap no 68
      Record lock, heap no 69
      Record lock, heap no 70
      Record lock, heap no 71
      Record lock, heap no 72
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 0 page no 19991 n bits 80 index `PRIMARY` of table `game/uzivatel` trx id 0 125715374 lock_mode X waiting
      Record lock, heap no 12 PHYSICAL RECORD: n_fields 57; compact format; info bits 0
      .
      .
      .
      .

      *** WE ROLL BACK TRANSACTION (1)

      .
      .
      .
      .
      .
      .

      Comment


      • #4
        Posted info means that this deadlock has been resolved. What do you mean by "i see deadlock, which started long time ago"? If you use transactional storage engine, your application should be aware of such problems and should retry queries (most of the time).

        Comment


        • #5
          Quote:

          071128 14:05:16
          *** (1) TRANSACTION:
          TRANSACTION 0 125715557, ACTIVE 26 sec, process no 24189, OS thread id 2496023472 inserting
          mysql tables in use 1, locked 1
          LOCK WAIT 11 lock struct(s), heap size 1024, undo log entries 1
          MySQL thread id 526468, query id 27045614 192.168.30.23 game_user update
          insert into uzivatel_im_data (user_id, zustatek, zetony) values (25704,0,0)


          from this ) i get this list innodb status on 17:00 and time wroted is 14... i thing when deadlock is alive about 3 hours...
          I still don't know if info about deadlock is for me as info or warning or error )

          Comment

          Working...
          X