Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24494

Lock on simple UPDATE query with Semaphore wait > 600 seconds

    XMLWordPrintable

    Details

      Description

      MariaDB 10.4.17 server randomly hangs on simple UPDATE statements.

      UPDATE loginlogs SET rxbytes = rxbytes + ?, txbytes = txbytes + ? WHERE logid = ?
      

      When I query

      SELECT * FROM sys.innodb_lock_waits
      

      I get output such as

      wait_started    wait_age        wait_age_secs   locked_table    locked_index    locked_type     waiting_trx_id  waiting_trx_started     waiting_trx_age waiting_trx_rows_locked waiting_trx_rows_modified       waiting_pid     waiting_query   waiting_lock_id waiting_lock_mode       blocking_trx_id blocking_pid    blocking_query  blocking_lock_id        blocking_lock_mode      blocking_trx_started    blocking_trx_age        blocking_trx_rows_locked        blocking_trx_rows_modified      sql_kill_blocking_query sql_kill_blocking_connection
      2020-12-27 07:43:32     00:00:04        4       `db`.`loginlogs`   PRIMARY RECORD  37075679818     2020-12-27 07:43:32     00:00:04        1       0       19194139        UPDATE loginlogs SET  ... HERE logid = 64634225227638257       37075679818:921:15944673:61     X       37075617021     19191704        UPDATE loginlogs SET ... HERE logid = 64634225227638257       37075617021:921:15944673:61     X       2020-12-27 07:43:07     00:00:29        1       0       KILL QUERY 19191704     KILL 19191704
      

      So one UPDATE is blocking another update due to X lock. The blocking UPDATE does not show up in innodb lock waits, so no idea why it keeps on waiting with the X lock on loginlogs table. logid is PRIMARY KEY.

      The UPDATE is not part of any transaction, its a stand-alone statement.

      The lock then ends up in this:

      2020-12-27  7:59:00 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung
      

      And then MariaDB restarts.

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.