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;
* 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 .