PHP MySQLi Lock Tables Query Sometimes Does Not Complete

Question

I am attempting to lock some tables while running some code and unlocking the tables when I am done.

Process:

1. Run Lock Tables MySQL Query.
2. Run some PHP Code.
3. Run Unlock Tables MySQL Query.

While running this process, 9 times out of 10 everything runs perfectly. Sometimes, when I run my query no response comes back from MySQL and PHP just waits for a response. Since no response happens, I never get to steps 2 or 3 and the table stays locked indefinitely. I am running the exact same Lock Tables Query in every single attempt.

After some research, I found that the problem happens when a Second Server writes to the database and then the First Server tries to do a "LOCK TABLES" query. It appears that the Second Server's write does a "metadata lock". I believe this is due to MySQL having the "autocommit" feature enabled and the transaction is not yet completed. So when I attempt to lock the tables, MySQL never responds and the "show processlist;" has "Waiting for table metadata lock | LOCK TABLES ..." listed in it until I manually kill the process.

I am locking Numerous tables. Below is a similar example to my actual table query. I am using the same table multiple times with different aliases based on the queries I am attempting to prevent from accessing the tables.

$sql = "LOCK TABLES table1 as t1 WRITE
                    , table2 as t2 WRITE
                    , table3 WRITE
                    , table2 WRITE
                    , table4 WRITE
                    , table1 WRITE
                    , table5 WRITE
                    , table5 as t5 WRITE
                    , table6 as t6 WRITE
                    , table7 as t7 WRITE
                    , table7 WRITE
                    , table8 as t8 WRITE
                    , table9 t9 WRITE
                    , table10 t10 WRITE
                    , table11 t11 WRITE
                    , table12 WRITE;";

$this->mysqli = new mysqli(
    $this->credentials->server
    , $this->credentials->user
    , $this->credentials->password
    , $this->credentials->database
);

try{
    error_log('before first attempt lock tables '.$sql);
    $this->mysqli->query($sql);
    error_log('after first attempt lock tables');
} catch (Exception $e){
    error_log('Error locking tables: '.$e->getMessage());
}

error_log('After try catch.');
if($this->mysqli->error){
    error_log('lock table error: '.$this->mysqli->error);
}

When the process fails, I see "before first attempt lock tables" in my PHP Error log. I do not see any of the other error_log() calls. After some checking, I determined it is because PHP has not received a response from MySQL.

I never get into the Catch Exception, since MySQL is not returning an error. MySQL is not returning anything unless I manually kill the MySQL Lock Tables Process.

If I don't kill the process, the PHP code never stops waiting for a response from mysql.

I am confused why the "LOCK TABLES" query is not gracefully waiting for the "metadata lock" to complete and then running. It was my understanding that more than one server was allowed to write to a database and the lock table features would work together instead of one hanging indefinitely.

Answer 1

Gag! LOCK TABLES is essentially unnecessary if you are using ENGINE=InnoDB. If you are still using MyISAM, convert!

After converting, you will need to understand how to put things in "transactions" (BEGIN...COMMIT) and use SELECT ... FOR UPDATE.