The meaning difference between row lock and table lock , It should be high frequency in the interview , We should be right MySQL There is a systematic understanding of the lock in , More detailed information needs to be consulted by yourself , This article is a summary of the answer .
MySQL Common engines are MyISAM and InnoDB, and InnoDB yes mysql Default engine .MyISAM Row locks are not supported , and InnoDB Support row lock and table lock .
How to lock ?
MyISAM In the execution of the query statement （SELECT） Front , All tables involved will be read locked automatically , During the update operation （UPDATE,DELETE,INSERT etc. ） Front , Write locks are automatically applied to the tables involved , This process does not require user intervention , Therefore, users generally do not need to use it directly LOCK
TABLE Order to MyISAM Watch explicit lock .
Explicit locking ：
Shared lock on （ Read lock ） How to write ：lock in share mode, for example ：
select math from zje where math>60 lock in share mode;
Top row lock （ Write lock ） How to write ：for update, for example ：
select math from zje where math >60 for update;
<> Watch lock
There will be no deadlock , High probability of lock conflict , Low concurrency .
MyISAM In the execution of the query statement （select） Front , All tables involved will be read locked automatically , Before adding, deleting and modifying , Write locks are automatically applied to the tables involved .
MySQL There are two modes of table level locking for ：
* Table shared read lock
* Table Write Lock
Read locks block writes , A write lock blocks reading and writing
* Yes MyISAM Read operation of table , It will not block other processes' reading requests to the same table , But it blocks write requests to the same table . Only when the read lock is released , Will execute the write operation of other processes .
* Yes MyISAM Write operations on tables , Will block other processes to read and write to the same table , Only when the write lock is released , Will perform the read and write operations of other processes .
MyISAM It is not suitable to be an engine for writing the main table , Because after writing the lock , Other threads cannot do anything , A large number of updates make it difficult for queries to get locks , This will cause permanent obstruction .
<> Row lock
There will be a deadlock , The probability of lock conflict is low , High concurrency .
stay MySQL Of InnoDB The engine supports row locks , And Oracle Different ,MySQL The row locks for are loaded through the index , in other words , Row locks are imposed on the rows of the index response , If the corresponding SQL Statement is not indexed , It will scan the whole table , Row locks cannot be implemented , Instead, it's a watch lock , At this time, other transactions cannot update or insert the current table .
CREATE TABLE `user` ( `name` VARCHAR(32) DEFAULT NULL, `count` INT(11) DEFAULT
NULL, `id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=INNODB
AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 -- here , We build one user surface , The primary key is id --
A Insert through primary key , But the transaction was not committed update user set count=10 where id=1; -- B The update operation is also performed at this time update
user set count=10 where id=2; -- Because it is selected through the primary key , Is a row level lock ,A and B The operation is not on the same line ,B The operation performed can be performed --
A adopt name Perform the insert operation , But the transaction was not committed update user set count=10 where name='xxx'; -- B The update operation is also performed at this time
update user set count=10 where id=2; -- Because it is selected through a non primary key or index , Upgrade to table level lock , --
B The table cannot be updated or inserted , Only when A After committing a transaction ,B Will be executed successfully
The implementation of row lock needs attention ：
* A row lock must have an index to implement , Otherwise, the whole meter will be locked automatically , So it's not a row lock .
* Two transactions cannot lock the same index .
* insert,delete,update The exclusive lock will be automatically added in the transaction by default .
Row lock scenario ：
A User consumption ,service The layer first queries the account balance of the user , If the balance is sufficient , Then the subsequent deduction operation will be carried out ; In this case, the record should be locked when querying .
otherwise ,B User in A After the user inquires, he or she will A Money transferred from user account , And now A The user has judged whether the user balance is sufficient , The balance may be insufficient but the deduction is successful .
To avoid this situation , Need to be in A When the user operates the record for update Lock
extend ： Clearance lock
When we retrieve data using range conditions rather than equality conditions , And request a shared or exclusive lock ,InnoDB The index entries of existing data records that meet the criteria are locked ; For records whose key value does not exist within the range of conditions , It's called a gap
InnoDB It's going to be the same with this " clearance " Lock , This kind of lock mechanism is called gap lock
-- user A update user set count=8 where id>2 and id<6 -- user B update user set count
=10 where id=5;
If the user A After the above operation , The transaction has not been committed , be B Can't be right 2~6 Between records to update or insert records , It will block , When A After committing the transaction ,B The update operation of the .
* As far as possible, all data retrieval is done through index , Avoid upgrading non index row locks to table locks
* Rational design index , Minimize the scope of the lock
* Minimize index conditions , Avoid gap locks
* Try to control the transaction size , Reduce the amount of locked resources and the length of time