Benefits of table level locking

Since mysql schedules queries for execution in this manner:

• Write requests (like UPDATEs and DELETEs) take priority over read requests (SELECTs)
• Server performs writes FIFO manner (in the order received)

What are the benefits ?

Deadlocks can never occur with MyISAM. The MySQL server can thus manage all contention, explicit (LOCK TABLEs) or implcit (Any DML).

As long as a MyISAM table has no deleted or updated records, concurrent inserts can freely occur with impunity. That would, indeed, include INSERTs on a table that has an explicit read lock.

For any table with gaps, running OPTIMIZE TABLE would remove those gaps and allow concurrent inserts once again.

For more information, please read "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.

MyISAM does not have deadlocking, but deadlocking in a way, is an improvement over table-level locking.

When you are trying to INSERT/UPDATE/DELETE from a locked table, you will need to wait until it is available or until you get a timeout (by default 28800 seconds). With deadlocking on a row-level locking engine, you wait a bit if it is not free and if the server detects a sort of "infinite loop" - where 2 connections will not give up a row - then it will reject both connections quickly and give a deadlock.

If you are trying to resolve deadlocking, I would suggest you look at the following things:

• Does the deadlock happen on bad code in a transaction? is it really necessary to "hold" a row for you to complete your computation and update it?
• Is there an index for the condition in your statement? otherwise InnoDB might indeed mark the whole table as waiting to be updated.
• Could it be that the harddisk on the server does not commit the InnoDB changes fast enough? does the checkpoint operation on the server cause problems/stalls with InnoDB?
• Is it because of auto increment locking? If so, maybe you should look at a more liberal auto incrementing - setting http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html (also look at row-level locking to resolve some of the problems caused by such changes)

A table-lock (such as used by MyISAM) is free from deadlock issues.