Analysis of the locking mechanism of MySQL database

In the case of concurrent access, non-repeatable reads and other read phenomena are likely to occur. In order to better cope with high concurrency, blocking, timestamps, optimistic concurrency control (optimistic lock), and pessimistic concurrency control (pessimistic lock) are the main technical methods used in concurrency control.

Lock classification

①, by operation: DML lock, DDL lock

②, by lock granularity: table-level lock, row-level lock, page-level lock

③. Classification by lock level: shared lock, exclusive lock

④. Classification by locking method: automatic lock, display lock

⑤. Classification by usage: optimistic locking, pessimistic locking

Optimistic and pessimistic locking

Optimistic concurrency control and pessimistic concurrency control are the main methods used in concurrency control. Optimistic locking and pessimistic locking are not only used in relational databases, but also have related concepts in Hibernate, Memcache, etc.

Pessimistic lock: also known as pessimistic concurrency control, Pessimistic Concurrency Controller, abbreviated as PCC. Pessimistic locking means that data is locked during the data processing process, which is generally implemented using the database locking mechanism.

Note: To use pessimistic locking in MySQL, you must turn off MySQL autocommit, set autocommit=0. MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will automatically commit the result.

For example: Use the select...for update method to lock the data, that is, to enable the exclusive lock

//0. Start transaction begin;/begin work;/start transaction; (Choose one of the three) //1. Query product information select status from t_goods where id=1 for update;
//2. Generate an order based on product information insert into t_orders (id,goods_id) values ​​(null,1);
//3. Change the product status to 2
update t_goods set status=2;
//4. Commit transaction commit;/commit work;

Pessimistic Lock

Advantages: Pessimistic locking uses the locking mechanism in the database to implement sequential execution of data changes, which is the most effective way

Disadvantages: The locking mechanism will generate additional overhead and increase the chance of deadlock. After a transaction locks data with a pessimistic lock, other transactions will not be able to perform any operations on the locked data except querying. If the transaction execution time is very long, other transactions will have to wait all the time, which will inevitably affect the throughput of our system.

Optimistic Locking

Advantages: Optimistic locking does not lock the database. Any transaction can operate on the data, and verification is only performed when updating. This avoids the disadvantage of reduced throughput caused by pessimistic locking.

Disadvantages: Since optimistic locking is implemented manually, it is only applicable to our own business. If external transactions are inserted, errors may occur.

• Lock mechanism of MySQL common storage engine
• BDB: supports page-level locks and table-level locks, with page-level locks being the default
• InnoDB: supports row-level locks and table-level locks, with row-level locks being the default
• MyISAM & Memory: Both storage engines use table-level locks

Exclusive locks and shared locks in MySQL

Exclusive lock

An exclusive lock is also called a write lock. If transaction T adds an exclusive lock to A, no other transaction can add any type of lock to A. A transaction that is granted an exclusive lock can both read and write data.

Usage: SELECT ... FOR UPDATE

Shared lock

A shared lock is also called a read lock. If transaction T adds a shared lock to A, other transactions can only add a shared lock to A and cannot add other locks. Transactions that are granted shared locks can only read data, not write data.

Usage: SELECT ... LOCK IN SHARE MODE;

Row-level locks, table-level locks, and page-level locks in MySQL

Row-level locks: Row-level locks are divided into shared locks and exclusive locks. Row-level locks are the most fine-grained locks in MySQL. The InnoDB engine supports row-level locks and table-level locks. Row-level locks are used only when data is retrieved through index conditions, otherwise table-level locks are used. Row-level locks have high overhead, slow lock adding, minimum lock granularity, lowest probability of lock conflicts, and highest concurrency.

Table-level lock: Table-level locks are divided into table shared locks and table exclusive locks. Table-level locks have low overhead, fast locking, large locking granularity, the highest lock conflicts, and the lowest concurrency

Page-level lock: A page-level lock is a type of lock in MySQL whose locking granularity is between row-level lock and table-level lock. Table-level locks are fast but have many conflicts, while row-level locks have fewer conflicts but are slow. Therefore, a compromise page level is adopted to lock a group of adjacent records at a time. BDB supports page-level locking.

The overhead and locking time are between table locks and row locks; deadlock may occur; the locking granularity is between table locks and row locks, and the concurrency is average

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.