(3) MySQL lock mechanism + transaction

Why is the algorithm so difficult 2021-02-23 12:37:48
mysql lock mechanism transaction

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
unlock table;
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
  • adopt 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

    • session1

      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; 
    • session2

      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 .

  1. 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
  2. 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

characteristic explain
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 mysqlbinlog Tools )
redolog and binlog The difference between ?
  1. redolog yes InnoDB Peculiar ,binlog yes MySQL Of Server Layer , All engines work .
  2. redolog yes Physical log ,binlog It's a logical log
  3. 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 .
  4. 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 RC—> Repeatable RR—> Serializable

  • 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;

Lock the (5,9) The range of , You can't insert it at this time . The clearance lock only exists in RR Isolation level .

reflection : 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,9] and (9,11], namely (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 .

  1. DB_TRX_ID: Record the last transaction to update the row ID
  2. DB_ROLL_PTR: Point to the corresponding undo log The pointer to
  3. 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 TRX_ID aggregate

  • Read the snapshot

pure select operation .

Every time a transaction updates data , The last updated transaction will be recorded DB_TRX_ID.

If this line of data DB_TRX_ID stay 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_ID It'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 :

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 .

本文为[Why is the algorithm so difficult]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云