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

A 'handler open' places a table metadata read lock on MyISAM table

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.3.39
    • N/A
    • Locking
    • None
    • RHEL 8

    Description

      Use a 'HANDLER <table> OPEN' command on one connection, then try a 'LOCK TABLES <table> write' from another connection. The lock will wait for the handler to close as the 'HANDLER <table> OPEN' command has left a lock on the table.

      MariaDB [test]> create table t1 (a int) engine=MyISAM ;
      Query OK, 0 rows affected (0.003 sec)
       
      MariaDB [test]> handler t1 open ;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> select * from information_schema.metadata_lock_info ;
      +-----------+-----------------+---------------+---------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE       | LOCK_DURATION | LOCK_TYPE           | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-----------------+---------------+---------------------+--------------+------------+
      |     17821 | MDL_SHARED_READ | NULL          | Table metadata lock | test         | t1         |
      +-----------+-----------------+---------------+---------------------+--------------+------------+
      1 row in set (0.001 sec)
      

      Attachments

        Activity

          ff234 Fourfront created issue -
          ysth Yitzchak added a comment -

          Appears to be from https://stackoverflow.com/questions/78019973/should-handler-open-place-a-table-metadata-read-lock-on-mariadb-myisam-table, where the user is upgrading from mariadb 5.5 and encountering an issue with old ported from ISAM code blocking a newer application that uses standard DML.

          ysth Yitzchak added a comment - Appears to be from https://stackoverflow.com/questions/78019973/should-handler-open-place-a-table-metadata-read-lock-on-mariadb-myisam-table , where the user is upgrading from mariadb 5.5 and encountering an issue with old ported from ISAM code blocking a newer application that uses standard DML.
          danblack Daniel Black added a comment -

          LOCK TABLES WRITE "Exclusive write lock. No other connections can read or write to this table", which means it can't continue while a HANDLER tbl OPEN because that would mean there is an exclusive write lock that isn't exclusive because the HANDLER tbl OPEN can read it.

          So I'm thinking the current behaviour is correct, because if allowed in 5.5, the lock tables write wasn't exclusive.

          danblack Daniel Black added a comment - LOCK TABLES WRITE "Exclusive write lock. No other connections can read or write to this table", which means it can't continue while a HANDLER tbl OPEN because that would mean there is an exclusive write lock that isn't exclusive because the HANDLER tbl OPEN can read it. So I'm thinking the current behaviour is correct, because if allowed in 5.5, the lock tables write wasn't exclusive.
          danblack Daniel Black made changes -
          Field Original Value New Value
          issue.field.resolutiondate 2024-02-21 01:34:55.0 2024-02-21 01:34:55.279
          danblack Daniel Black made changes -
          Fix Version/s N/A [ 14700 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 6 ]
          ff234 Fourfront added a comment -

          I'm not convinced with this argument and don't agree this issue should be closed.

          The manual page (https://mariadb.com/kb/en/handler-commands/) states ;-

          ...
          Limitations
          As this is a direct interface to the storage engine, some limitations may apply for what you can do and what happens if the table changes. Here follows some of the common limitations:

          Finding 'Old Rows'
          HANDLER READ is not transaction safe, consistent or atomic. It's ok for the storage engine to returns rows that existed when you started the scan but that were later deleted. This can happen as the storage engine may cache rows as part of the scan from a previous read.
          You may also find rows committed since the scan originally started.
          ...

          In the old version (5.5) of MariaDB this was possible and intentional - i.e. other connections must be able to alter content in the table whilst there is a HANDLER OPEN connection on it.
          I would expect the LOCK TABLES WRITE command to block HANDLER READ statements. Instead the HANDLER OPEN statement is blocking every other connection from modifying rows in the table.

          Surely this means that either the manual is incorrect, and the HANDLER READ statements have now been made transaction safe and consistent, or this is a bug and the HANDLER OPEN should not be placing the table metadata lock.

          ff234 Fourfront added a comment - I'm not convinced with this argument and don't agree this issue should be closed. The manual page ( https://mariadb.com/kb/en/handler-commands/ ) states ;- ... Limitations As this is a direct interface to the storage engine, some limitations may apply for what you can do and what happens if the table changes . Here follows some of the common limitations: Finding 'Old Rows' HANDLER READ is not transaction safe, consistent or atomic. It's ok for the storage engine to returns rows that existed when you started the scan but that were later deleted. This can happen as the storage engine may cache rows as part of the scan from a previous read. You may also find rows committed since the scan originally started. ... In the old version (5.5) of MariaDB this was possible and intentional - i.e. other connections must be able to alter content in the table whilst there is a HANDLER OPEN connection on it. I would expect the LOCK TABLES WRITE command to block HANDLER READ statements. Instead the HANDLER OPEN statement is blocking every other connection from modifying rows in the table. Surely this means that either the manual is incorrect, and the HANDLER READ statements have now been made transaction safe and consistent, or this is a bug and the HANDLER OPEN should not be placing the table metadata lock.

          People

            Unassigned Unassigned
            ff234 Fourfront
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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