Continue answering questions from planet Water:
MyISAM only supports table locking, but it is better to use MyISAM when the number of concurrent inserts is large. Is this contradictory?
MySQL > alter table lock MySQL > alter table lock
_ Voiceover: _ Many articles on the Internet only say the conclusion, do not say why, easy to make people confused.
MySQL table lock knowledge
Which storage engines use table locks?
MySQL, except InnoDB which supports row locking, other storage engines of MySQL only use table locking, such as: MyISAM, MEMORY, MERGE, etc.
What are the benefits of table locking?
(1) Table locks occupy much less memory, and the number of row locks is related to the number of row records, which consumes a lot of memory;
(2) If the business frequently reads and writes a large portion of the table, the table lock is faster because only one lock is involved instead of managing N locks at the same time;
(3) If the business frequently uses group by, the table lock will be faster, for the same reason as (2);
_ Voiceover: _ For some scenarios like this, MyISAM is better than InnoDB.
How does table locking work?
Similar to read/write locks for other critical resources.
When writing, add write lock:
(1) If the table does not lock, add write lock to the table;
(2) Otherwise, the write lock queue is entered;
To read, add a read lock:
(1) If there is no write lock on the table, add read lock on the table;
(2) Otherwise, access lock queue;
Table lock release:
If there are locks in both the write lock queue and the read lock queue, the write lock queue has higher priority, that is, the write lock queue exits first. The reason for this is that if there is a “big query”, it can cause write locks to starve to death in batches, and write locks tend to be released quickly.
The subtext is that if there are a large number of concurrent UPDATE requests, select will wait until all update requests have been executed.
How do I check table locks?
If you want to analyze table lock conflicts, view:
Table_locks_immediate: the number of times the table lock is obtained immediately;
Table_locks_waited: number of waits for table lock;
These two variables.
Use the following command to view:
show status like ‘Table%’;
If the number of waits for table locks is large, table locks may be a potential bottleneck.
Why is MyISAM a good use for concurrent insertions? Won’t ** reduce throughput due to frequent table lock collisions?
_ Voiceover: _ Systematization of knowledge is more important than answers to questions.
Knowledge 1:
MyISAM’s index is separated from the record store, with a separate area to store row records, and PK is a non-clustered index.
This knowledge point will not expand, “database, primary key why not too long?” Just talked about.
Knowledge 2:
MyISAM tables, if data files are tightly stored and there are no free blocks in between, always insert data to the end of the data file, just like appending logs, which performs very well. Concurrent inserts and select are lock-free.
As shown above:
(1) Data files are stored continuously and closely;
(2) Concurrent insert without table lock contention (only insert queue mutually exclusive);
(3) Insert only occurs at the end of the data file;
(4) Concurrent select can also be performed simultaneously (shared read lock);
Knowledge 3:
MyISAM table, if there is a hole in the data file, the above mechanism will be disabled until the hole is filled with new data, and the unlocked mechanism will be enabled again.
How do holes come about?
Deleting or modifying data can result in voids.
As shown above:
(1) Some data is deleted in the middle, leading to free blocks in the middle;
(2) There will be a table lock conflict between select and INSERT.
As shown in the figure above:
(1) As the insert progresses, the free block in the middle is filled again;
(2) At this point, the concurrent SELECT and INSERT resume;
conclusion
Although MyISAM only supports table locking, the above mechanism makes MyISAM’s table locking performance very strong in high concurrent SELECT and INSERT business scenarios.
This article is based on MySQL5.6.
I hope that answers the question of our water friend.