patternsqlMinor
Benefits of table level locking
Viewed 0 times
levelbenefitstablelocking
Problem
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?
Edit
Its widely considered that table level locking prevents from deadlocks. But how prevention of deadlocks at the cost of concurrency is worthwhile?
Solution
Since mysql schedules queries for execution in this manner:
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.
- 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.
Context
StackExchange Database Administrators Q#5780, answer score: 6
Revisions (0)
No revisions yet.