为什么mysqli打开事务会锁定数据库？

Question

我正在尝试使用mysqli插入数据，但出现了一些奇怪的行为。例如，当我首次使用$mysqli->autocommit(FALSE);并花费几分钟运行我的PHP并等待查询结果时，它会持有数据库，直到$mysqli->commit();，因此我无法执行任何其他数据库操作。当我在phpmyadmin中检查状态时，它显示即将到来的SQL查询状态为等待锁定表格，如何解决？谢谢。

/* 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); 

- user782104

---

做完操作后就赶紧离开。无论如何，从http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-transactions.html和http://dev.mysql.com/doc/refman/5.7/en/innodb-concepts.html开始 - 请注意，为了保持ACID性质，需要使用锁定或其他形式的控制（例如MVCC）。确切的锁定对象（以及方式）取决于许多因素。 - user2864740

你想要无锁的方式吗？比如添加一个“状态”列，在“putlog”之后更新它。 - farmer1992

请通过设置php.ini来增加您的php执行时间。 - alok.kumar

展示你的另一个查询。 - sectus

如果您需要一些存在于函数作用域之外的变量（global $mysqli,$ip,$browser,$dateLog,$isQuerySuccess;），请将其作为参数传递，不要滥用 global。 - Elias Van Ootegem

Answer 1

正确的事物顺序是：

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

但是只有一个小插入，这种做法有些奇怪。在您的情况下，仅需要执行以下操作即可：

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

没有禁用自动提交，也没有提交。

- Artur

Answer 2

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';

首先发出这个（非阻塞）查询，然后只有在上述返回0的情况下，才发出后续的CREATE TABLE IF EXISTS 语句。

虽然很少见，但是有可能会在执行上述两个语句的中间，出现并发事务可以创建该表并锁定。这是因为information_schema表是MEMORY表，此引擎不支持事务（也不支持任何形式的锁定）。因此，请勿删除IF EXISTS子句。

您可以通过创建自己的（InnoDB）跟踪表来规避此限制。

- RandomSeed

Answer 3

首先，不应该仅为了一个查询而开启事务。事务是有成本的，只有在确实需要时才付出代价。

其次，动态创建表并不是一个好的想法。它显示了糟糕的模式设计的迹象。

假设你分享的两个查询必须成对执行，最好的方法是开启一个事务。在这种情况下，在第一个查询之前打开事务，并在任何失败使整个操作变得不必要的情况下立即关闭它。 您还可以使用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;

顺便提一下，即使创建了一个交易，只有这两个查询不应该超过30秒。您是否有可能在错误的桶中寻找坏掉的蛋？请考虑这个事实。

- MD. Sahib Bin Mahboob

Answer 4

  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

- alok.kumar

---

这只是减少等待时间导致超时的可能性，而不是减少等待时间或查询被放置在等待队列中的可能性。 - RandomSeed

Answer 5

基本上，DDL操作与事务不兼容。DDL是数据定义语言的缩写，指的是CREATE、ALTER和DROP语句。

尽量避免动态创建表。如果需要，所有表都应手动创建或使用一次性脚本创建。

如果绝对必要，在打开事务之前尝试创建表。

- Cvetan Nenov

---

非常好的观点，这让我想起 CREATE TABLE 会导致隐式提交。需要谨慎考虑。 - RandomSeed