(5) mysql-optimized MyISAM Table lock and mysqlmyisam

(5) mysql-optimized MyISAM Table lock and mysqlmyisam
Overview

The MyISAM storage engine only supports table locks. There are two modes for mysql table locks: Read locks and write locks. Their compatibility relationship is (read operations on myisam will not block read requests from other users to the same table, but will block write operations on the same table *) and (write operations on myisam will block read and write operations on the same table by other users). Read and Write operations are sequential.

How to add a table lock

Before executing a query statement (select), MyISAM automatically adds a read lock to all tables involved. Before performing update operations (such as update, delete, and insert), a write lock is automatically applied to the involved tables. This process does not require user intervention. To display the lock, see the link:
Http://blog.csdn.net/pursuing0my0dream/article/details/45166975
Note:

• When the lock tables option is added with the 'local' option, its function is to allow other users to insert records concurrently at the end of the MyISAM table under the condition that the table is inserted concurrently.
• When lock tables explicitly adds a table lock to a table, all the locks related to the table must be obtained at the same time, and MySQL does not support lock upgrade. That is, after you execute lock tables, you can only access the explicitly locked tables, but not the unlocked tables. MyISAM always gets all the locks required by SQL statements at a time. This is why the MyISAM table does not have a deadlock.
• When you use lock tables, you not only need to lock the table used once, but also the number of times that the same table appears in an SQL statement, the number of times that table will be locked in the same alias.

Concurrent Inserts

Under certain conditions, MyISAM tables support query and insert concurrent execution. MyISAm has a system variable concurrent_insert, which is used to control its concurrent behaviors. 0-insert is not allowed; 1-if the table does not have holes, you can insert records at the end of the table. This is the default mysql setting; 2-whether there is holes or not, you can insert records at the end of the table.

Lock Scheduling

The read and write locks of myISAM are mutually exclusive. When a process requests the Lock of a MyISAM table, the other process also requests the write lock of the same table. How does MySQL handle it? The result is to write the process first and then read the process. This is because mysql considers that write requests are generally more important than read requests. We can change the lock processing sequence through some settings:

• By enabling the low-priority-updates parameter, the MyISAM engine gives priority to read infringement by default.
• Set low_priority_updates = 1 to lower the priority of the update request sent by the connection.
• You can reduce the priority of an insert, update, or delete Statement by specifying the low_priority attribute.
• You can also set max_write_lock_count. When the read lock of the table reaches this value, mysql will temporarily lower the Write Request priority and give the read process a chance to get the lock.