MySQL table lock mechanism

How to ensure data consistency, integrity, and effectiveness during concurrent access is a core concern of the database. The database lock mechanism is designed to solve this problem. To a certain extent, the lock mechanism will sort the concurrent access to shared resources to ensure data consistency and integrity. The locking mechanism directly affects the concurrent data processing capability and performance. The implementation of a good lock mechanism is one of the core competitiveness of data.

How to ensure data consistency, integrity, and effectiveness during concurrent access is a core concern of the database. The database lock mechanism is designed to solve this problem. To a certain extent, the lock mechanism will sort the concurrent access to shared resources to ensure data consistency and integrity. The locking mechanism directly affects the concurrent data processing capability and performance. The implementation of a good lock mechanism is one of the core competitiveness of data.

We know that table-level locks, page-level locks, and RoW-level locks exist in MySQL. MySQL implements table-level locks by default. Other locking mechanisms are implemented in different storage engines, which is one of the features of MySQL: suitable storage engines can be used for specific application scenarios. Regardless of the advantages and disadvantages of various storage engines and lock mechanisms, here we will talk about their respective features and implementations.

As the default storage engine, MyISAM uses the default table-level locking mechanism provided by MySQL. Although it does not implement its own lock mechanism, it adds the concurrent insert feature based on the default table-level lock. Concurrent insertion is related to the system parameter concurrent_insert. concurrent_insert has three values:

• Concurrent_insert = 0 disable concurrent write
• Concurrent_insert = 1 (default) enable parallel insert in the MyISAM table without empty data blocks
• Concurrent_insert = 2 enable parallel insert for all MyISAM tables. If the table is empty or is being used by another thread, the new row is inserted to the end of the table. If the table is not used, MySQL locks the normal read and inserts a blank record into the new row.

This parameter is related to the data storage method of the MyISAM storage engine: Under normal circumstances, new data of MyISAM will be appended to the end of the data file. after some DELETE operations, data files are no longer consecutive. in the image aspect, there are a lot of hole in the data file. when new data is inserted, by default, the system checks whether the size of these hole can accommodate new data. If yes, it directly saves the new data to the hole. otherwise, save the new data to the end of the data file. This is done to reduce the size of data files and reduce the generation of file fragments.

If we use concurrent_insert = 2 (this is usually recommended), this will generate a large number of file fragments. Therefore, when Setting this parameter value, regularly perform optimize table operations on the data TABLE. This operation can remove the data file fragments left after the delete operation, reduce the file size, and accelerate future read/write operations. However, during the optimize table operation, MySQL locks the TABLE.

MySQL has two Table Lock modes: Table Read Lock and Table Write Lock ). Shared locks and exclusive locks are very common implementations in the lock mechanism. Before executing a query statement, MyISAM automatically adds a read lock to all the tables involved. before executing the update operation (DDL), MyISAM automatically adds a write lock to the relevant tables. MySQL read/write locks (mysys/thr_lock.c) are maintained through four queues:

• Current read lock queue (lock-> read): stores information about all threads currently holding the read lock, sorted by the time when the lock is obtained.
• Lock wait queue (lock-> read_wait): stores information about the threads waiting for the lock to lock resources.
• Current write lock queue (lock-> write): stores information about all threads currently holding the write lock, sorted by the time when the lock is obtained
• Write lock wait queue (lock-> write_wait): stores information about threads waiting for write lock to lock resources

For read locks, when the requested resource does not have a write lock or there is no higher priority in the write lock wait queue, the write lock is waiting. A read lock is a shared lock that does not block read requests from other processes to the same resource, but blocks write requests to the same resource. Write operations of other processes are performed only after the read lock is released.

For write locks, when the requested resources are in the current write lock queue, write lock wait queue, or the current read lock queue, they enter the waiting write lock queue. write locks block other processes from reading and writing the same resource, read/write operations of other processes are performed only after the write lock is released.

Table locks are the most granular locks in MySQL databases. In addition, MySQL also provides page-level locks and row locks. The execution overhead of table locks is small, the locking speed is fast, and no deadlock occurs. However, the granularity of table locks is large, and the probability of lock conflicts is very high, resulting in low concurrency. You can consider using the master-slave structure to solve the problem of low concurrency.

References

Http://www.zhaokunyao.com/archives/206

Http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html

MySQL Performance Optimization and architecture design-Jian Chaoyang