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.
When I am using PHP to query data like in this example:
$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"?
Thank you, Emi
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:
mysql_query("START TRANSACTION", $dbConnection)
mysql_query("BEGIN", $dbConnection);
Then do your work:
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.
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