Today we experienced a rather weird issue. This has happend a few times before (last few weeks) and was remedied by restarting the server.
All of a sudden updates to our account table stop working and timeout after the 50 second innodb lock timeout we have. We think the account table is the culprit, at least from the innodb status logs.
The table has a self referencing foreign key. When we try to update a row in this table while updating that foreign self reference key, this is when the issue occurs. Same query without the self reference foreign key column update works just fine. This is something that always worked fine, just started experiencing issues a few weeks ago. Restarting the process does the job, but we want to get to the bottom of the problem. Today we removed the self referencing foreign key constraint and rebooted the server, so it’s not happening now. It’s hard to reproduce, because it happens only on our production boxes and starts about once a week or so until we reboot, then it’s fine for another week or so.
Here is a snippet from the innodb status. Any insight or help would be appreciated. If you need more info out of the innodb status, let me know. Nothing else seems out of the ordinary there from what I can tell. Below are only three wait records, there are about 10 total doing the same thing.
—TRANSACTION 37622F1B, ACTIVE 21 sec, process no 24330, OS thread id 1262987584 updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 2065601, query id 60203446 Updating
UPDATE account SET similar_account_id = ‘1915596’, similar_account_score = ‘15’, mtime = ‘1352392438’ WHERE (account.id = 1915679)
Trx read view will not see trx with id >= 37622F1C, sees < 375A3FB2
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1049519 page no 39375 n bits 80 index FK_ac_ac of table db.account trx id 37622F1B lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
—TRANSACTION 37622E32, ACTIVE 25 sec, process no 24330, OS thread id 1244084544 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 2065337, query id 60202608 Updating
UPDATE account SET similar_account_id = ‘1915036’, similar_account_score = ‘12’, mtime = ‘1352392395’ WHERE (account.id = 1915670)
Trx read view will not see trx with id >= 37622E33, sees < 375A3FB2
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1049519 page no 41529 n bits 152 index PRIMARY of table db.account trx id 37622E32 lock_mode X locks rec but not gap waiting
Record lock, heap no 83 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
—TRANSACTION 37622B41, ACTIVE 37 sec, process no 24330, OS thread id 1261123904 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 2065311, query id 60200083 Updating
UPDATE account SET similar_account_id = ‘1915588’, similar_account_score = ‘18’, mtime = ‘1352392383’ WHERE (account.id = 1915662)
Trx read view will not see trx with id >= 37622B42, sees < 375A3FB2
------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1049519 page no 41529 n bits 144 index PRIMARY of table db.account trx id 37622B41 lock_mode X locks rec but not gap waiting
Record lock, heap no 78 PHYSICAL RECORD: n_fields 27; compact format; info bits 0