MySQL lock mechanism

MXC Xiao 2021-01-20 23:40:56
mysql lock mechanism

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

1, characteristic

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
  1. At present session Only the current table can be read , Cannot update current table . And can't read and write other tables
  2. 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
  1. At present session You can only read and write the current table , Cannot read and write other tables
  2. other session Cannot read or write the current table ( Blocking ), You can manipulate other tables .

d) summary

  • 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

1, characteristic

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

2, Business

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 .

  1. Atomicity (Atomicity): A transaction is an atomic unit of operation , Its modification of data , Or do it all , Or none of them .
  2. 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 .
  3. 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 .
  4. 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
Isolation level value
Resulting problems
Use cases
Read-Uncommitted( Read uncommitted )
Dirty reading
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
Mysql InnoDB
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

a) Introduce

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

b) Example

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.

5, summary

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

6, analysis

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 .



本文为[MXC Xiao]所创,转载请带上原文链接,感谢

  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课程百度云