CODEWITHSUNDEEP
mysql
Stephen Bouffe
Stephen Bouffe

Reputation: 517

Error With Table Locking

This SELECT returns the results as expected.

SELECT 
    h.id,
    h.rec_inv_num,
    h.rec_inv,
    h.trans_date,
    h.trans_branch,
    h.trans_type,
    h.trans_number
FROM
    1_trans_history h
WHERE
    h.id IN (SELECT 
            h.id
        FROM
            1_trans_history_entries_stat s
                LEFT JOIN
            1_trans_history_entries e ON e.id = s.fk_trans_history_entries_id
                LEFT JOIN
            1_trans_history h ON h.id = e.fk_trans_history_id
                LEFT JOIN
            addresses a ON a.id = h.i_fk_addresses_id
        WHERE
            s.item_stat = 2
        GROUP BY h.id
        ORDER BY a.company , h.i_surname , h.i_middle_init , h.i_first_name , h.i_title);

I now need to lock the tables for write so I added this but I get "Error Code: 1100 Table '1_trans_history' was not locked with LOCK TABLES" 

LOCK TABLES 
1_trans_history h WRITE,
1_trans_history_entries e WRITE,
1_trans_history_entries_stat s WRITE;

I understand that "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:"

So I have changed the code to this but I get the same error

LOCK TABLES 
1_trans_history h WRITE,
1_trans_history_entries e WRITE,
1_trans_history_entries_stat s WRITE,
1_trans_history h2 WRITE,
1_trans_history_entries e2 WRITE,
1_trans_history_entries_stat s2 WRITE,
addresses a2 WRITE;

SELECT 
    h.id,
    h.rec_inv_num,
    h.rec_inv,
    h.trans_date,
    h.trans_branch,
    h.trans_type,
    h.trans_number
FROM
    1_trans_history h
WHERE
    h.id IN (SELECT 
            h2.id
        FROM
            1_trans_history_entries_stat s2
                LEFT JOIN
            1_trans_history_entries e2 ON e2.id = s2.fk_trans_history_entries_id
                LEFT JOIN
            1_trans_history h2 ON h2.id = e2.fk_trans_history_id
                LEFT JOIN
            addresses a2 ON a2.id = h2.i_fk_addresses_id
        WHERE
            s2.item_stat = 2
        GROUP BY h2.id
        ORDER BY a2.company , h2.i_surname , h2.i_middle_init , h2.i_first_name , h2.i_title);

unlock tables;

Upvotes: 0

Views: 100

Answers (1)

SHaKie
SHaKie

Reputation: 1424

I believe there is some confusion with the aliases in the LOCK statement. I've removed one of the aliases for the 1_trans_history table and explicitly added it to the LOCK statement. I've also renamed the a2 and s2 alias for a little more clarity.

LOCK TABLES
1_trans_history WRITE,
1_trans_history h WRITE,
1_trans_history_entries e WRITE,
1_trans_history_entries_stat s WRITE,
addresses a WRITE;

SELECT 
   id,
   rec_inv_num,
   rec_inv,
   trans_date,
   trans_branch,
   trans_type,
   trans_number
FROM
    1_trans_history
WHERE
    1_trans_history.id IN (SELECT 
        h.id
    FROM
        1_trans_history_entries_stat s
            LEFT JOIN
        1_trans_history_entries e ON e.id = s.fk_trans_history_entries_id
            LEFT JOIN
        1_trans_history h ON h.id = e.fk_trans_history_id
            LEFT JOIN
        addresses a ON a.id = h.i_fk_addresses_id
    WHERE
        s.item_stat = 2
    GROUP BY h.id
    ORDER BY a.company , h.i_surname , h.i_middle_init , h.i_first_name , h.i_title);
UNLOCK TABLES;

Try that, it should work for you.

To be honest, I believe the only table that would need the alias is 1_trans_history, however I understand the query is more readable with the other aliases specified.

Upvotes: 1

Related Questions