Announcement

Announcement Module
Collapse
No announcement yet.

Deadlock information never provides statement for one of the transactions

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

  • Deadlock information never provides statement for one of the transactions

    From "show engine innodb status" LATEST DETECTED DEADLOCK section we never have statements for both transaction. Always only for one of them.
    For example:
    2014-05-21 00:06:48 7f8f48127700
    *** (1) TRANSACTION:
    TRANSACTION 2307529851, ACTIVE 7 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 17 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 10
    MySQL thread id 7592416, OS thread handle 0x7f8f33de4700, query id 15406475198 10.58.0.147 database1 Searching rows for update
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 97 page no 237594 n bits 456 index `i_certainindex` of table `database1`.`certaintable` trx id 2307529851 lock_mode X waiting
    *** (2) TRANSACTION:
    TRANSACTION 2307529808, ACTIVE 8 sec updating or deleting
    mysql tables in use 1, locked 1
    25 lock struct(s), heap size 3112, 189 row lock(s), undo log entries 122
    MySQL thread id 7266970, OS thread handle 0x7f8f48127700, query id 15406475505 10.58.0.147 database1 updating
    update certaintable set program_status='INACTIVE', date_expired='2014-05-21 00:06:48' where (t_id in (52088)) and program_status='ACTIVE' and (day_of_week_local in (0 , 1 , 2 , 3 , 4 , 5 , 6))
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 97 page no 237594 n bits 456 index `i_certainindex` of table `database1`.`certaintable` trx id 2307529808 lock_mode X
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 97 page no 237594 n bits 464 index `i_certainindex` of table `database1`.`certaintable` trx id 2307529808 lock_mode X locks gap before rec insert intention waiting
    *** WE ROLL BACK TRANSACTION (1)

    Sometimes there is no statement for committed transaction, sometimes not for rollbacked but always only one
    We run Percona server version 5.6.17-65.0-56-log

    Any ideas?

Working...
X