Deadlock for weeks in innodb status

[Raj Khanna]

I just happened to notice that bunch of our databases are reporting deadlocks in innodb status. The errors have been there for weeks. We have not noticed any performance or downtime issues. I am running 5.2.

My question is why should I care if one query is holding lock and another waiting if I am not seeing any issues? I understand that the query will stay pending until I don't clear the lock and other queries requiring to read that row will also get stuck However, I want to understand the relationship between deadlock and performance? When will deadlock become a performance or stability issue? I can monitor and retry/kill the query to remove deadlock but would like to know the downside of not doing so.


140331 15:44:53
*** (1) TRANSACTION:
TRANSACTION 78D6C8435, ACTIVE 0 sec, process no 19612, OS thread id 1449400640 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 2
MySQL thread id 2956793, query id 20931091662 10.10.30.19 testuser statistics
SELECT * FROM social.activity WHERE (social.activity.friendId = 386262203) AND (social.activity.name = 'subscription') FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16129 page no 1057 n bits 376 index `PRIMARY` of table `social`.`activity` trx id 78D6C8435 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 78D6C843F, ACTIVE 0 sec, process no 19612, OS thread id 1396906304 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1248, 36 row lock(s), undo log entries 33
MySQL thread id 2956433, query id 20931091748 10.10.30.11 testuser Updating
UPDATE social.subscription SET title = 'Bones'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 16129 page no 1057 n bits 376 index `PRIMARY` of table `social`.`activity` trx id 78D6C843F lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35012 page no 33439 n bits 120 index `PRIMARY` of table `social`.`subscription` trx id 78D6C843F lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

kr