locking the same table with different aliases

Question

I'm trying to lock all tables involved in a particular set of queries but one of the queries does a self JOIN to itself. How would I lock that one?

LOCK TABLES employees e READ,
            employees e2 READ;

Superficially it seems like that should work but in my testing it doesn't seem to? Is my testing just botched or does that really not work? Or could there be something else going on that I'm unaware of?

Answer 1

You need to obtain a lock for both the alias name and the table itself

LOCK TABLES employees READ, employees AS employees_read READ;

SELECT * FROM employees AS employees_read;

UNLOCK TABLES;

here & here

Answer 2

Just to add my two cents: when locking a table alias multiple times, you need to use different alias names even if you use the same table/alias and lock each one of them: (sorry for the dumb example)

LOCK TABLES
    orders READ,
    order_items READ,
    orders AS orders2 READ,
    orders AS orders3 READ;

SELECT *
FROM orders
WHERE (
    SELECT COUNT(*) 
    FROM order_items
    JOIN orders AS orders2
    ON orders2.id = order_items.order_id
    WHERE orders2.date BETWEEN '2023-01-01' AND '2023-03-31'
) > 0
AND (
    SELECT COUNT(*) 
    FROM order_items
    JOIN orders AS orders3
    ON orders3.id = order_items.order_id
    WHERE orders3.location = 'Kansas'
) > 0;

UNLOCK TABLES;