编程知识 cdmana.com

The interviewer asked me if I knew about the MySQL lock, and he was impressed by the next 15 minutes

Preface

We know , Data is also a resource for many users to share and access .

How to ensure the consistency of data concurrent access 、 effectiveness , It's a problem that all databases have to solve , Lock conflict is also an important factor affecting the concurrent access performance of database .

From this point of view , Lock is very important for database . This article will lead you to a deeper understanding of Mysql All kinds of styles of locks .

Table locks

The watch lock is mysql The lock with the largest granularity , Indicates that the current operation locks the whole table , Less resource overhead than row locking , There will be no deadlock , But the probability of lock conflict is very high .

The most important feature of this locking mechanism is that the implementation logic is very simple , It has the least negative impact on the system .

The lock is quick to get and release . Because a table level lock locks the entire table at a time , So it can be very good to avoid the deadlock problem that bothers us .

The watch lock is mostly mysql Engine support ,MyISAM and InnoDB Table level locks are supported .

MyISAM It only supports table lock , So the performance is relative Innodb Relatively low , and Innodb Watch lock is also supported , But the default row lock , And only in queries or other SQL Statement will use row lock through index .

Row lock

The line lock is mysql A lock with the smallest granularity , Because the granularity of lock is very small , So the probability of resource contention is the least , Concurrency is the most powerful , But it can also cause deadlock , The cost of adding and releasing locks will also increase .

Right now it's mainly Innodb Use row lock ,Innodb It's also mysql stay 5.5.5 The storage engine used by default after version .

The row lock also shares the lock according to the way of use (S Lock or read lock ) And lock it (X Lock or write lock )

Shared lock (S lock , Read the lock )

Instructions : If business A For data objects 1 add S lock , The transaction A Can read data objects 1 But it can't be modified , Other transactions can only deal with data objects 1 Add S lock , Instead of X lock , Until transaction A Release data objects 1 Upper S lock .

This ensures that other transactions can read data objects 1, But in business A Release data objects 1 Upper S Data object cannot be locked before 1 Make any changes .

usage :

select ... lock in share mode;

---- Shared lock is that multiple transactions can share a lock for the same data , All have access to data , But it can only be read but not modified .

Exclusive lock (X lock , Write lock )

*

Instructions : If business A For data objects 1 add X lock , Business A Can read data objects 1 You can also modify data objects 1, No more data objects for other transactions 1 Add any locks , Until transaction A Release data objects 1 The lock on the .

*

This ensures that other business is in business A Release data objects 1 The data object cannot be read or modified before the lock on 1.

select ... for update
---- Exclusive lock is that it can't coexist with others , For example, a transaction acquires an exclusive lock of a data row , Other transactions can no longer acquire other locks of the row 

Intention sharing lock (IS) And the intention to lock it (IX)

paraphrase :

Intention sharing lock (IS): The transaction wants to get a shared lock on some records in the table , You need to add intention sharing lock to the table first .

Intent exclusive lock (IX): The transaction wants to get the mutex of some records in the table , You need to add an intentional mutex to the table first .

Intention sharing lock and intention exclusive lock are called intention lock . Intention locks appear to support Innodb Support for multi granularity lock .

First , Intent locks are table level locks .

reason : When we need to lock a watch , We need to judge whether the data row in the table is locked according to the intention lock , To determine if it will add success .

If the intention lock is a row lock , Then we have to traverse all the data rows in the table to judge . If the intention lock is a table lock , Then we can directly judge whether any data row in the table is locked at one time .

So it is much better to set the intent lock to table level than row lock .

After having intention lock , The transaction in the previous example A Lock in the application line ( Write lock ) Before , The database will automatically give transactions first A Exclusive application .

When a transaction B To apply for the write lock will fail , Because there is an intention on the table to exclude other affairs after locking B The write lock of the application form will be blocked .

therefore , The purpose of intention lock is :

When a transaction needs to acquire the lock of resources , If the resource has been occupied by an exclusive lock , Then the database will automatically request an intention lock for the transaction . If you need a sharing lock , Apply for an intention sharing lock .

If you need a line ( Or something ) The exclusive lock of , Apply for an intentional exclusive lock .

Optimism lock

Optimistic locks are not included in the database , We need to achieve it ourselves .

Optimistic lock refers to the operation of the database ( update operation ), The idea is optimistic , Think this operation will not lead to conflict , When operating data , Without any other special treatment ( That is, no lock ), And after the update , To judge whether there is a conflict .

Usually the implementation is like this : When the data in the table is operated ( to update ), First, add a version to the data table (version) Field , Every time you operate , Add the version number of that record to 1.

That is to find out the record first , Take out version Field , If you want to operate on that record ( to update ), Then judge the moment first version Whether the value of is the same as the value of version The values are equal , If equal , It means that during this period , There is no other program to operate it , Then you can perform the update , take version Add... To the value of the field 1;

If you find out when you update version Value and just get out of version The values of , It means that there are other procedures to operate it during this period , Do not update .

Using examples :

1. SELECT data AS old_data, version AS old_version FROM …;

2.  Business operations based on the data obtained , obtain new_data and new_version

3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {
//  Optimistic lock gets success , Operation is completed 
} else {
//  Optimistic lock acquisition failed , Roll back and try again 
}

advantage

As can be seen from the above example , Optimistic locking mechanism avoids database locking overhead in long transactions , It greatly improves the overall performance of the system under large concurrency .

shortcoming

Optimistic locking mechanism is often based on the data storage logic in the system , Therefore, it also has some limitations , As in the example above , Because optimistic locking mechanism is implemented in our system , Update operations from external systems are not controlled by our system , As a result, dirty data may be updated to the database .

In the system design stage , The possibility of these situations should be fully considered , And adjust accordingly ( If optimistic locking strategy is implemented in database stored procedure , Only data update channels based on this stored procedure are open to the public , Instead of exposing database tables directly to the public ).

summary : Read with optimistic lock , Write with pessimistic lock .

Pessimistic locking

Pessimistic lock introduction ( From encyclopedia ):

*

Pessimistic locking , As its name suggests , It refers to the data being exposed to the outside world ( Includes other current transactions of the system , And transactions from external systems ) The revision was conservative , therefore , In the whole data processing process , Lock the data . Pessimistic lock implementation , It often relies on the locking mechanism provided by the database ( Only the locking mechanism provided by the database layer can truly guarantee the exclusivity of data access , otherwise , Even in this system to implement the locking mechanism , There is no guarantee that external systems will not modify the data ).

*

Pessimistic lock implementation : When the pessimistic lock is realized first , We must first use set autocommit=0; close mysql Of autoCommit attribute . Because we need to lock the data after we find it .

After turning off auto submit , We need to start the transaction manually .

//1. Start business 
begin;  perhaps  start transaction;

//2. Search out the product information , And then through for update Lock data to prevent other transactions from modifying 
select status from t_goods where id=1 for update;

//3. Generate orders based on product information 
insert into t_orders (id,goods_id) values (null,1);

//4. Modify the goods status by 2
update t_goods set status=2;

//4. Commit transaction 
commit-- completion of enforcement , Commit transaction 

The pessimistic lock is realized , A pessimist lock is a pessimist , It will think that we are in business A Operation data in 1 When , There must be something B To modify the data 1, therefore , In the 2 Step we will query the data and add the exclusive lock directly (X) lock , Prevent other transactions from modifying transactions 1, Until we commit after , Just released the lock .

advantage

Ensure the security of data processing .

shortcoming

Adding locks increases overhead , And increased the chance of deadlock . Reduced concurrency .

Optimism lock Updates may fail , Even the update failed several times , It's risky . So if you write mostly , The demand for throughput is not high , Can use pessimistic lock .

The next three locks are innodb The row lock , As we said before, row locking is based on index , Once the lock operation does not operate on the index , It degenerates into a watch lock .

Clearance lock (Next-Key lock )

Clearance lock , On a non unique index , Main purpose , To prevent other transactions from inserting data in the interval , To cause “ It can't be read repeatedly ”.

If you downgrade the isolation level of a transaction to read commit (Read Committed, RC), The clearance lock will automatically fail .

Pictured :(1,4),(4,7),(7,11),(11,∞) It is the position to lock the clearance lock .

Illustrate with examples :

SELECT * FROM table WHERE id = 8 FOR UPDATE;
---- here ,(7,11) It will be locked 

SELECT * FROM table WHERE id BETWEN 2 AND 5 FOR UPDATE;
---- here ,(1,4) and (4,7) It will be locked 

Record locks

Record locks , It blocks index records , On a unique index , As shown in the figure below : 

select * from t where id=4 for update;

It will be id=4 Lock the index records of , To prevent other transactions from inserting , to update , Delete id=1 This line .

It should be noted that : select * from t where id=4; It's snapshot reading (SnapShot Read), It's not locked , Does not affect other transactions to manipulate the data .

Temporary key lock

Temporary key lock , On a non unique index , It's a combination of recording lock and clearance lock , As shown in the figure below :

Its blockade area , Include both index records , It also includes the interval before the index , namely ( Negative infinity ,1],(2,4],(5,7],(8,11],(12, infinity ].

In the transaction A In the implementation of :

UPDATE table SET name = 'javaHuang' WHERE age = 4;
SELECT * FROM table WHERE age = 4 FOR UPDATE;

Both statements lock (2,4],(4,7) These two intervals .

namely , InnoDB Will get the record line Temporary key lock , At the same time, obtain the gap lock of the next interval of the record line .

Temporary key lock appears for innodb stay rr Under isolation level , Solve the phantom reading problem ( How to solve the problem of unreal reading , In the future, the article will give detailed answers , You can also pay attention to the official account 【Java Learning tribe 】, see ).

If you downgrade the isolation level of a transaction to RC, Temporary key lock will also fail .

Deadlock

*

paraphrase : Deadlock refers to the phenomenon that two or more transactions wait for each other due to competing for lock resources during execution

*

The picture above , This is the normal situation of deadlock .

So how to solve deadlock ?

1. Timeout waiting for transaction , Active rollback .

2. Do a deadlock check , Take the initiative to roll back a transaction , Let other things go on .

Here's a way , Status of deadlock resolution :

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- View the transaction being locked 

 

kill trx_mysql_thread_id;
--( Upper figure trx_mysql_thread_id The value of the column )

Deadlock is a very complicated topic , It can only be said briefly here , I will write an article on deadlock later .

summary

Through this paper , I have a general understanding of mysql Most of the lock functions , effect , Implementation and solutions , I want to be a java Development Engineer , It should be enough to know that , After all, we are not DBA, Otherwise, I know too much , Robbed DBA My job is not so good , To make fun of , After all, there is no end to learning .

Spring Cloud Wonderful series of microservices

  1. Ali asked me : Do you know what Eureka, This time, , I'm not silent
  2. Detailed explanation of Wanzi Ribbon framework , For the interview high-frequency questions in detail from multiple angles Ribbon
  3. What is? Hystrix, Ali technology , It's a pity that Hystrix In front of !
  4. 2 Wanzi Haowen, comprehensive in-depth study SpringCloud Fegin, Interview is no longer hesitating
  5. Zuul, hear SpringCloud I'm not going to ask for it , But why do you ask me every day in the interview ?
  6. The most comprehensive explanation of the whole network Spring Cloud Gateway, It's enough to read this one carefully !

 

 

 

 

版权声明
本文为[Java learning tribe]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224105400259m.html

Scroll to Top