1

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.