MariaDB – Are Aliases Required for Subquery Table Locks?

lockingmariadb

MySQL suffers from a bug (which they claim is not a bug) that prevents a table lock from working on a sub-query unless you create a table alias for each mention of the table name.

See: https://bugs.mysql.com/bug.php?id=31080

They say:

"You cannot use a locked table multiple times in a single query. Use
aliases instead, in which case you must obtain a lock for each alias
separately"

I looked at the MariaDB lock tables manual page on their site, but it is unclear if this limitation has been fixed in Maria, or is still present.

Since my application builds queries dynamically, trying to work around this MySQL bug would create very messy code and a lot of unnecessary work to create pointless table aliases. If I can just swap the whole MySQL DB for Maria without compatibility issues, that would save a ton of time.

Best Answer

Really peculiar behaviour, unfortunate this limitation still exists atleast in my version of MariaDB:

MariaDB [test]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.23-MariaDB |
+-----------------+
1 row in set (0.00 sec)


MariaDB [test]> create table t ( x int not null );
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> lock tables t read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Locking the aliases does not seem to help

MariaDB [test]> lock tables t read, t1 read, t2 read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Weird indeed.

Related Question