4

The Table in MySQL is MyISAM.

Now i want to select many data.

$res = $db->query("SELECT ...");
// -- Break 1
while($row = $res->fetch_assoc()) {
 //working with the row;
}
// -- Break 2

When does the lock on the table ends? On Break 1 or Break 2?

StrongLucky
  • 568
  • 5
  • 15
  • 2
    This is a good and in-depth read about table-locking: http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html – Jay Blanchard Sep 05 '14 at 13:30
  • Maybe profiling SQL give some idea about when the mysql freeing table.[Details](http://dev.mysql.com/doc/refman/5.5/en/show-profile.html) – Santa's helper Sep 05 '14 at 13:34
  • 1
    Maybe profiling SQL give some idea about when the mysql freeing table.[Details](http://dev.mysql.com/doc/refman/5.5/en/show-profile.html) i think its at the Break1 becouse your query parsed executed and returned the resultset resource, acording to Mysql your result set has been setted so meanwhile inserts or updates sould not change your result set – Santa's helper Sep 05 '14 at 13:40
  • Please explain your comment @sectus. – Jay Blanchard Sep 05 '14 at 14:05
  • I agree @Santa'shelper, the lock should be released when the query returns the results based on what I have read so far. – Jay Blanchard Sep 05 '14 at 14:07
  • @JayBlanchard, as I know SELECT does not lock MyISAM tables. – sectus Sep 05 '14 at 14:12
  • From the documentation @sectus "MySQL uses *table-level locking* for **MyISAM, MEMORY, and MERGE tables**, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications." It places a "read lock" on the table during a SELECT and there can be multiple read locks. http://stackoverflow.com/a/6415256/1011527 – Jay Blanchard Sep 05 '14 at 14:19
  • @JayBlanchard, MyIsam has locking mechanism, but select does not lock table. – sectus Sep 05 '14 at 14:22
  • Ok, so far.. SELECT only locks the rows. But the problem is: The INSERTS and UPDATES get done when all Locks are closed.. And the Tale get full locked on INSERT / UPDATE. Hope this is correct. – StrongLucky Sep 06 '14 at 12:17
  • Regarding PHP (not especially the lock): Please note that PHP may work "buffered" or "unbuffered". When working "buffered", PHP will retrieve the results in the background - and fetch_assoc() can fetch the rows while the query is already finished. When working "unbuffered", the query will remain active unless the last row was fetched (which may be a bit later, depending on what your while loop is doing. – BurninLeo Sep 09 '14 at 08:56

2 Answers2

0

@bauer as far as i have understood your question. The answer near to me is whenever you are working with while loop and you have a resource object or variable on which you iterate, its only done once on the page, because once the iteration is complete the object or the respective variable would get free and contains null, so in order to run bundle of while loops you need to define different obj each irrespective to your result set.

Haisum Usman
  • 518
  • 5
  • 13
0

// -- Break 1

The data loads during the process before this line. Table is released.

Further data manipulation from this point is in the PHP application and no longer requires data from the DB.

As for the earlier comment that there is no data lock, that depends on your point of view. If your query returns two records quickly, it would appear there is no lock (technically incorrect, but easily mistaken). But if your query is complex and your result set huge, which requires some serious time to return results (on the order of 45 seconds, for instance) it will become painfully obvious that there is indeed a table lock in MyISAM. A simple "show processlist" during a 45 second return clearly shows locked tables. (Waiting for table level lock is kind of a dead giveaway). Happens every day on larger Vicidial installations.

Indexing the table can often reduce the lock time. Setting "limit 50" (some number to limit the resulting data set) can reduce the lock time if that's a viable option for the query as well.

Also do note that the locking has some methods of bypass depending on the query trying to "play through". Selects vs inserts vs updates can often bypas one another (that's where the studying of the locking mechanisms can come in handy). And testing is a good idea to be sure you understand it. Never fear creating a table with 30 million records in it and running a huge query to see what locks and what does not between select/insert/update while one of them is huge and another is tiny and trying to "play through" while the big one is running. Results can surprise you even if you think you "got it". And then watch for the ability to explicitly state that a query is less important and what happens when the subsequent queries rotate their types.

TheSatinKnight
  • 696
  • 7
  • 16