MySQL lock details, java reflection principle interview questions

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 ( Lock a line )

============

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 Next, we will

《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 Full content open source sharing

After the data is queried, the exclusive lock is directly added （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 ：