Benefits of table level locking

Question

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.

Edit Its widely considered that table level locking prevents from deadlocks. But how prevention of deadlocks at the cost of concurrency is worthwhile?

Answer 1

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.

Answer 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)

Answer 3

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

Answer 4

Two benefits.

a. Locking tables is an example of a brute-force algorithm for solving the problem of deadlocks. Brute force always works, for suitable values of "works".

Ken Thompson, co-inventor of Unix, is reported to have uttered the epigram “When in doubt, use brute force”. He probably intended this as a ha ha only serious, but the original Unix kernel's preference for simple, robust, and portable algorithms over brittle ‘smart’ ones does seem to have been a significant factor in the success of that OS. Like so many other tradeoffs in software design, the choice between brute force and complex, finely-tuned cleverness is often a difficult one that requires both engineering savvy and delicate esthetic judgment.

b. You will undoubtedly make different decisions as you gain both engineering savvy and esthetic judgment. This does not affect the validity of "a" above.