MySQL锁机制

MXC肖某某 2021-01-20 23:40:34
Mysql 博客园 数据库/缓存 机制


一、概述

1,锁的定义

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制
  • 在数据库中,除传统的计算机资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源
  • 如何保证数据并发访问的一致性、有效性是所有数据库必须解決的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。

2,锁的分类

a)数据操作的类型

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
  • 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁

b)数据操作的颗粒度

  • 行锁
  • 表锁
  • 页锁

二、表锁

1,特点

偏向MyISAM存储引擎,开销小加锁快,不会出现死锁锁定力度大,发生锁冲突概率高,并发度最低

2,案例分析

#加锁方式
lock table 表名1 read(write),表名2 read(write);
#查看表上加过的锁 0表示未上锁
show open tables;
#释放锁
unlock tables;

a)建表

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)加读锁

  • 在session 1中加锁
#给mylock表加读锁
lock table mylock read;
#查询mylock表 可以展示所有数据
select * from mylock;
#在当前会话中不能读取别的表:Table 'tbl_emp' was not locked with LOCK TABLES
select * from tbl_emp;
  • 在session 2中读取和修改
#可以查询
select * from mylock;
#修改mylock表会出先等待,直到mylock表释放锁
update mylock set name='aaa' where id = 1;
  • 结论:当前session为当前表加写读锁
  1. 当前 session只能读取当前表,不能更新当前表。并且不能操作其他表的读和写
  2. 其他 session只能读取当前表,不能更新当前表(阻塞)。可以操作其他表的读和写。

c)加写锁

  • 在session 1中添加写锁
#给mylock表加写锁
lock table mylock write;
#查询mylock表 可以展示所有数据
select * from mylock;
#在当前会话中不能读取别的表:Table 'tbl_emp' was not locked with LOCK TABLES
select * from tbl_emp;
  • 在session 2中读取和更新
#在session 2中无法读取数据和更新数据,一直阻塞;直到session中unlock tables解锁
select * from mylock;
  • 结论:当前session为当前表加写锁
  1. 当前session只能操作当前表的读和写,不能操作其他表的读和写
  2. 其他session不能操作当前表的读和写(阻塞),可以操作其他表。

d)总结

  • MyIsam在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁
  • 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
  • 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
  • 简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞

三、行锁

1,特点

  • 偏向InnoDB存储引擎,开销大,加锁慢会出现死锁(间隙锁)锁定粒度最小,发生锁冲突的概率最低,并发度也最高
  • InnoDB与MyISam的最大不同有两个:1.支持事务;2.采用行级锁。

2,事务

a)事务(Transation)及其ACID

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

  1. 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  2. 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
  3. 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
  4. 持久性(Durability):事务院成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

b)事务隔离级别

MySQL查看事务隔离级别:show variables like 'tx_isolation';

  • 脏读:事务A读取到了事务B已修改但尚未提交的的数据,还在这个数据基础上做了操作。
  • 不可重复读:事务A读取到了事务B已经提交的修改数据,不符合隔离性
  • 幻读:第一个事务对一定范围的数据进行批量修改,第二个事务在这个范围内增加一条数据,这时候第一个事务就会丢失对新新增数据的修改
隔离级别
隔离级别的值
导致的问题
用例
Read-Uncommitted(读未提交)
0
有脏读
 
Read-committed(读提交)
1
无脏读,允许不可重复读和幻读
SqlServer、Oracle
Repeatable-read(重复读)
2
无脏读和不可重复读,允许幻读
Mysql InnoDB
Serializable (序列化)
3
都可以避免,执行效率慢,慎用
 

3,行锁案例

a)创建表

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)操作同一行数据

操作同一行数据时:当上一个事务修改未提交时,第二个事务也去修改会处于阻塞

c)操作不同行数据

操作不同行时:即使上一个事务修改未提交,第二个事务也能修改,互不影响

d)索引失效,表锁

修改 test_innodb_lock 中的数据,varchar 不用 ’ ’ ,导致系统自动转换类型,导致索引失效,会出现表锁

 

4,间隙锁

a)介绍

  • 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”
  • InnoDB也会对这个“间隙”加锁,这种锁机制是所谓的间隙锁(Next-Key锁)

b)示例

4,锁定某一行

select xxx ... for update 锁定某一行后,其它的操作会被阻塞,直到锁定行的会话提交commit。

5,总结

  • Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
  • 当系统并发量较高的时候,Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
  • 但是,Innodb的行级锁定同样也有其脆弱的一面,当我们使用不当的时候(索引失效,导致行锁变表锁),可能会让Innodb的整体性能表现不仅不能比MyISAM高,甚至可能会更差。

6,分析

show status like 'innodb_row_lock%';

  • Innodb_row_lock_current_waits:当前正在等待锁定的数量;
  • Innodb_row_lock_time:从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg:每次等待所花平均时间
  • Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
  • Innodb_row_lock_waits:系统启动后到现在总共等待的次数

7,行锁优化

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能减少检索条件,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

四、页锁

  • 开销和加锁时间界于表锁和行锁之间:会出现死锁;
  • 锁定粒度界于表锁和行锁之间,并发度一般。

 

 

版权声明
本文为[MXC肖某某]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/bbgs-xc/p/14302996.html

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