编程知识 cdmana.com

MySQL锁机制

引入

1.什么是锁

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制,我们称之为锁机制

2.为何要使用锁机制

  • 因为在数据库中,除了传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供需要用户共享的资源

  • 当并发事务同时访问一个共享的资源时,有可能导致数据不一致、数据无效等问题

  • 例如在上一篇介绍过的事务并发情况下出现的读现象: 脏读、不可重复读、幻读等

  • 为了解决这些方法, 主流的数据库软件都提供了锁机制, 以及事务隔离级别的概念

  • 而锁机制可以将并发的数据访问顺序化, 以保证数据库中数据的一致性和有效性

ps : 锁冲突也是影响数据库并发性能的一个重要因素, 对锁对数据库非常重要, 但也更加复杂

3.并发控制

  • 在计算机科学,特别是程序设计、操作系统、多处理机和数据库等领域,并发控制(Concurrency control)是确保及时纠正由并发操作导致的错误的一种机制
  • 为了更好的应对高并发, 封锁、时间戳、乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段

二.锁分类

  • 按照按锁的粒度划分 : 可分为行级锁、表级锁、页级锁

  • 按照级别划分 : 可分为共享锁、排他锁、意向锁、间隙锁(Next-Key)

  • 按照使用方式分 : 可分为乐观锁、悲观锁

  • 按照加锁方式分 : 可分为自动锁、显式锁

  • 按照操作划分 : DDL锁、DML锁

  • 其他 : 死锁、MVCC

三.DDL锁与DML锁

  • DML锁(data locks, 数据锁),用于保护数据的完整性, 其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))

  • DDL锁(dictionary locks,数据字典锁), 用于保护数据库对象的结构,如表、索引等的结构定义; 其中包排他DDL(Exclusive DDL lock)、共享DDL锁(Share DDL lock),可中断解析锁(Breakable parse locks)

四.MySQL中的行级锁、表级锁、页级锁 (按粒度分)

在DBMS中, 可以按照锁的粒度把数据库锁分为行级锁(Innodb引擎默认使用)、表级锁(Myisam引擎默认使用)和页级锁(BDB引擎默认使用)

1.行级锁

  • 行级锁定义

行级锁是Mysql中锁定粒度最细的一种锁, 表示只针对当前操作的行进行加锁; 行级锁能大大减少数据库操作的冲突; 其加锁粒度最小, 但加锁的开销也最大; 行级锁分为共享锁和排它锁

  • 特点

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

  • 支持引擎

Innodb 引擎

  • 语法
"共享锁(s)" : select * from [表名] where [条件] lock in share mode;"排它锁(x)" : select * from [表名] where [条件] for update;

2.表级锁 (偏向于读)

  • 表级锁定义

表级锁是MySQL中锁定粒度最大的一种锁, 表示对当前操作的整张表加锁, 它实现简单, 资源消耗较少, 被大部分MySQL引擎支持; 最常使用的Myisam与Innodb都支持表级锁定; 表级锁定分为表共享读锁(共享锁)和表独占写锁(排它锁)

  • 特点

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

  • 支持引擎

Myisam引擎、Memory引擎、Innodb引擎

  • 示例
"语法" : lock table [表名1] [resd|write],[表名2] [resd|write], ...; # 可以加读锁或者写作lock table user read; # 将表 user 加上写锁show open tables where in_user>=1;  # 查看当前会话锁定一次以上的表update user set name="song" where id=1; # 更新表数据(会提示表被锁定)unlock tables; # 释放当前会话持有的任何锁update user set name="song" where id=1; # 再次更新可以成功

image-20210228150932400

3.页级锁

  • 页级锁定义

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁; 表级锁速度快, 但冲突多, 行级冲突少, 但速度慢; 所以取了折衷的页级, 一次锁定相邻的一组记录; BDB支持页级锁

  • 特点

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

  • 支持引擎

BDB引擎

五.Innodb中的行级锁之共享锁与排它锁 (按级别分)

1.Innodb中行级锁的与表锁对比

  • InnoDB行锁不是直接锁记录, 而是锁索引, 这一点MySQL与Oracle不同, 后者是通过在数据块中对相应数据行加锁来实现的

  • InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据, InnoDB才使用行级锁, 否则,InnoDB将锁住所有行, 实现的效果相当于是表锁

  • 演示

create table t01(id int,name char(16)); # 创建表(并且不添加索引)insert t01 value(1,"aa"),(2,"bb"),(3,"cc"),(4,"dd"); # 插入记录# 开启两个会话窗口, 分别手动开启事务# 事务1对 id=2 进行锁行操作, 事务2对 id!=2 的行进行更新# 发现阻塞, 一段时间后显示超时 : ERROR 1205 (HY000): Lock wait timeout exceeded;....

image-20210228153157610

create index index_id on t01(id); # 为 id 字段创建索引desc t01; # 查看表结构# 再次重复上面开启事务的步骤

image-20210228154050025

2.行锁的实现原理

行锁锁的是索引, 索引又分为主键索引和非主键索引两种, 所以锁定的方式分为以下三种 :

  • 如果一条 sql 语句操作了主键索引, Mysql 就会锁定这条语句命中的主键索引(或称聚簇索引)
  • 如果一条语句操作了非主键索引(或称辅助索引), MySQL会先锁定该非主键索引, 再锁定相关的主键索引
  • 如果没有索引, InnoDB 会通过隐藏的聚簇索引来对记录加锁; 也就是说 : 如果不通过索引条件检索数据, 那么InnoDB将对表中所有数据加锁, 实际效果跟表级锁一样

3.实际应用中的问题

在实际应用中, 要特别注意InnoDB行锁的这一特性, 否则可能导致大量的锁冲突, 从而影响并发性能 :

  • 在不通过索引条件查询的时候, InnoDB 行锁锁定所有行, 效果相当于表锁
  • 当表有多个索引的时候, 不同的事务可以使用不同的索引锁定不同的行, 另外, 不论 是使用主键索引、唯一索引或普通索引, InnoDB 都会使用行锁来对数据加锁
  • 由于 MySQL 的行锁是针对索引加的锁, 不是针对记录加的锁, 所以虽然是访问不同行的记录, 但是如果是使用相同的索引键, 也还是会出现锁冲突的
  • 即便在条件中使用了索引字段, 但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的, 如果 MySQL 认为全表扫 效率更高, 比如对一些很小的表, 它就不会使用索引, 这种情况下 InnoDB 将锁住所有的行, 相当于表锁, 而不是行锁; 因此, 在分析锁冲突时, 别忘了检查 SQL 的执行计划, 以确认是否真正使用了索引

4.行级锁分为共享锁和排他锁

与对行处理有关的语句有 : insertupdatedeleteselect, 这四类语句在操作记录时, 都可以为行加上锁, 但需要注意的是 :

  • 对于 insert、update、delete语句, InnoDB会自动给涉及的数据加锁,而且是排他锁 (简称X锁)
# 手动开启事务1,对 id=1 的记录进行增删改操作, 并且为提交状态# 开启事务2,也对 id=1 的记录进行增删改操作# 发现阻塞在原地,一段时间后显示超时 : ERROR 1205 (HY000): Lock wait timeout exceeded ....

image-20210228163926773

  • 对于普通的 select 语句, InnoDB不会加任何锁, 需要我们手动自己加, 可以加两种类型的锁 :
"共享锁(s)" : select * from [表名] where [条件] lock in share mode;"排它锁(x)" : select * from [表名] where [条件] for update;

5.共享锁 (Share Lock)

  • 共享锁定义

共享锁又称为读锁, 简称S锁, 顾名思义, 共享锁就是多个事务对于同一数据可以共享一把锁, 获准共享锁的事务只能读数据, 不能修改数据直到已释放所有共享锁, 所以共享锁可以支持并发读

如果事务1对数据A加上共享锁后, 则其他事务只能对A再加共享锁或不加锁 (在其他事务里一定不能再加排他锁, 但是在事务1自己里面是可以加的), 反之亦然

  • 共享锁用法
select * from [表名] where [条件] lock in share mode;

在查询语句后面增加lock in share mode,Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时, 可以成功申请共享锁, 否则会被阻塞; 其他线程也可以读取使用了共享锁的表, 而且这些线程读取的是同一个版本的数据

6.排它锁 (Exclusive Lock)

  • 排它锁定义

排他锁又称为写锁, 简称X锁, 顾名思义, 排他锁就是不能与其他所并存, 如一个事务获取了一个数据行的排他锁, 其他事务就不能再对该行加任何类型的其他他锁 (共享锁和排他锁), 但是获取排他锁的事务是可以对数据就行读取和修改

  • 排它锁用法
select * from [表名] where [条件] for update;

在查询语句后面增加for update, Mysql会对查询结果中的每行都加排他锁, 当没有其他线程对查询结果集中的任何一行使用排他锁时, 可以成功申请排他锁, 否则会被阻塞

加过排他锁的数据行在其他事务种是不能修改数据的, 也不能通过for updatelock in share mode锁的方式查询数据, 但可以直接通过select ...from...查询数据, 因为普通select查询没有任何锁机制

7.共享锁与排它锁实验

建立了索引且命中的情况下:

  • 事务1对某记录加排它锁, 事务2无法对该记录进行"改"(三种操作)操作(上面已经演示过了)
  • 事务1对某记录加共享锁, 自己可读可写, 其他事务只能读不能写

image-20210228170050117

  • 当其他事务也加上共享锁时, 这时候所有的事务都只能进行读操作

image-20210228171920885

8.意向锁

  • 概念

意向锁是表级锁, 其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型

  • 作用

当一个事务在需要获取资源锁定的时候, 如果遇到自己需要的资源已经被排他锁占用的时候, 该事务可以需要锁定行的表上面添加一个合适的意向锁

如果自己需要一个共享锁, 那么就在表上面添加一个意向共享锁; 而如果自己需要的是某行(或者某些行)上面添加一个排他锁的话, 则先在表上面添加一个意向排他锁

  • Innodb中的两种意向锁
  • 意向共享锁(IS) : 事务打算给数据行共享锁; 事务在给一个数据行加共享锁前必须先取得该表的IS锁
  • 意向排他锁(IX) : 事务打算给数据行加排他锁; 事务在给一个数据行加排他锁前必须先取得该表的IX锁

ps : 意向锁是InnoDB自动加的,不需要用户干预

9.哪种情况使用表锁

绝大部分情况使用行锁, 但在个别特殊事务中, 也可以考虑使用表锁

  • 事务需要更新大部分数据, 表又较大

若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度

  • 事务涉及多个表, 较复杂, 很可能引起死锁, 造成大量事务回滚

这种情况也可以考虑一次性锁定事务.........

版权声明
本文为[程序猿欧文]所创,转载请带上原文链接,感谢
https://my.oschina.net/mikeowen/blog/4967227

Scroll to Top