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

This article mainly explains the "introduction to the principle of the lock mechanism of MySQL". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the introduction of the lock mechanism of MySQL".

MySQL locks come in the following forms:

Table-level lock: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict and the lowest concurrency. MyISAM engines fall into this type.

Row-level institute: high overhead, slow locking, deadlock will occur, lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest. InnoDB engines fall into this type.

Page lock: the overhead and locking time are between table lock and row lock, and deadlock occurs. The locking granularity is between table lock and row lock, and the concurrency is general. NDB belongs to this type.

Table lock:

The MyISAM storage engine only supports table locks, so when operating on MyISAM tables, there are the following situations:

1) the read operation on the MyISAM table (adding a read lock) will not block the read operation of other processes to the same table, but will block the write request to the same table. Only when the read lock is released will the write operation of other processes be performed.

2) the write operation to the MyISAM table (adding a write lock) will block the read and write operations of other processes to the same table, and the read and write operations of other processes will be performed only when the write lock is released.

Row lock:

The InnoDB storage engine is implemented by locking the index items on the index, which means that InnoDB will use row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks. In the case of high concurrent access, if a large number of transactions hang because they can not get the locks they need immediately, they will occupy a lot of computer resources, cause serious performance problems, and even drag down the database. At this time, you need to set an appropriate lock waiting timeout threshold parameter InnoDB_lock_wait_timeout to solve the problem, which is generally set to 100 seconds.

Deadlock:

Both transactions need to obtain the exclusivity held by the other in order to continue to complete the transaction, and this loop waits for a typical deadlock.

After a deadlock occurs, InnoDB usually automatically detects that one transaction releases the lock and rolls back, while another transaction color acquires the lock and continues to complete the transaction. Deadlocks are inevitable, and we can minimize the probability of deadlocks by adjusting the logic of the business.

Thank you for your reading. the above is the content of "introduction to the principle of lock mechanism of MySQL". After the study of this article, I believe you have a deeper understanding of the introduction of the principle of lock mechanism of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.