Table locks （ Partial reading ）
deviation MyISAM Storage engine . Low overhead , Locked fast , No deadlock , Large locking size , The highest probability of lock collisions , The lowest concurrency .
- View the locking status of tables in the current database ,0 It means not locked .
show open tables;
- Add read / Write lock
lock table Table name read(write) , Table name 2...
- Release the watch lock
Add read lock （ share ）
- The current session and other sessions can read the read locked table
- The current session cannot read other tables （ Do your own business well first ）
- Other sessions need to modify the read locked table , You have to wait for the lock to release （ Blocked state ）
Add write lock （ Monopoly ）
- The current session can read and modify the write locked table
- The current session can't read other tables （ Do your own business well first ）
- Other sessions want to read a write locked table , You have to wait for the lock to release （ Blocked state ）
MyISAM When executing a query statement , Will automatically add Read the lock . When the operation of adding, deleting and modifying is performed again , It will automatically add Write lock .
In short , It's just that reading locks block writing , But it doesn't block reading . A write lock blocks both reading and writing .
Analysis of table lock
- See which tables are locked ,0 The watch lock is not locked ：
show open table
show status like 'table%'command
- Table_locks_immediate： The number of times table level locks were generated , Every time I get it right away , Lock value plus 1
- Table_locks_waited： The number of times a lock cannot be acquired immediately , Every time you wait, the lock value is increased 1. A high value indicates a serious table level lock contention situation .
- Besides ,MyISAM Write lock priority scheduling , This is also not suitable for the main table engine . Because after writing the lock , No other thread can do anything , A large number of updates will make it difficult for queries to get locks , And cause permanent obstruction .
Row lock （ Partial writing ）
deviation InnoDB Storage engine . Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions , The highest degree of concurrency .
InnoDB And MyISAM The two biggest differences between the two ：① Support transactions ,② Row level lock is used
- Operate on the same row of data
Modify the same row of data in the table , Will lead to session2 happen Blocking , once session1 Commit transaction ,session2 The update operation will be performed .
- Manipulating different rows of data
Due to the use of row lock ,session2 and session1 They don't interfere with each other , therefore session2 The modification operation in is not blocked
The row lock is upgraded to the table lock due to the bootless guidance
set autocommit=0; # Turn off auto commit transactions #varchar Data types don't need to ‘’, Causes the system to automatically convert types , Cause index to fail update test_innodb_lock set a=44 where b=4000;
set autocommit=0; update test_innodb_lock set b='9001' where a=9; # Execution discovery is blocked
- session Medium SQL Statement index invalid , Causes row lock to become table lock .session2 Need to wait session1 Commit transaction release lock .
Why create an index after creating a table ？ Because if you don't create an index , When updating operations , A row lock becomes a table lock because there is no index .
When you want to lock a line , Add after ：
for update, Other operations will be blocked , Know the session commit of the locked row .
- InnoDB Line level locking is realized , The performance loss will be higher than table level locking , But on the whole Concurrent processing power Far better than MyISAM
- InnoDB There is also a weak side to row level locking , When we don't use it properly （ Index invalidation becomes table lock ）, May let InnoDB The overall performance ratio of MyISAM Difference .
The nature of transactions
|Atomicity （Atomicity）||All operations in a transaction , All or nothing , All or nothing|
|Uniformity （Consistency）||Before and after a transaction , The integrity of the database is not compromised|
|Isolation, （Isolation）||Different transactions do not affect each other （ There are four levels of isolation ）|
|persistence （Durability）||Once the transaction is committed , The result will be permanent , Data can be recovered even in case of downtime|
Examples of consistency ： For example ,A and B The total amount of money is 5000, No matter A and B How to transfer , When the business is over A and B The sum of money is 5000.
How to implement transactions at the database level ？
- InnoDB They've opened up the business for us , And every statement will be submitted automatically . If you want to ensure the transactional nature of multiple statements , You have to start the transaction manually .
InnoDB Storage engine pair ACID How to implement
Using rollback logs （undo log） and Redo log （redo log） Two tables implement transactions , And implement MVCC ( Multi version concurrency control );
redo log（ Redo log ）
- It is used to record the modified status of data
- Every time an operation is performed , Write the related operations to redo log. In this way, when the power cut and other accidents happen , After system recovery , You can continue to make these changes .
Binlog Two of the most important usage scenarios of logs ？
- MySQL Master slave copy
- Data recovery （ adopt
redolog and binlog The difference between ？
- redolog yes InnoDB Peculiar ,binlog yes MySQL Of Server Layer , All engines work .
- redolog yes Physical log ,binlog It's a logical log
- redolog It's written in a circle , Will be covered , Because the space is fixed, it will run out .binlog It's additional , After writing to a certain size, it will switch to the next .
- redolog stay During transaction execution Keep writing ,binlog Is in Before the transaction is finally committed Written in .
undo log（ Rollback log ）
- Used to record the status of data before modification
- When something unexpected happens in the middle of some changes , And it can't be done , According to undo log Go back to the previous version .（undolog It's a logical log ）
- The opposite record . When delete When a record ,undo log A corresponding insert Record , vice versa . When update When a record , It records an opposite update Record .
For example, the database goes down at some point , There are two things , A transaction has been committed , Another transaction is in progress . After the database restarts , Roll forward or roll back according to the log . Write committed transaction changes to data file , Uncommitted transaction changes are restored to the state before the transaction started .
MySQL in ACID Underlying implementation
- Atomicity ： Mainly depends on undo.log Log implementation , That is, to perform a rollback when a transaction fails .
- persistence ： Mainly depends on redo.log Log implementation . First ,MySQL Persistence through cache To improve efficiency . But it's gone because the cache is powered down , So we need to redo.log journal . When performing modification operations ,sql It will be written to redo.log journal , And then write it to the cache . So even if the power is off , It can also ensure that the data is not lost , Achieve persistence
- Isolation, ： There are four levels of database isolation , adopt MVCC And lock Realization .
- Uniformity ： The above three features are to ensure the consistency of the database （redolog+undolog）
The problem of transaction concurrency
- Lost update ：（ update operation Add exclusive locks to avoid ）
- Of a transaction Roll back or Submit , Causes the loss of updated data for another transaction
- Dirty reading ： Read other concurrent transactions Not submitted The data of . Breaking the isolation of transactions .
- It can't be read repeatedly ： Read data submitted by other concurrent transactions . in the light of update and delete, It also destroys the isolation of transactions .
- Fantasy reading ： Read data submitted by other concurrent transactions . in the light of insert
Isolation level ： Read uncommitted —> Read submitted
- MySQL5.5+ Of InnoDB, stay RR Level can solve the problem of unreal reading .
V1、V2、V3 What are the values of at different isolation levels ？
- Read uncommitted ：20、20、20
- Read submitted ：18、20、20
- Repeatable ：18、18、20
- Serializable ：18、18、20
Implementation of transaction isolation level
LBCC（Lock-Based Concurrent Control） Lock based concurrency control
InnoDB lock ：
- Shared lock is also called read lock , Shared locks are reentrant , But it can't be modified .
- Exclusive lock is also called write lock
- Intent locks ： Intention lock is table lock , Is created automatically when a shared or exclusive lock is created , Cannot create... Manually . Role is , It's used to tell you if you have locked data , It can improve the efficiency of meter lock .
InnoDB The underlying implementation of lock
This is a primary key only id by 1、5、9、11 Table of , The interval of the lock is as follows ：
Gap It's open range ,Next-Key On the right is the closed interval .
- Recrod Locks—— Record locks （ Equivalence match , Precise matching ）
select * from user where id=1 for update;
Lock this article
id = 1 The record of
- Gap Locks—— Clearance lock （ Range match ）
select * from user where id>5 and id<9 for update;
(5,9) The range of , You can't insert it at this time . The clearance lock only exists in RR Isolation level .
select * from user where id>15 for update;Where is the lock ？
Range match , Trigger the clearance lock , Because the biggest one in the table is 11, Index gap lock will put
(11,+oo)The interval of the car is locked , Insert id=12 It's not going to work .
- Next-Key Locks—— Temporary key lock （ Range match , And hit a record ）
The temporary key lock is The combination of record lock and clearance lock .
select * from user where id>5 and id<11 for update;
Range match , And hit the record in the table 9, Trigger the keylock . lock
(5,11]. So insert id=11 It's not going to work .
MVCC（Multi-Version Concurrent Control） Multi version concurrency control .
- A record can have multiple versions in the system , yes InnoDB Realize the important function of transaction concurrency .
The concrete implementation is , In every row of the database , Add three extra fields .
DB_TRX_ID： Record the last transaction to update the row ID
DB_ROLL_PTR： Point to the corresponding undo log The pointer to
DB_ROW_ID： A monotonous row ID, Namely AUTO_INCREMENT Primary key of id
Snapshot read and current read
InnoDB Have a self increasing global transaction ID, Every transaction starts , Will record the unique information of the current transaction ID. meanwhile , When a new transaction is created , The transaction system will change the current Not submitted All the business of id Array of components Provide Give this new business , This array is called
- Read the snapshot
pure select operation .
Every time a transaction updates data , The last updated transaction will be recorded
If this line of data
TRX_ID Collection Greater than The transaction of the current transaction ID, So this line of data is committed after the current transaction is opened . Otherwise, this row of data is committed before the current transaction is opened .
If you encounter data submitted after the current transaction is opened , The current transaction will pass
DB_ROLL_PTR Find the rollback log , Then the logical rollback is performed to get the original data when the transaction is started .
This one goes through undolog+ data row The process of getting the original data when the transaction is started is “ Read the snapshot ”.
Explain in detail ：
BD_TRX_IDIt's someone else's business ID, Our business ID And other people's business ID All in the uncommitted transaction collection , If someone else's business ID More than my business ID, It shows that this transaction was started later than me , So it means that this line of data is after I start the transaction , Other things changed
- The current reading
A lot of times , When we read the database , What you need to read is the current data of the row , Not the original data when the transaction was started .
The current read is done by lock , adopt
next-key The algorithm locks this area , Other transactions cannot be modified .
It mainly includes the following operations ：
insert update select ... lock in share mode # Shared lock select ... for update # Exclusive lock
Innodb stay RR How to avoid unreal reading ？
- In the case of snapshot read （select）, adopt MVCC To avoid unreal reading
adopt undo log, Find the raw data .
- In the current reading situation , adopt next-Key To avoid unreal reading
problem ：InnoDB It can solve the problem of unreal reading , But the solution is not perfect . use MVCC The implementation of snapshot read is flawed , Once a transaction is modified , It covers the insertion of other transactions “ Magic line ”, So these “ Magic line ” It will appear again in the next query , And then there's the phantom problem .