Mysql concurrency: what happens if a locked table is accessed?

Question

The question is rather simple but I couldn't find a precise answer: In a myisam db, what happens if a php file locks a table (with an atomic operation, say an INSERT) and another php file tries to access the same table (reading or writing)?

Now, while it is obvious that the second session will not be able to access the table, what exactly happens? Does it return some kind of error? Does it wait in queue until it is able to access it?

Saponin

Answer 1

The second connection will wait for the lock to free.

With MyISAM any write (insert / update / delete) will lock the table,

However with INNODB table type the atomic operation will only lock the affected rows

Lanettelaney

Perfect. Is there some kind of timeout for the second connection? – Saponin

Whatever connection timeout defined in the mysql config OR the application you are using (for example php.ini mysql timeout is 60 seconds). The connection will wait until this time, then drop (obviously) – Lanettelaney

Well .. with InnoDB you can lock the whole table too. But then it is for making snapshot of that table. – Moulding