(5) MySQL optimized MyISAM table lock

Overview

?? The MyISAM storage engine supports only table locks, and there are two modes of table lock in MySQL: Read and write Locks. Their compatibility relationship is (read operations to MyISAM, does not block other users ' read requests to the same table, but blocks writes to the same table *) and (writes to MyISAM, which blocks other users from reading and writing to the same table), and read and write operations are serial.

How to add a table lock

?? MyISAM will automatically add read locks to all the tables involved before executing the query statement (SELECT). Before the update operation (Update,delete,insert, etc.), the involved table is automatically added to the write lock, the process does not require user intervention. If you want to display the lock, see the link:
http://blog.csdn.net/pursuing0my0dream/article/details/45166975
Description

• Lock tables plus the ' local ' option, which is the function of allowing other users to insert records concurrently at the end of the table, while satisfying the MyISAM table concurrency insertion condition.
• When lock tables explicitly adds a table lock to the table, all locks involving the table must be obtained at the same time, and MySQL does not support lock escalation. That is, after you execute lock tables, you can access only those tables that are explicitly locked, and you cannot access unlocked tables. MyISAM always gets all the locks required by the SQL statement at once. This is why the MyISAM table does not appear to be deadlocked.
• When you use lock tables, you not only need to lock the table once, but how many times the same table appears in the SQL statement, and how many times are locked in the same alias.

Concurrent Insertions

Under certain conditions, the MyISAM table supports querying and inserting concurrent execution. The MyISAM has a system variable Concurrent_insert that is specifically designed to control its concurrency behavior. 0-no insertion is allowed; 1-if the table is not empty, the record is allowed to be inserted at the end of the table, which is the default MySQL setting, and 2-the record is allowed to be inserted at the end of the table, regardless of the hole.

Lock Scheduling

MyISAM read lock and write lock are mutually exclusive, read and write serial. Then, when a process requests a lock on a MyISAM table, the other process also requests a write lock on the same table. How does MySQL handle it? The result is a write-ahead read process. This is supposed to be important for MySQL to consider writing requests generally more than read requests. There are some settings that we can use to change the order of lock processing:

• By starting the parameter low-priority-updates, the MyISAM engine is given priority rights to read the infringement by default.
• By using set Low_priority_updates=1, the priority for update requests made by this connection is reduced.
• Reduce the priority of the statement by specifying the Low_priority property of the Insert,update,delete statement.
• You can also set Max_write_lock_count. When the table reads the lock to this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to get a lock.

(5) MySQL optimized MyISAM table lock