Mysql-detailed explanation of various lock distinctions and MVCC

Mysql seems to have a lot of locks. After checking most of the information,

What table locks, row locks, page locks

Shared lock, exclusive lock, intention lock, read lock, write lock

Pessimistic lock, optimistic lock. .

I'm going, I really want to ask, are there any golden locks? I still have Fan Bingbing. . .

Oh, why do I feel so messed up. So let’s sort it out and summarize it.

There is also an understanding and examples of mvcc under innodb. How to maintain high efficiency and consistency under concurrent access? It’s simple and easy to understand. I heard from my friends that big companies also like to ask this question during interviews.

Table/row/page-lock:

Table-level locking: MyISAM and MEMORY storage engines

Row-level locking (row-level locking): InnoDB storage engine

Page lock (page-level-locking): BDB storage engine

Table-level lock: low overhead, low concurrency, fast locking; no deadlock; large locking granularity , the probability of lock conflict is the highest, and the concurrency is also the lowest.

Row-level locks: high overhead, high concurrency, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency Also the highest.

Page lock: The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average .

Shared/exclusive lock

Shared lock, also known as read lock, is a lock created by a read operation. Other users can read the data concurrently, but no transaction can modify the data (acquire an exclusive lock on the data) until all shared locks have been released.

Exclusive lock is also called write lock. If transaction T adds an exclusive lock to data A, other transactions cannot add any type of blockade to A. Transactions granted exclusive locks can both read and modify data.

Mysiam lock mode

MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT). Before update operations (UPDATE, DELETE, INSERT, etc.), write locks will be automatically added to the tables involved.

a. Reading operations on the MyISAM table (adding read locks) will not block other processes' read requests for the same table, but will block write requests for the same table. This will only happen when the read lock is released. Perform write operations from other processes.

b. Writing operations (adding write locks) to the MyISAM table will block other processes from reading and writing operations on the same table. Only when the write lock is released, will other processes be executed. Read and write operations of the process.

innodb lock mode

Intention lock is automatically added by InnoDB and does not require user intervention.

For insert, update, and delete, InnoDB will automatically add exclusive locks (X) to the data involved; for general Select statements, InnoDB will not add any locks, and the transaction can be done through the following The statement adds a shared lock or an exclusive lock to the display.

Shared lock: SELECT ... LOCK IN SHARE MODE;

Exclusive lock: SELECT ... FOR UPDATE;

##MVCC(Multiversion concurrency control)

A difficult concept to understand, I have consulted a lot of information and blogs, and here is a simple and easy-to-understand explanation.

Scenario simulation:

Under the premise of high concurrency, we must pay attention to this premise.

Transaction L1 modifies the key value of D in a table and has not yet submitted;

Transaction L2 also modifies the key value of D and submits it ;Then L1 submits.

what happened?

L1 reads the value 100 corresponding to key:123 from D

L2 reads 100 corresponding to key:123 from D

L1 increases the value by 1 and updates key:123 to 100 + 1

L2 increases the value by 2 and updates key:123 to 100 + 2

If L1 and L2 are executed serially, the value corresponding to key:123 will be 103, but the execution effect of L1 in the above concurrent execution is completely covered by L2, and the actual value corresponding to key:123 becomes It became 102. Just because the L1 transaction was not submitted, L2 came again.

How to deal with it?

Method 1:

Add a lock. Aren't we all talking about this lock problem before? Add a write lock to it, and wait for L1 to finish executing before executing L2. It's possible, but queuing occurs and concurrency drops. This is a kind of pessimism. Lock-based concurrency control machines are generally called pessimistic mechanisms.

Method 2:

In order to achieve serializability while avoiding the lock mechanism For various existing problems, we can use the lock-free concurrency mechanism based on the idea of ​​multiversion concurrency control (MVCC), and finally what I want to say is drawn out! People generally call the lock-based concurrency control machine pessimistic mechanism (pessimistic lock), while mechanisms such as MVCC are called optimistic mechanism (optimistic lock). A mechanism to add a version number. D maintains the version number. Every time the data is updated, the version number is increased. The version number can be used to manage transaction consistency and high concurrency issues more efficiently.

Because the lock mechanism is preventive, reading will block writing, and writing will also block reading. When the locking granularity is larger and the time is longer, it is concurrency. The performance will not be very good; and MVCC is a posteriori. Reading does not block writing, and writing does not block reading. It waits until submission to check whether there is a conflict. Since there is no lock, reading Writes do not block each other, thus greatly improving concurrency performance.

The above is the detailed explanation of Mysql-various lock distinctions and MVCC. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!