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

Killing application cannot remove DB locked session

    XMLWordPrintable

Details

    Description

      When a DB session running an UPDATE on locked row(s), killing the corresponding application (e.g. MySQL client) does not kill/remove that session in DB. The session is gone when the innodb_lock_wait_timeout is reached.

      Scenario:
      1. Create a simple table and insert a row:
      CREATE TABLE `t1` (
      `idx` int(11) NOT NULL,
      `name` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`idx`)
      ) ;
      INSERT INTO t1 VALUES (1, 'abc'); COMMIT;

      2. Run MySQL client to create session A to lock the row:
      > SET AUTOCOMMIT=OFF;
      > UPDATE t1 SET name='sess A' WHERE idx=1;

      3. Run another MySQL client to create sesssion B, update the locked row by session A:
      > SET AUTOCOMMIT=ON;
      > UPDATE t1 SET name='sess B' WHERE idx=1;

      4. Run SHOW FULL PROCESSLIST on another MySQL client, it shows session B is running running the UPDATE statement (waiting for the row to be unlocked).

      5. On the OS shell, run "kill -9 <Session B MySQL client PID>" to kill the MySQL client of session B.

      6. Run SHOW FULL PROCESSLIST on another MySQL client again, it shows session B still remains in the DB, running the UPDATE statement. Session B is gone until innodb_lock_wait_timeout is reached.

      7. If session A commited the UPDATE before session B's innodb_lock_wait_timeout is reached, the row will be updated by session B.

      I would suppose that killing application can kill the session in DB but it doesn't in the above scenario. Please advise the above behavior is normal or not.

      Thanks.

      Attachments

        Activity

          People

            Unassigned Unassigned
            ktchow_ogcio Chow King Tak
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.