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?

  • This discussion may have some relevant info for you. Sep 14, 2011 at 20:57
  • This discussion is about MyISAM vs InnoDB characteristics nothing is there about benefits of table level locking.
    – Rick James
    Sep 14, 2011 at 21:05

4 Answers 4


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)

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.

  • This way deadlock will never occur but where comes its benefit when compared with its disadvantages like no concurrency. And on high load slow throughput of the database.
    – Rick James
    Sep 14, 2011 at 21:10
  • 2
    @rickjames the benefit of myisam isn't in how it manages updates. It's best in a non-write intensive situation. Sep 14, 2011 at 21:13
  • Unfortunately, loads of people use WordPress, Drupal, and MoveableTypes CMS's, whose default is always MyISAM. If anyone has a high-trafficked website, they have to get away from MyISAM. Otherwise, MyISAM should suffice. Sep 14, 2011 at 21:13
  • @DTest right, but even in read intensive system how its beneficial compared with row level locking?
    – Rick James
    Sep 14, 2011 at 21:14
  • Even in a read-intesive system, just one DELETE or UPDATE statement will quickly nullify whatever benefits MyISAM has. Sep 14, 2011 at 21:16

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

  • BTW +1 for answering first on deadlocking !!! Sep 14, 2011 at 21:17

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.