CODEWITHSUNDEEP
mariadblockingmyisam
user23440221
user23440221

Reputation: 11

Should handler open place a table metadata read lock on MariaDB MyISAM table?

I'm migrating an application from MariaDB version 5.5 to version 10.3. This application uses handler open, handler read commands to directly access MyISAM tables in the DB. Also there is another application operating on the same tables from a different connection which uses standard select / insert / update etc. statements.

In version 5.5 the handler open command does not place any locks on the table, but in 10.3 it does as follows;-

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)

When the other connection then attempts a lock tables t1 write ; command it blocks indefinitely. In the process list the connection will show 'Waiting for table metadata lock'.

The handler page in the MySQL manual https://dev.mysql.com/doc/refman/8.0/en/handler.html says;-

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by the current session or other sessions) and these modifications might be only partially visible to HANDLER ... NEXT or HANDLER ... PREV scans.

This is how MariaDB 5.5 used to behave. However the manual page for MariaDB (https://mariadb.com/kb/en/handler-commands/) implies no metadata lock as it 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.

(Obviously the table cannot change if it has been locked).

The table metadata lock that is placed on the table by the handler open command seems to break the functionality from a previous version. Should it be placing that lock?

Upvotes: 0

Views: 116

Answers (1)

danblack
danblack

Reputation: 14761

LOCK TABLES WRITE "Exclusive write lock. No other connections can read or write to this table"

This means while a HANDLER tbl OPEN is there the LOCK TABLES cannot proceed because the HANDLER tbl OPEN can read it. So it has to wait or error.

The previous 5.5 behaviour would have been violating this exclusivity.

Upvotes: 0

Related Questions