Hang when locking both spider table and remote table in the same statement

A simplified version of a test mentioned in a comment in MDEV-29854[1], tested at 11.0 0199a5d0f634051f23d4c7f7ce7651c7f93c818f. Probably also existing in other versions.

--echo #

--echo # MDEV-XXXXX Hang when locking both spider table and remote table in the same statement

--echo #

--disable_query_log

--disable_result_log

--source ../../t/test_init.inc

--enable_result_log

--enable_query_log

evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql

OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');

create table t (c int);

create table t2 (c int) ENGINE=Spider

COMMENT='WRAPPER "mysql", srv "srv",TABLE "t"';

LOCK TABLES t WRITE,t2 WRITE;

drop table t, t2;

drop server srv;

--disable_query_log

--disable_result_log

--source ../../t/test_deinit.inc

--enable_result_log

--enable_query_log

--echo #

--echo # end of test mdev_XXXXX

--echo #

The metadata lock of t is acquired when opening tables. Then in lock_external() it iterates over all tables to lock. After locking t, when trying to lock t2, spider sends a query to the data node to lock t again. Somehow the MDL lock of t is not released yet, so the triggered open_table() hangs at waiting for MDL until lock_wait_timeout (default 1h) has passed.

Not sure whether it counts as a spider bug or a sql layer bug.

[1] https://jira.mariadb.org/browse/MDEV-29854?focusedCommentId=238531&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-238531

For comparison, if we simply lock tables with the same name / alias in one statement, the parser would pick out the issue and throw a 1066 ER_NONUNIQ_TABLE