GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Waiting on lock grant

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

  • Waiting on lock grant

    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

  • #2
    Hi,

    Since updates are giving errors due to InnoDB lock_wait_timeout error, means that the updates are accessing and trying to update rows in such a way that they overlap when executed from multiple concurrent sessions. You should note that having a foreign key introduces more locking, and InnoDB has to lock more rows then it would as compared to when there are no foreign keys. This is evident from the fact that without foreign keys the updates get executed successfully.

    In your case, foreign keys appear to be introducing more locking that is limiting the concurrency. so it makes sense to remove the foreign key from the table and instead do validation in the application side.

    Comment

    Working...
    X