Databases
StackOverflow2013
Postsdbo
POWhy mysqli open transcation locks the database?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy mysqli open transcation locks the database?
    primarykey
    data
    text
    <p>I am trying to use mysqli to insert the data and there is some strange behavior. For example, when I first using <code>$mysqli-&gt;autocommit(FALSE);</code> and spent a several minute to run my PHP and wait for the query provided, it will hold the database until <code>$mysqli-&gt;commit();</code> , so I can not execute any other database operation . When I check the status in phpmyadmin , it show the coming SQL query status is <code>Waiting for table metalock</code> , How to fix it? Thanks</p> <pre><code>/* Insert log Query */ function putLog($query){ global $mysqli,$ip,$browser,$dateLog,$isQuerySuccess; $isQuerySuccess = $mysqli-&gt;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-&gt;autocommit(FALSE); $isQuerySuccess = true; putLog ("Fail to delete: $folderPath.$item"); $isQuerySuccess ? $mysqli-&gt;commit() : $mysqli-&gt;rollback(); $mysqli-&gt;close(); </code></pre> <p><strong>Update</strong>: 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</p> <pre><code>$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 -&gt; databaseHelper -&gt; common_query($sql); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRandomSeed
    1. USuser782104
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. USuser782104
      1. VTBountyStart
    2. VO
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo your operation and then get the heck out. Anyway, start at http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-transactions.html and http://dev.mysql.com/doc/refman/5.7/en/innodb-concepts.html - note that locking or some other form of control (e.g. MVCC) is required to maintain ACID. Exactly what is locked (and how) depends upon a number of factors.
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. USuser2864740
    2. COdo you want lock free way, something like add a `status` column and update it after `putlog`
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. USfarmer1992
    3. COPlease increase your php execution time by php.ini setting
      singulars
      1. POWhy mysqli open transcation locks the database?
      1. USalok.kumar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload