Geek Answers Handbook

Why does mysqli open transcation lock the database?

I am trying to use mysqli to insert data, and there is a weird behavior. For example, when I first used it $mysqli->autocommit(FALSE);and spent a few minutes starting my PHP and waiting for the request, it will store the database until $mysqli->commit();, so I cannot perform any other database operation. When I check the status in phpmyadmin, it shows that the next SQL query has a status Waiting for table metalock, how can I 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();

:   , , , . , , , . , ( > 30 ), ( metalock), , ?

$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);
+4
5

:

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

. :

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

.

+5

CREATE TABLE your_table , INSERT INTO your_table . CREATE TABLE , .

, (IF NOT EXISTS), , CREATE TABLE:

SELECT COUNT(*) FROM information_schema
WHERE table_schema = 'your database here'
AND table_name = 'your table name here';

() , CREATE TABLE IF EXISTS, 0.

, , , . , information_schema MEMORY, ( ). IF EXISTS.

, (InnoDB).

+2
  Please increase your php execution time by php.ini setting 

     step 1 open php.ini
     step 2 search max_execution_time 
     step 3 set max_execution_time = 3000
     step 4 save it 
     step 5 Restart  apache 
     step 6 run the program again
+1

DDL . DDL - , CREATE, ALTER DROP.

, . .

.

+1

. . , .

-, . .

, , , , . , - , . Mysql Savepoint , .

- .

open a transaction.

everything_is_okay=true;
try{
    do a query
    do another
    if(everything_is_not_all_right_till_now())
        throw a meaningfull exception;
    do some more query;
    savepoint A;
    and again some other query;
    if(some_partial_problem_happend())
        rollback to savepoint A;
    do hell lot of query;
}

catch(meaningfull exception){
    rollback;
}
if(every_thing_is_still_okay())
    commit;

By the way, even after creating a transaction, only these 2 requests should not take> 30 seconds. Is it likely that you are looking at the wrong bucket for spoiled eggs?: N Think about it.

+1
source

More articles:


All Articles