One 、 summary
1, Definition of lock
- Locks are computers A mechanism for coordinating concurrent access to a resource by multiple processes or threads
- In the database , In addition to traditional computer resources ( Such as CPU、RAM、I/O etc. ) Beyond contention , Data is also a resource that is Shared by many users
- How to ensure the consistency of data concurrent access 、 Validity is a problem that all databases must solve , Lock conflicts are also an important factor affecting the performance of concurrent database access .
2, Classification of locks
a） The type of data operation
- Read the lock ( Shared lock )： For the same data , Multiple read operations can be performed simultaneously without affecting each other
- Write lock ( Exclusive lock )： Before the current write operation is completed , It blocks other write and read locks
b） Granularity of data manipulation
- Row lock
- Table locks
- Page locks
Two 、 Table locks
deviation MyISAM Storage engine , Low overhead , Locked fast , A deadlock will not occur ; Strong locking , High probability of lock conflict , Lowest degree of concurrency .
2, case analysis
# Lock mode lock table Table name 1 read(write), Table name 2 read(write); # View the lock added to the table 0 It means not locked show open tables; # Release the lock unlock tables;
a) Build table
create table mylock ( id int not null primary key auto_increment, name varchar(20) default '' ) engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e'); select * from mylock;
b) Add read lock
- stay session 1 Middle lock
# to mylock Read lock on the meter lock table mylock read; # Inquire about mylock surface You can show all the data select * from mylock; # No other tables can be read in the current session :Table 'tbl_emp' was not locked with LOCK TABLES select * from tbl_emp;
- stay session 2 Read and modify
# You can query select * from mylock; # modify mylock The watch will come out first and wait , until mylock Table release lock update mylock set name='aaa' where id = 1;
- Conclusion ： At present session For the current table Add write read lock
- At present session Only the current table can be read , Cannot update current table . And can't read and write other tables
- other session Only the current table can be read , Cannot update current table ( Blocking ). You can read and write other tables .
c) Add write lock
- stay session 1 Add a write lock to
# to mylock Table write lock lock table mylock write; # Inquire about mylock surface You can show all the data select * from mylock; # No other tables can be read in the current session :Table 'tbl_emp' was not locked with LOCK TABLES select * from tbl_emp;
- stay session 2 Read and update
# stay session 2 Unable to read and update data in , Keep blocking ; until session in unlock tables Unlock select * from mylock;
- Conclusion ： At present session Write lock the current table
- At present session You can only read and write the current table , Cannot read and write other tables
- other session Cannot read or write the current table ( Blocking ), You can manipulate other tables .
- MyIsam In the execution of the query statement （SELECT） front , Will automatically give the involved All tables are read locked , In execution Before adding, deleting and modifying , Will automatically give the involved Table write lock .
- Yes MyISAM Read operation of table （ Add read lock ）, 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 , To perform other process write operations .
- Yes MyISAM Write operation of table （ Add write lock ）, Will block other processes to read and write to the same table , Only when the write lock is released , Will perform read and write operations of other processes .
- In short , Namely Reading locks block writing , But it doesn't block reading . The write lock blocks both reading and writing .
3、 ... and 、 Row lock
- deviation InnoDB Storage engine , Spending big , Lock the slow ; A deadlock occurs ( Clearance lock ); Locking granularity minimum , The lowest probability of lock collisions , The highest degree of concurrency .
- InnoDB And MyISam There are two big differences between ：1. Support transactions ;2. Using row level locks .
a） Business （Transation） And its ACID
The business is made up of a group of SQL A logical processing unit made up of statements , The transaction has the following 4 Attributes , Usually referred to as transaction ACID attribute .
- Atomicity （Atomicity）： A transaction is an atomic unit of operation , Its modification of data , Or do it all , Or none of them .
- Uniformity （Consistent）： At the beginning and end of the transaction , Data must be consistent . This means that all relevant data rules must be applied to the modification of the transaction , To maintain the integrity of the data ; At the end of the transaction , All internal data structures （ Such as B Tree index or double linked list ） They have to be right .
- Isolation, （Isolation）： The database system provides a certain isolation mechanism , Ensure that transactions are not affected by external concurrent operations “ Independent ” Environmental execution . This means that the intermediate state in the transaction process is not visible to the outside , vice versa .
- persistence （Durability）： After the establishment of the affairs Institute , Its modification of data is permanent , Even if there is a system failure, it can keep .
b） Transaction isolation level
MySQL View transaction isolation level ：show variables like 'tx_isolation';
- Dirty reading ： Business A Transaction read B Modified but Data not yet submitted , On the basis of this data, we have done the operation .
- It can't be read repeatedly ： Business A Transaction read B Revised data submitted , Nonconformance isolation
- Fantasy reading ： The first transaction modifies a certain range of data in batches , The second transaction adds a piece of data in this range , At this point, the first transaction will be Lost modifications to new data
Isolation level value
Read-Uncommitted( Read uncommitted )
Read-committed( Read the submission )
No dirty reading , Allow non repetition and unreal reading
Repeatable-read( Repeated reading )
Dirty reading and non repeatable reading , Allow unreal reading
Serializable ( serialize )
Can be avoided , Slow execution , Use with caution
3, Row lock case
a） Create table
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB; INSERT INTO test_innodb_lock VALUES(1,'b2'); INSERT INTO test_innodb_lock VALUES(3,'3'); INSERT INTO test_innodb_lock VALUES(4, '4000'); INSERT INTO test_innodb_lock VALUES(5,'5000'); INSERT INTO test_innodb_lock VALUES(6, '6000'); INSERT INTO test_innodb_lock VALUES(7,'7000'); INSERT INTO test_innodb_lock VALUES(8, '8000'); INSERT INTO test_innodb_lock VALUES(9,'9000'); INSERT INTO test_innodb_lock VALUES(1,'b1'); CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
b） Operate on the same row of data
When manipulating the same row of data ： When the last business Modification not submitted when , The second business will also be modified It's in a jam .
c） Manipulating different rows of data
When the operation is different ： Even if the last transaction was fixed I didn't submit , The second transaction can also be modified , They don't influence each other .
d） Index failure , Table locks
modify test_innodb_lock Data in ,varchar no need ’ ’ , Causes the system to automatically convert types , Lead to Index failure , There will be Table locks .
4, Clearance lock
- When we use Range conditions Instead of retrieving data under equal conditions , And ask for Shared or exclusive locks when ,InnoDB It will lock the index entries of existing data records that meet the conditions ; For records where the key value is in the condition range but does not exist , be called “ The gap （GAP）”
- InnoDB It's also about this “ The gap ” Lock , This locking mechanism is called clearance lock （Next-Key lock ）
4, Lock a line
select xxx ... for update After locking a line , Other operations will be blocked , Until the session commit of the locked row commit.
- Innodb Because the storage engine implements Row level lock , Although the implementation of the locking mechanism brings about Performance loss may be higher than table level locking some , But on the whole The concurrent processing ability is far better than MyISAM The watch level is locked .
- When the system concurrency is high ,Innodb The overall performance and MyISAM There will be obvious advantages in comparison .
- however ,Innodb There is also a weak side to row level locking , When we don't use it properly （ Index failure , Causes the row lock to become the table lock ）, May let Innodb The overall performance of MyISAM high , Even worse .
show status like 'innodb_row_lock%';
- Innodb_row_lock_current_waits： The number of currently waiting locks ;
- Innodb_row_lock_time： From system startup to now Total lock time ;
- Innodb_row_lock_time_avg： The average time it takes to wait ;
- Innodb_row_lock_time_max： Time spent waiting for the most frequent time from system startup to now ;
- Innodb_row_lock_waits： Since the system started up Total waiting times ;
7, Row lock optimization
- As far as possible, all data retrieval should be done through index , Avoid upgrading non indexed row locks to table locks
- reasonable Design index , Try to narrow down the range of locks
- As far as possible Reduce search conditions , Avoid gap locks
- Try to control the transaction size , Reduce the amount of locked resources and the length of time
- As low level transaction isolation as possible
Four 、 Page locks
- Cost and lock time are between table lock and row lock ： A deadlock occurs ;
- Lock granularity is between table lock and row lock , The concurrency is average .