> I am running 5.2.
That version of MySQL was never "released". Please update to at least 5.5.
> My question is why should I care if one query is holding lock and another waiting if I am not seeing any issues?
Normally, you don't need to worry about locks. What you do need to worry about is transactions that fail due to locks. But, in general, all you need to do is rerun the transaction (BEGIN...COMMIT).
> The errors have been there for weeks.
Elaborate. InnoDB STATUS is rather transient (usually replaced within a minute). Please explain "weeks".
> UPDATE social.subscription SET title = 'Bones'
Is there no WHERE clause? So, you are updating _all_ the rows of that table?
Are there any VIEWs involved here? Or are subscription and activity real tables?
Find the transaction that contains
SELECT *
FROM social.activity
WHERE (social.activity.friendId = 386262203)
AND (social.activity.name = 'subscription') FOR UPDATE ;
Let's see all the statements from BEGIN through COMMIT.
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'auto%';
You possibly have the 'classical' deadlock:
One transaction does:
BEGIN;
write or SELECT FOR UPDATE activity ... ;
write or SELECT FOR UPDATE subscription ... ;
COMMIT;
And the other does
BEGIN;
write or SELECT FOR UPDATE subscription ... ;
write or SELECT FOR UPDATE activity ... ;
COMMIT;
It may suffice to reorder the commands in one of the transactions. (If this is practical.)