Why mysqli open transcation locks the database?

Question

I am trying to use mysqli to insert the data and there is some strange behavior. For example, when I first using $mysqli->autocommit(FALSE); and spent a several minute to run my PHP and wait for the query provided, it will hold the database until $mysqli->commit(); , so I can not execute any other database operation . When I check the status in phpmyadmin , it show the coming SQL query status is Waiting for table metalock , How to fix it? Thanks

/* Insert log Query */
function putLog($query){
global $mysqli,$ip,$browser,$dateLog,$isQuerySuccess;
$isQuerySuccess = $mysqli->query("INSERT INTO DPS_Log_$dateLog (PageID,FunctionID,ActionID,UserID,UserIP,UserInfo,LogType,Remark,LogTime) VALUES (15,20,25,25,'$ip','$browser',1,'$query',NOW())") ? true : false;
}

/* Start DB connection */
$mysqli = new mysqli(DATABASEIP, DBUSER, DBPWD, DATABASE,PORT);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->autocommit(FALSE);

$isQuerySuccess = true;

putLog ("Fail to delete: $folderPath.$item");

$isQuerySuccess ? $mysqli->commit() : $mysqli->rollback();
$mysqli->close();

Update: I finally found the problem is caused by another query . In short, The above coding is to insert log while the below query is to check whether the log table exist when the user login. The problem is , when I am open a transaction and try to log the operation(operation takes > 30 second) results, I can not execute the below query (wait for table metalock) so the whole system holds until the operation finished , how to fix it? thanks

$sql = "
CREATE TABLE IF NOT EXISTS `$logTableName` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`PageID` int(2),
`FunctionID` int(2),
`ActionID` int(3) NOT NULL,
`UserID` int(5) NOT NULL,
`UserIP` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`UserInfo` text COLLATE utf8_unicode_ci NOT NULL,
`LogType` int(1) NOT NULL DEFAULT '1',
`Remark` text COLLATE utf8_unicode_ci NOT NULL,
`LogTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
";
$this -> databaseHelper -> common_query($sql); 

Answer 1

Proper order of things is:

$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO ...");
$mysqli->commit();

But for one small insert it is weird to do it this way. In your particular case it is sufficient to do only this:

$mysqli->query("INSERT INTO ...);

no autocommit disabling and no commits.