当从php中选择数据时，MySQL表被锁定了多长时间?

I am currently playing around with MySQL and PHP, and I was wondering about table locks. From the MySQL documentation I know that MySQL applies table wide read locks, which can cause other queries to wait until the select query finishes.

我现在正在玩MySQL和PHP，我想知道关于表锁的事情。从MySQL文档中，我知道MySQL应用了表宽读锁，这会导致其他查询等待到select查询完成。

When I am using PHP to query data like in this example:

当我使用PHP查询数据时，如本例所示:

$dbConnection = mysql_connect($dbHost, $dbUser, $dbPass) 
mysql_select_db($dbName, $dbConnection) 

$qry = "select * from bigTable";
$result = mysql_query($qry, $dbConnection);

//Read Data

mysql_close($dbConnection);

When, from the perspective of the PHP code, will the read lock get removed from "bigTable"?

从PHP代码的角度来看，何时从“bigTable”中删除读锁?

Thank you, Emi

谢谢你,Emi

2 个解决方案

#1

The lock is released at the end of your read query:

锁在读查询的末尾释放:

$result = mysql_query($qry, $dbConnection);

because you are doing per-query transactions.

因为您正在执行每个查询事务。

If you want to do more complex transactions, make sure that you use the innodb engine and start your transaction:

如果您希望进行更复杂的事务，请确保使用innodb引擎并启动您的事务:

mysql_query("START TRANSACTION", $dbConnection)
mysql_query("BEGIN", $dbConnection);

Then do your work:

然后做你的工作:

for example read a table
例如读表
then write some values, etc.
然后写一些值，等等。

and either commit or rollback the transaction:

提交或回滚事务:

mysql_query("COMMIT", $dbConnection);
mysql_query("ROLLBACK", $dbConnection);

depending if you want the changes to go through or not.

这取决于你是否想要改变。

#2

it is locked untill all you selected is read, depending on the table engine the lock ranged from whole table to row entry.

它是锁定的，直到您所选择的全部被读取，根据表引擎，锁定范围从整个表到行入口。

Check innoDB it is on rowlock-level

检查innoDB是否在行锁级别

#1