MySQL Database lockup

Question

This is rather a general question. If I run an SQL query and loop through the results. Would the database be locked whilst I was looping through these results and prevent further queries / inserts?

Also, if I was to send 5 or 6 insert statements to the database at the same time (via different calls), would there be a lockup?

I am having an issue where some of the logs I am meant to be inserting into the database are not there so I wanted to investigate this route.

I am using PHP 5 and lastest MySQL (can't remember version).

Thanks.

Answer 1

There is a difference between "lock" and "corruption"

Database Lock is something which database does to prevent data corruption. When two simultaneous DML (insert / update / delete) queries are encountered by the database, it will lock the related table(s). Now, there could be "row-level" locking OR "table-level" locks

What happens in the locking is that the table is locked and all the subsequent queries are queued until the current query is executed by the database. If its a row level locking, depending upon the database multiple updates are allowed simultaneously.

SELECT queries:

When you loop through your result set, there are no more calls being made to the database (while in the loop) . The result is already generated. Thus, it will not affect the database

Answer 2

It can depending on the engine. MyISAM does table locking whereas InnoDB does row locking.

http://dev.mysql.com/doc/refman/5.0/en/table-locking.html