One ,InnoDB Implementation of row lock

By locking the index entries on the index , Which means : Retrieve data only by index criteria ,InnoDB Use row lock only , otherwise ,InnoDB Table lock will be used . This point needs special attention in practical application , Otherwise, it may lead to a lot of lock conflicts , Thus, the concurrent performance is affected .

Experiment 1 : Lock without index , Cause table lock

1) preparation : build tab_no_index surface , No index in table , And insert data

2)Session_1: We give id=1 Row with exclusive lock (for update), Because id No index , It's actually a table lock ;

3)Session_2: We give id=2 Row with exclusive lock (for
update), Because id No index , So apply for form level lock , But there's a lock waiting ! The reason is that without an index ,InnoDB Only table lock can be used .

Remarks :MySQL Medium for update Only for InnoDB( Because only this engine has row level lock ), And transaction must be opened , stay begin And commit Between .for
update It's locked in the database , You can place an exclusive lock on a row in the database . When the operation of a transaction is not completed , Other transactions can read this row but cannot write or update it , You can only wait for this transaction Rollback,
Commit, Lost connection…

Experiment 2 : Lock key value with index , Lock all data rows involved

1) preparation : Yes id Index as follows

2)Session_1: here id It's indexed , We're right id=1 and name=1 Row by row ;

3)Session_2: Access is different from Session_1 Of id=1, name=5 That's ok , But the index key values are the same , Still waiting for lock , Lock conflict .

Experiment 3 : When multiple indexes , Different transactions can lock different rows with different indexes , No matter what index ,InnoDB Row locks are used to lock data ( For indexed row data ).

1) preparation : Yes tab_no_index Append name Indexes :alter table tab_no_index add index name(name);

2)Session_1: Open transaction pair id=1 Row lock , That is right name=1 And name=5 Two data locks .

3)Session_2: Open transaction pair name=2 Line lock , Because the data is not locked , Index can get lock

4)Session_3: Again on name=5 Lock the data of , Because the data record has been Session_1 locking , So wait for the lock .

matters needing attention : Even with indexes , But we still have to see MySQL Specific SQL Implementation plan of , Not necessarily available

For example, we have three pairs of experiments name='2' Lock , Mistaking name yes int type , originally name It's indexed , But the result is a table lock :

Two , Gap lock (Next-Key lock )

When retrieving data with range conditions instead of equality conditions , And request sharing or exclusive locking ,InnoDB Lock the index entries of existing data records that meet the conditions ; For records that are not in scope but do not exist , Be called “ clearance (GAP)”,InnoDB This gap will also be locked , This is the so-called clearance lock .

as :select * from where id>100 for
update  Yes id greater than 100 Data pair lock of , But in the data id only 1,2….100,101, Not only for existing 101 Record lock of , It will also 101 Gap locking of non-existent data .

in addition , Lock a nonexistent record using an equality request ,InnoDB Clearance locks are also used , as follows :

Session_1: Yes, it doesn't exist id=6 Record lock of

Session_2: insert id=6 Records of , There will also be lock waiting

Three , When to use watch lock ?

about InnoDB surface , Row locks should be used in most cases , Because transactions and row locks are often our choice InnoDB Reasons for the table , But in some cases, table level lock is also used ;

* Transactions need to update most or all of the data , The watch is bigger , If the default row lock is used , Not only is this transaction inefficient , And may cause other transactions to wait for a long time and lock conflict ;
* Transactions involve multiple tables , Complex , It is likely to cause deadlock , Cause a large number of transaction rollback .
Several points to be paid attention to when using table lock :

1) Use LOCK TABLES Although you can give InnoDB Add meter level lock , Table lock is not InnoDB Managed by storage engine layer , It's up there MySQL Server conscientious

2) In use LOCK TABLES Yes InnoDB Attention shall be paid when the watch is locked , Will be Autocommit Set to 0, otherwise MySQL Don't lock the watch ; Before end of transaction , Don't use it UNLOCK
TABLES Release table lock , because UNLOCK_TABLES Implicit commit transaction ;COMMIT or ROLLBACK It can't be released LOCK TABLES Add meter level lock .
SET AUTOCOMMIT=0; LOCK TABLES table1 WRITE, table2 READ,...;
[do something....] COMMIT; UNLOCK TABLES;
summary

* From the beginning of design , We should establish a good index mechanism , Avoid table lock when searching key fields ;
* Avoid long uncommitted transactions, etc , Cause lock conflict , Deadlock, etc ;
* Don't always complain about database problems , It should be written from itself SQL Starting from analysis , Learn to analyze ( Databases don't work, mostly because SQL There's something wrong with the writing , You're right , It's a problem of its own );
* Don't always think it's DBA What to do , Developers should learn basic SQL common sense ( as MySQL Left most index of , Return table , Index coverage and other knowledge ), Learn the basic optimization steps .
 

Technology
©2019-2020 Toolsou All rights reserved,
JAVA Detailed explanation of anomalies MySQL An interview is a must ! How to use it quickly html and css Write static page R Language cluster analysis case Dialogue between apple and Nissan suspended ,Apple Car How's it going ?java Realize the function of grabbing red packets SpringBoot practice ( five ):mybatis-plus In BaseMapper,Iservice and ServiceImpl Google says home office affects work efficiency !2021 Return to offline office in 2010 about keras use fit_generator Encountered in StopIteration Programmer Tanabata Valentine's Day confession code