文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL锁机制详解-表锁与行锁

2023-09-11 15:36

关注

文章目录

1. 数据库锁理论

锁是计算机协调多个进程或线程并发访问某一资源的机制。

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

我们到淘宝上买一件商品,商品只有一件库存,这个时候如果还有另一个人买,那么如何解决是你买还是别人买到的问题呢?这里就涉及到了事务,我们先从库存表取出物品数量,然后插入订单,付款后插入付款表信息,然后更新商品数量。在这个过程中,使用锁可以对有限的资源进行保护,解决隔离和并发的问题。

2. 锁的分类

2.1 按数据操作的类型分类

锁的分类,按数据操作的类型划分,分为读锁和写锁:

2.2 按数据操作的颗粒度分类

在关系型数据库中,按数据操作的颗粒度划分,分为表锁,行锁和页锁。表锁,行锁和页锁的对比如下:

特点:表锁开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低

特点:行锁开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

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

3. 表锁的应用

表锁对当前操作的整张表加锁,实现简单 ,资源消耗也比较少,加锁快,不会出现死锁 。触发锁冲突的概率最高,并发度最低,MyISAM和 InnoDB引擎都支持表级锁。在 MyISAM 存储引擎中,会自动为 select语句加上共享锁,为 update/delete/insert 操作加上排他锁。

3.1 表锁相关命令

(1)手动添加表锁

lock table 表名字 read(write),表名字2 read(write)

(2)查看表上加过的锁的命令

show open tables

在这里插入图片描述

(3)释放表锁的命令

unlock tables

(4)分析表锁定的命令

 show status  like 'table%';

在这里插入图片描述
我们可以通过检查table_locks_waitedtable_locks_immediate状态变量来分析系统的表锁定情况。两个变量的说明如下:

如果table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。这时,需要我们对应用做进一步的检查,来确定问题所在。

3.2 给表加表共享读锁

表共享读锁,加了共享读锁的表,不会阻塞其他 session 的读请求,但是会阻塞其他 session 的写请求。

创建数据的SQL:

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');### 3.1 读锁

以给mylock表加read锁(读阻塞写例子)
在这里插入图片描述

在这里插入图片描述
总结:

加了共享读锁的表,不会阻塞其他 session 的读(select)请求,但是会阻塞当前session和其他 session 的写(insert、update、delete)请求。

3.3 给表加表独占写锁

独占写锁就是大家锁所熟知的排他锁,它会阻塞其他进程对同一表的读写操作,只有当当前的排他锁释放后,才会执行其他进程的读写操作。
在这里插入图片描述
加了写锁后,当前会话不能对其他表进行读写操作,而其他会话可以对其他表进行读写操作。
在这里插入图片描述

3.4 意向共享锁和意向排他锁

当一个事务需要给自己需要的某个资源加锁的时候,如果遇到一个共享锁正锁定着自己需要的资源的时候,自己可以再加一个共享锁,不过不能加排他锁。但是,如果遇到自己需要锁定的资源已经被一个排他锁占有之后,则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。

InnoDB支持多粒度锁,允许行锁和表锁共存意向锁是表级锁,意向锁的作用是指示事务稍后需要对表中的一行使用哪种类型的锁(共享锁或排他锁)。就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。

InnoDB存储引擎的两个表级锁:

注意:

InnoDB的锁机制兼容情况如下图所示:
在这里插入图片描述

当一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之如果请求不兼容,则该事物就等待锁释放。

3.5 并发插入

为减少读写锁之间的争抢,MyISAM存储引擎支持并发插入。我们可以使用local关键字实现一个表加了读锁,其他session对该表的写操作依然可以执行。但是只有当当前持有读锁的session释放读锁后,其他session的写操作结果才可见。语法如下:

lock table 表名 read local

这样在当前表被加读锁的时候,可以让其他session往表里添加记录,但需要配合concurrent_insert全局变量使用。MySQL的 concurrent_insert参数用枚举值及含义如下:

查看当前数据库的设置:

show global variables like '%concurrent_insert%';

在这里插入图片描述

改变数据库设置:

set global concurrent_insert = ALWAYS;

在这里插入图片描述

3.6 MyISAM锁调度机制

对于仅使用表级锁的存储引擎(如 MyISAM 、MEMORY 和 MERGE),写进程的优先级高于读进程,尽管读进程在队列的头部,写进程也会插队。通过设置系统变量 low-priority-updates=1,所有的 INSERT、UPDATE、DELETE和 LOCK TABLE WRITE 语句都将等待,直到受影响的表上没有挂起的 SELECTLOCK TABLE 读操作。

在这里插入图片描述

3.7 总结

4. 行锁的应用

4.1 基本介绍

InnoDB存储引擎默认采用行锁,行级锁锁定粒度最小,发生锁冲突的概率最低,并发度也最高。但是行锁开销大,加锁慢,会出现死锁。 InnoDB是基于索引来完成行锁,例如:

select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定,并且 ID 是有索引键的列,如果 ID不是索引键那么InnoDB将完成表锁。

InnoDB与MyISAM的最大不同有两点:1.支持事务 2.采用行级锁

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

  1. 原⼦性(Atomicity): 事务是最⼩的执⾏单位,不允许分割。事务的原⼦性确保动作要么全部完成,要么完全不起作⽤;
  2. ⼀致性(Consistency): 执⾏事务前后,数据都必须保持一致状态,多个事务对同⼀个数据读取的结果是相同的;
  3. 隔离性(Isolation): 数据库系统提供一定的隔离机制,并发访问数据库时,⼀个⽤户的事务不被其他事务所⼲扰,各并发事务之间数据库是独⽴的;
  4. 持久性(Durability): ⼀个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发⽣故障也不应该对其有任何影响。

在典型的应⽤程序中,多个事务并发运⾏,经常会操作相同的数据来完成各⾃的任务(多个⽤户
对同⼀数据进⾏操作)。并发虽然是必须的,但可能会导致以下的问题。

  1. 脏读(Dirty read): 当⼀个事务正在访问数据并且对数据进⾏了修改,⽽这种修改还没有提交到数据库中,这时另外⼀个事务也访问了这个数据,然后使⽤了这个数据。因为这个数据是还没有提交的数据,那么另外⼀个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。
  2. 丢失修改(Lost to modify): 指在⼀个事务读取⼀个数据时,另外⼀个事务也访问了该数据,那么在第⼀个事务中修改了这个数据后,第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失,因此称为丢失修改。
    例如:事务1读取某表中的数据A=20,事务2也读取A=20,事务1修改A=A-1,事务2也修改A=A-1,最终结果A=19,事务1的修改被丢失。
  3. 不可重复读(Unrepeatableread): 指在⼀个事务内多次读同⼀数据。在这个事务还没有结 束时,另⼀个事务也访问该数据。那么,在第⼀个事务中的两次读数据之间,由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况,因此称为不可重复读。
  4. 幻读(Phantom read): 幻读与不可重复读类似。它发⽣在⼀个事务(T1)读取了⼏⾏数据,接着另⼀个并发事务(T2)插⼊了⼀些数据时。在随后的查询中,第⼀个事务(T1)就会发现多了⼀些原本不存在的记录,就好像发⽣了幻觉⼀样,所以称为幻读。

不可重复读和幻读区别: 不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改,幻读的重点在于新增或者删除,⽐如多次读取⼀条记录发现记录增多或减少了。

MySQL的事务隔离级别有四种:

  • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更, 可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻⽌脏读,但 是幻读或不可重复读仍有可能发⽣。
  • REPEATABLE-READ(可重复读): 对同⼀字段的多次读取结果都是⼀致的,除⾮数据是被 本身事务⾃⼰所修改,可以阻⽌脏读和不可重复读,但幻读仍有可能发⽣。
  • SERIALIZABLE(可串⾏化): 最⾼的隔离级别,完全服从ACID的隔离级别。所有的事务依 次逐个执⾏,这样事务之间就完全不可能产⽣⼲扰,也就是说,该级别可以防⽌脏读、不可 重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ(可重复读)

4.2 行锁的使用

建表SQL:

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(2,'2000');insert into test_innodb_lock values(3,'3000');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');create index idx_a on test_innodb_lock(a);create index idx_b on test_innodb_lock(b);

普通的 select 语句是不会对记录加锁的,如果要在查询时对记录加行锁,可以使用下面这两个方式:

#对读取的记录加共享锁select... from ... where ... lock in share mode;#对读取的记录加独占锁select ... from ... where ... for update;

上面这两条语句必须在一个事务中,因为当事务提交了,锁就会被释放,所以在使用这两条语句的时候,要加上 begin、start transaction 或者取消自动提交。
取消自动提交命令:

set autocommit = 0;

行锁演示:
通过for update 给某一行上行锁
在这里插入图片描述

两个会话更新同一行,2号会话会阻塞。
在这里插入图片描述

两个会话更新不同的行,1号会话更新a=1,而2号会话更新其他行可以正常更新不会阻塞。
在这里插入图片描述
注意:索引未生效或查询条件没有建立索引,会导致行锁变表锁,如varchar 不用 ’ ’ 导致系统自动转换类型,使得索引失效。

在索引失效的情况下,行锁变成了使用表锁,会话2 的更新操作阻塞,直至会话1释放持有的表锁。
在这里插入图片描述
索引未失效的情况,使用行锁,不会导致会话2 的更新操作阻塞
在这里插入图片描述

4.3 行锁的算法

InnoDB存储引擎行锁的算法有三种:

4.3.1 Record Lock

Innodb对于行的查询使用Next-key LockNext-key Lock为了解决Phantom Problem幻读问题。

当查询的索引含有唯一属性时,将Next-key Lock降级为Record Lock。如下SQL:

SELECT id FROM user WHERE id = 1;

当id列为唯一索引列,对id=1的索引记录进行加锁,此时使用的是Record Lock。

4.3.2 Gap Lock

Gap Lock间隙锁是在索引记录之间的间隙上的锁,或者是在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

在这里插入图片描述
可以看到不能在test_innnodb_lock表上的a列插入值2,因为a列当前范围(1,5)的间隙被加上了间隙锁。

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

间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内,而这会导致幻读问题的产生。
有两种方式显式关闭Gap Lock间隙锁:(除了外键约束和唯一性检查外,其余情况仅使用record lock)

4.3.3 Next-key Lock

Next-key Lock 锁定索引项本身和索引范围,即Record Lock和Gap Lock的结合,可解决幻读问题。

例子:当事务 T1 对 r行 加 共享 或 排他锁时,同时会对 r行 前的间隙加 间隙锁,此时,另一个事务 T2 无法在 r行 之前插入新的索引记录。
假设一个索引包含值10、11、13和20。该索引可能的 Next-Key Locks覆盖以下区间:

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
对于最后一个间隔,Next-Key Locks 锁定了索引中最大值之上的间隙和“正无穷”伪记录,该伪记录的值高于索引中任何实际值。其并不是一个真正的索引记录,因此,实际上,这个 Next-Key Locks 只锁定最大索引值后面的间隙。

默认情况下,InnoDB操作在 可重复读(REPEATABLE READ) 事务隔离级别。在这种情况下,InnoDB使用Next-Key Locks进行搜索和索引扫描,这样可以防止幻读。

关于行锁的更多学习: https://mp.weixin.qq.com/s/1LGJjbx_n_cvZndjM3R8mQ

4.4 分析行锁的争夺情况

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况,命令如下:

 show status like 'innodb_row_lock%';

在这里插入图片描述
各个状态量的说明如下:

当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。

4.5 死锁和避免死锁

死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象。

InnoDB的行级锁是基于索引实现的,如果查询语句没有命中任何索引,那么InnoDB会使用表级锁.。此外,InnoDB的行级锁是针对索引加的锁,不针对数据记录,因此即使访问不同行的记录,如果使用了相同的索引键仍然会出现锁冲突。

此外,不同于MyISAM总是一次性获得所需的全部锁,InnoDB的锁是逐步获得的,当两个事务都需要获得对方持有的锁,导致双方都在等待,这就产生了死锁。 发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个则可以获取锁完成事务,我们可以采取以上方式避免死锁:

4.6 总结

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

但是,InodbDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。

使用InnoDB存储引擎的优化建议:

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

5. 页锁

页锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。页级进行了折衷,一次锁定相邻的一组记录。BDB存储引擎支持页级锁。开销和加锁时间界于表锁和行锁之间,会出现死锁。锁定粒度界于表锁和行锁之间,并发度一般。

6. 隔离级别与锁的关系

读取未提交Read Uncommitted级别下,读取数据不需要加共享锁,这样就不会跟被修改的数据上的排他锁冲突

读取已提交Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁。

可重复读Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁。

可串行化SERIALIZABLE是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成。

7. 数据库的乐观锁和悲观锁

数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

悲观锁的实现方式:使用数据库中的锁机制

乐观锁的实现方式:一般会使用版本号机制或CAS算法实现。

关于两种锁的使用场景,从上面对两种锁的介绍,我们知道两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。

但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行Retry,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适。

8. 总结

MyISAM和InnoDB存储引擎使用的锁:

表级锁和行级锁,和页锁的对比:

特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。

特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

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

从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用。

参考:
1.https://blog.csdn.net/qq_34337272/article/details/80611486
2.https://mp.weixin.qq.com/s/rFBFwzsDvoqptTubAqyuFQ
3.https://zhuanlan.zhihu.com/p/123962424

来源地址:https://blog.csdn.net/huangjhai/article/details/119011417

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯