MySQL lock overview
Compared with other databases, the locking mechanism of MySQL is relatively simple. The most significant feature of MySQL is that different storage engines support different locking mechanisms.
Such as:
Table-level locking is adopted by MyISAM and MEMORY storage engines. InnoDB storage engine supports row-level locking and table-level locking, but row-level locking is used by default. The features of the two main types of MySQL locks can be summarized as follows:
MySQL MySQL row lock, table lock, pessimistic lock, optimistic lock features and applications: low overhead, fast lock; There are no deadlocks (because MyISAM acquires all the locks required by SQL at once); The lock granularity is large, and the probability of lock conflict is high and the concurrency is low. Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. Page locks: the overhead and speed of locking is between table and row locks. Deadlocks occur; The lock granularity is between table lock and row lock, and the concurrency is general row lock and table lock
1. Lock granularity is mainly divided into row lock, table lock and library lock
(1) Row lock: when accessing the database, lock the entire row of data to prevent concurrent errors.
(2) Table lock: when accessing the database, lock the entire table data to prevent concurrent errors.
2. Row locking and table locking:
Table lock: small overhead, lock fast, will not appear deadlock; Large lock intensity, high lock conflict probability, lowest concurrency row lock: high overhead, slow lock, deadlock; Small lock granularity, low probability of lock conflict, high concurrency pessimistic lock and optimistic lock
(1) Pessimistic lock: just as the name implies, it is very pessimistic. Every time I go to get the data, I think others will modify it, so every time I get the data, I lock it, so that others will block the data until it gets the lock.
Traditional relational database inside used a lot of this locking mechanism, such as row lock, table lock, read lock, write lock, etc., are in the operation before the first lock.
(2) Optimistic lock: as the name implies, it is very optimistic. Every time I go to get the data, I think that others will not modify it, so I will not lock it. But when UPDATING, I will judge whether others have updated the data during this period, and I can use the version number and other mechanisms.
Optimistic locks are suitable for multi-read applications to improve throughput. For example, if a database provides a mechanism similar to write_condition, it will provide optimistic locks.
(3) The difference between pessimistic lock and optimistic lock:
The two kinds of locks have their own advantages and disadvantages, and one can not be considered better than the other. For example, optimistic lock is suitable for the situation with less write, that is, when conflicts really rarely occur, which can save the lock overhead and increase the overall throughput of the system. However, if there are frequent conflicts, the upper application will continue to retry, which can degrade performance, so pessimistic locking is appropriate in this case.
A Shared lock
A shared lock means that the same lock is shared on the same resource for multiple different transactions. It’s like having multiple keys to the same door. You have one key, your girlfriend has one key, and you can all use this key to get into your house. This is called a shared lock.
Shared lock is also a kind of pessimistic lock, so shared lock in mysql is called through what command? By querying the data, we learned that by adding lock in share mode to the end of the execution statement, it means that some resources are locked with a share.
When to use table locks
For InnoDB tables, row-level locking should be used in most cases, because transactions and row locking are often the reasons why we choose InnoDB tables. However, table level locking can also be considered for specific transactions.
The first case is: the transaction needs to update most or all of the data, and the table is large. If the default row lock is used, the transaction execution efficiency is low, and other transactions may be locked for a long time and lock conflicts. In this case, table locks can be considered to improve the execution speed of the transaction. In the second case, transactions involving multiple tables are complex and likely to cause deadlocks and a large number of transactions to roll back. In this case, you can also consider locking the tables involved in a transaction once to avoid deadlocks and reduce database overhead due to transaction rollback. Of course, you should not have too many of these two types of transactions in your application, otherwise you should consider using MyISAM tables.
Table and row locks are used in the following scenarios:
Table level locking is not used and concurrency is not high, mainly query, a small number of updated applications, such as small Web applications; However, row-level locking is suitable for systems with high concurrency and high requirement on transaction integrity, such as online transaction processing system. These are the characteristics and application scenarios of row locks and table locks.