Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the principle of MySQL locking mechanism". In daily operation, I believe that many people have doubts about what the principle of MySQL locking mechanism is. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what is the principle of MySQL locking mechanism?" Next, please follow the editor to study!

In many cases, experience can be used to guess what kind of lock is more appropriate for the application, but it is usually difficult to say that one lock is better than the other, it all depends on the application, and different locks may be required in different places.

To decide whether or not to use a storage engine that supports row-level locks, look at what the application does and how the query and update statements are used. For example, many web applications do a lot of queries and rarely delete, mainly index-based updates, inserting records into specific tables only. It is appropriate to use a basic MySQLMyISAM table.

Deadlocks are released in MySQL for the storage engine of table-level locks. Deadlock avoidance can be done by requesting a lock before any query and locking the table in the order in which it is requested.

The implementation mechanism of table lock for WRITE (write) in MySQL is as follows:

If the table is not locked, add a write lock.

Otherwise, put the request in the write lock queue.

The implementation mechanism of table lock for READ (read) in MySQL is as follows:

If the table does not have a write lock, then add a read lock.

Otherwise, put the request in the read lock queue.

When the lock is released, the thread in the write lock queue can use the lock resource, and then it is the turn of the thread in the read lock queue.

That is, if there are many updates in the table, Select must wait until all the updates are complete.

Starting with MySQL3.23.33, you can analyze lock table contention in the system through the state variables Table_locks_waited and Table_locks_immediate:

Mysql > SHOWSTATUSLIKE'Table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 1151552 | |

| | Table_locks_waited | 15324 | |

+-+ +

What exactly is the MySQL locking mechanism?

After MySQL3.23.7 (3.23.25 on Windows), as long as there are no conflicting Insert operations in the MyISAM table, you can freely execute Insert and Select statements in parallel without using a lock table. That is, you can insert a new record while another client is reading the MyISAM table record. If there are no free blocks in the middle of the data file, there will be no conflict, because in this case all new records will be written at the end of the data file (when deleting or updating in the middle of the table, it can lead to holes). When the hole is filled with new data, the parallel insert feature is automatically re-enabled.

If you want to do a lot of Insert and Select operations on a table, but parallel inserts are not possible, you can insert records into the temporary table, and then periodically update the data in the temporary table to the actual table. You can do this with the following command:

Mysql > LOCKTABLESreal_tableWRITE,insert_tableWRITE

Mysql > InsertINTOreal_tableSelect*FROMinsert_table

Mysql > TRUNCATETABLEinsert_table

Mysql > UNLOCKTABLES

InnoDB uses row-level locks and BDB uses page-level locks. For InnoDB and BDB storage engines, deadlocks can occur. This is because InnoDB automatically catches row locks, and BDB captures page locks when the SQL statement is executed, not at the beginning of the transaction.

A lot of scanning tables and GROUPBY operations on the whole table, but there is no writing to the table.

The difference between table-level locks and row-level or page-level locks is also:

Versions will be made with one write and multiple reads at the same time (such as concurrent inserts in MySQL). That is, the database / table supports a variety of attempts depending on the point in time when you start accessing the data. Other names are: time schedule, write copy, or copy on demand.

Versioning (suchasweuseinMySQLforconcurrentinserts) whereyoucanhaveonewriteratthesametimeasmanyreaders.Thismeansthatthedatabase/tablesupportsdifferentviewsforthedatadependingonwhenyoustartedtoaccessit.Othernamesforthisaretimetravel,copyonwrite,orcopyondemand.

On-demand replication is in many cases much better than page-level or row-level locks. However, in the worst case, more memory is used than other normal locks.

You can replace row-level locks with application-level locks, such as GET_LOCK () and RELEASE_LOCK () in MySQL. But they are advice locks (original: Theseareadvisorylocks), so they can only be used in secure and trusted applications.

At this point, the study of "what is the principle of MySQL locking mechanism" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.