Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
This discussion may have some relevant info for you. –  Nick Chammas Sep 14 '11 at 20:57
    
This discussion is about MyISAM vs InnoDB characteristics nothing is there about benefits of table level locking. –  Rick James Sep 14 '11 at 21:05
add comment

4 Answers

up vote 3 down vote accepted

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)
share|improve this answer
add comment

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.

share|improve this answer
    
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 '11 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. –  Derek Downey Sep 14 '11 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. –  RolandoMySQLDBA Sep 14 '11 at 21:13
    
@DTest right, but even in read intensive system how its beneficial compared with row level locking? –  Rick James Sep 14 '11 at 21:14
    
Even in a read-intesive system, just one DELETE or UPDATE statement will quickly nullify whatever benefits MyISAM has. –  RolandoMySQLDBA Sep 14 '11 at 21:16
show 2 more comments

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

share|improve this answer
    
BTW +1 for answering first on deadlocking !!! –  RolandoMySQLDBA Sep 14 '11 at 21:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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