For the locking mechanism of the MyISAM engine

For the locking mechanism of the MyISAM engine

This article describes the knowledge about the MyISAM engine locking mechanism in the mysql database. For more information, see.

This section describes the locking mechanism of the MyISAM engine.

 

In mysql databases, the MyISAM storage engine is suitable for reading frequencies much higher than write frequencies. Currently, applications may have read/write frequencies in a certain period of time.

 

Roughly as follows: when a client sends a SELECT statement that requires long running, other clients issue an INSERT or UPDATE statement on the same table. This client will wait for the SELECT statement to complete the execution of another SELECT statement on the same table; because UPDATE or INSERT has a higher priority than SELECT, the SELECT statement will wait for UPDATE or INSERT to complete, and will also wait for the first SELECT to complete, that is, the read operation on the MyISAM table, read requests from other users to the same table are not blocked, but write requests to the same table are blocked. Write operations on the MyISAM table are not blocked, the read and write operations on the same table by other users are blocked. The read and write operations on the MyISAM table are serialized!

 

Solution: The MyISAM storage engine has a system variable concurrent_insert, which is used to control its concurrent insert behavior. The values can be 0, 1, or 2, respectively. 0: concurrent operations are not allowed. 1. If the MyISAM table has no holes (that is, the row in the middle of the table is not deleted), MyISAM allows a process to read the table while, another process inserts records from the end of the table. This is also the default setting of MySQL. 2. Whether there are holes in the MyISAM table, you can use -- low-priority-updates to enable mysqld for concurrent insert records at the end of the table. This gives all statements that update (modify) a table with a lower priority than the SELECT statement. In this case, the last SELECT statement in the previous case will be executed before the INSERT statement.

 

Set a low value for max_write_lock_count so that after a certain number of WRITE locks, the READ lock is given and the LOW_PRIORITY attribute is given to a specific INSERT, for lower UPDATE or DELETE priorities, the HIGH_PRIORITY attribute is used to give an insert delayed statement to a specific SELECT statement.

Article reprinted: http://bbs.it-home.org/thread-17004-1-1.html

How can I solve the concurrency problem and transaction processing for a database full of MyISAM storage engines? -Technical Q &

MySQL MyISAM has these internal mechanisms to implement its concurrency control, but it does not support foreign keys
 
What is the MyISAM engine?

MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. (Note that MySQL 5.1 does not support ISAM ).

Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type .. Frm file storage table definition. The data file extension is. MYD (MYData ). The extension of the index file is. MYI (MYIndex ).

To explicitly indicate that you want to use a MyISAM table, use the ENGINE table option:

Create table t (I INT) ENGINE = MYISAM;
Note: MySQL of earlier versions uses TYPE instead of ENGINE (for example, TYPE = MYISAM ). MySQL 5.1 supports this syntax for backward compatibility, but TYPE is currently ignored, and ENGINE is the first usage.

Generally, the ENGINE option is unnecessary. Unless the default value has been changed, MyISAM is the default storage ENGINE.

The following are some features of the MyISAM storage engine:

· All data values are stored in low bytes first. This separates the data machine from the operating system. The only requirement for Binary portability is that the machine uses the complement code (as in the past 20 years) and the IEEE floating point format (also dominated by mainstream machines ). The only machine that does not support binary compatibility is an embedded system. These systems sometimes use special processors.

Low data bytes are stored first, which does not seriously affect the speed. The bytes in the Data row are generally not combined, reading unassociated bytes from one direction does not occupy more resources than reading from the reverse direction. The code for getting the column value on the server is not time-consuming compared with other code.

· Large files (up to 63 characters in length) are supported on file systems and operating systems that support large files.

· When the deletion, update, and insertion are mixed, the dynamic size of rows is less fragmented. This is automatically completed by merging adjacent deleted blocks and extending them to the next block if the next block is deleted.

· The maximum number of indexes in each MyISAM table is 64. This can be changed through recompilation. The maximum number of columns for each index is 16.

· The maximum key length is 1000 bytes. This can also be changed through compilation. When the key length exceeds 250 bytes, a key block exceeding 1024 bytes is used.

· BLOB and TEXT columns can be indexed.

· The NULL value is allowed in the indexed column. This occupies 0-1 bytes of each key.

· All numeric keys are stored in high byte first to allow compression of a higher index.

· When records are inserted in an ordered Order (like when you use an AUTO_INCREMENT column), the index tree is split so that the high node contains only one key. This improves the spatial utilization of the index tree.

· Internal processing of an AUTO_INCREMEN column in each table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10% ). After the value at the top of the sequence is deleted, it cannot be reused. (When the AUTO_INCREMENT column is defined as the last column of the Multi-column index, the deleted value from the top of the sequence can be reused ). The AUTO_INCREMENT value can be reset using alter table or myisamch.

· If the table in the middle of the data file does not have a free block, you can INSERT a new row into the table while other threads read from the table. (This is recognized as a concurrent operation ). The appearance of a free block is the result of deleting a row, or updating a dynamic Length row with more data than the current content. When all free blocks are used up (filled up), future Inserts will become concurrent.

· You can place DATA files and INDEX files in different directories, and use the "create table" option of "data directory" and "index directory" to get more details...>