21 MySQl锁机制

501次阅读

没有评论

共计 9344 个字符，预计需要花费 24 分钟才能阅读完成。

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

因为在数据库中，除了传统的计算资源（如 CPU、RAM、I/ O 等）的争用以外，数据也是一种供需要用户共享的资源
当并发事务同时访问一个共享的资源时, 有可能导致数据不一致、数据无效等问题
例如在上一篇介绍过的事务并发情况下出现的读现象: 脏读、不可重复读、幻读等
为了解决这些方法, 主流的数据库软件都提供了 锁机制, 以及事务隔离级别 的概念
而锁机制可以将并发的数据访问顺序化, 以 保证数据库中数据的一致性和有效性

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

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

按照按锁的粒度划分 : 可分为行级锁、表级锁、页级锁
按照级别划分 : 可分为共享锁、排他锁、意向锁、间隙锁(Next-Key)
按照使用方式分 : 可分为乐观锁、悲观锁
按照加锁方式分 : 可分为自动锁、显式锁
按照操作划分 : DDL 锁、DML 锁
其他 : 死锁、MVCC

DML 锁(data locks, 数据锁)，用于保护数据的完整性, 其中包括行级锁(Row Locks (TX 锁))、表级锁(table lock(TM 锁))
DDL 锁(dictionary locks，数据字典锁), 用于保护数据库对象的结构, 如表、索引等的结构定义; 其中包排他 DDL(Exclusive DDL lock)、共享 DDL 锁(Share DDL lock), 可中断解析锁(Breakable parse locks)

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

行级锁定义

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

特点

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

支持引擎

Innodb 引擎

语法

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

表级锁定义

表级锁是 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;  # 再次更新可以成功

21 MySQl 锁机制

页级锁定义

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

特点

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

支持引擎

BDB 引擎

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

21 MySQl 锁机制

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

21 MySQl 锁机制

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

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

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

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

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

对于 insert、update、delete 语句, InnoDB 会自动给涉及的数据加锁，而且是排他锁 (简称 X 锁)

# 手动开启事务 1, 对 id=1 的记录进行增删改操作, 并且为提交状态
# 开启事务 2, 也对 id=1 的记录进行增删改操作
# 发现阻塞在原地, 一段时间后显示超时 : ERROR 1205 (HY000): Lock wait timeout exceeded ....

21 MySQl 锁机制

对于普通的 select 语句, InnoDB 不会加任何锁, 需要我们手动自己加, 可以加两种类型的锁 :

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

共享锁定义

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

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

共享锁用法

select * from [表名] where [条件] lock in share mode;

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

排它锁定义

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

排它锁用法

select * from [表名] where [条件] for update;

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

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

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

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

21 MySQl 锁机制

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

21 MySQl 锁机制

概念

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

作用

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

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

Innodb 中的两种意向锁

意向共享锁(IS) : 事务打算给数据行共享锁; 事务在给一个数据行加共享锁前必须先取得该表的 IS 锁

意向排他锁(IX) : 事务打算给数据行加排他锁; 事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

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

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

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

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

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

这种情况也可以考虑一次性锁定事务涉及的表, 从而避免死锁、减少数据库因事务回滚带来的开销当然, 应用中这两种事务不能太多, 否则, 就应该考虑使用 Myisam

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况, 在着手根据状态量来分析改善

show status like "innodb_row_lock%";  # 查看行锁状态

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

Myisam 和 Memory 默认采用表级锁(table-level locking)
BDB 采用页面锁 (page-level locking) 或表级锁，默认为页面锁
Innodb支持行级锁 (row-level locking) 和表级锁, 默认为行级锁 (偏向于写)

Innodb 四种锁定模式的共存逻辑关系 :

	共享锁(S)	排他锁(X)	意向共享锁(IS)	意向排他锁(Ⅸ)
共享锁(S)	兼容	冲突	兼容	冲突
排他锁(X)	冲突	冲突	冲突	冲突
意向共享锁(IS)	兼容	冲突	兼容	兼容
意向排他锁(Ⅸ)	冲突	冲突	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放

Record Lock : 单个行记录上的锁
Gap Lock : 间隙锁, 锁定一个范围, 但不包括记录本身; GAP 锁的目的, 是为了防止同一事务的两次当前读, 出现幻读的情况
Next-Key Lock : 等于 Record Lock 结合 Gap Lock, 也就说 Next-Key Lock 既锁定记录本身也锁定一个范围, 特别需要注意的是, InnoDB 存储引擎还会对辅助索引下一个键值加上 gap lock

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

21 MySQl 锁机制

示例 : 假设 emp 表中的有 100 条数据, 当使用 select * from emp where id>=100 for update; 语句的时候, 它是一个范围条件检索, 并且命中了索引, InnoDB 不仅会对符合条件的 emp_id 值为 100 的记录加锁, 也会对 em_pid 大于 100 (这些记录并不存在) 的 "间隙" 加锁

ps : 对于行的查询, Innodb 采用的都是 Next-Key Lock, 主要目的是解决幻读的问题, 以满足相关隔离级别以及恢复和复制的需要

MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在 InnoDB 中，锁是逐步获得的，就造成了死锁的可能

准备工作

create index index_id on t01;  # 删除之前的索引
alter table t01 modify id int primary ket;  # 将 id 字段设置成主键
create index index_id on t01(id);  # 创建聚集索引
create index index_name on t01(name);  # 创建辅助索引(非聚集索引)
desc t01;  # 查看表结构

21 MySQl 锁机制

开始试验

21 MySQl 锁机制

第二个死锁问题，只有多个事务同时运行的情况下才可能出现，但隐蔽性极强，虽然每个 Session 都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的 MySQL 加锁的规则。针对 Session 1，从 name 索引出发，读到的 [hdc, 1]，[hdc, 6] 均满足条件，不仅会加 name 索引上的记录 X 锁，而且会加聚簇索引上的记录 X 锁，加锁顺序为先 [1,hdc,100]，后[6,hdc,10]。而 Session 2，从 pubtime 索引出发，[10,6],[100,1] 均满足过滤条件，同样也会加聚簇索引上的记录 X 锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟 Session 1 的加锁顺序正好相反，如果两个 Session 恰好都持有了第一把锁，请求加第二把锁，死锁就发生了

死锁问题涉及到的问题

在 MySQL 中, 行级锁并不是直接锁记录, 而是锁索引; 索引分为主键索引和非主键索引两种

如果一条 sql 语句操作了主键索引, MySQL 就会锁定这条主键索引

如果一条语句操作了 非主键索引 , MySQL 会 先锁定该非主键索引, 再锁定相关的主键索引

在 update、delete 操作时, MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录, 而且会锁定相邻的键值, 即所谓的 next-key locking

死锁产生的本质原理

死锁的发生与否, 并不在于事务中有多少条 SQL 语句, 死锁的关键在于 : 两个 (或以上) 的 Session 加锁的 顺序不一致

而使用上面提到的, 分析 MySQL 每条 SQL 语句的加锁规则, 分析出每条语句的加锁顺序

然后检查多个并发 SQL 间是否存在以相反的顺序加锁的情况, 就可以分析出各种潜在的死锁情况, 也可以分析出线上死锁发生的原因

发生死锁后, InnoDB 一般都可以检测到, 并使一个事务释放锁回退, 另一个获取锁完成事务, 上面实验也证明了, 但也有多种方法可以避免死锁:

如果不同程序会并发存取多个表, 尽量约定以 相同的顺序访问表, 可以大大降低死锁机会
在同一个事务中, 尽可能做到一次锁定所需要的所有资源, 减少死锁产生概率
对于非常容易产生死锁的业务部分, 可以尝试使用升级锁定颗粒度, 通过表级锁定来减少死锁产生的概率
在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记录, 也可以大大降低出现死锁的可能

数据库管理系统 (DBMS) 中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性

乐观并发控制 (乐观锁) 和悲观并发控制 (悲观锁) 是并发控制主要采用的技术手段。

无论是悲观锁还是乐观锁, 都是人们定义出来的概念, 可以认为是一种思想; 其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念, 像 memcache、hibernate、tair 等都有类似的概念

针对于不同的业务场景, 应该选用不同的并发控制方式; 所以, 不要把乐观并发控制和悲观并发控制狭义的理解为 DBMS 中的概念, 更不要把他们和数据中提供的锁机制 (行锁、表锁、排他锁、共享锁) 混为一谈; 其实, 在 DBMS 中, 悲观锁正是利用数据库本身提供的锁机制来实现的

悲观的认为操作数据库就是修改数据, 为了避免数据库中的数据同时被修改, 直接对该操作加锁处理

当我们要对一个数据库中的一条数据进行修改的时候, 为了避免同时被其他人修改, 最好的办法就是直接对该数据进行加锁以防止并发

这种借助数据库锁机制在修改数据之前先锁定, 再修改的方式被称之为悲观并发控制 (又名“悲观锁”，Pessimistic Concurrency Control, 缩写“PCC”)

在关系数据库管理系统里，悲观并发控制 (又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”) 是一种并发控制的方法

它可以阻止一个事务以影响其他用户的方式来修改数据; 如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放, 其他事务才能够执行与该锁冲突的操作

悲观并发控制主要用于数据争用激烈的环境, 以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中

悲观锁, 正如其名, 它指的是对数据被外界 (包括本系统当前的其他事务, 以及来自外部系统的事务处理) 修改持保守态度(悲观), 因此, 在整个数据处理过程中, 将数据处于锁定状态;

悲观锁的实现, 往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性, 否则, 即使在本系统中实现了加锁机制, 也无法保证外部系统不会修改数据) , 现在互联网高并发的架构中, 受到 fail-fast 思路的影响, 悲观锁已经非常少见了

在对任意记录进行修改前, 先尝试为该记录加上排他锁 (exclusive locking)
如果加锁失败, 说明该记录正在被修改, 那么当前查询可能要等待或者抛出异常; 具体响应方式由开发者根据实际需要决定
如果成功加锁, 那么就可以对记录做修改, 事务完成后就会解锁了
其间如果有其他对该记录做修改或加排他锁的操作, 都会等待我们解锁或直接抛出异常

ps : 行锁、表锁、读锁、写锁都是在操作之前先上排他锁

悲观并发控制主要用于数据争用激烈的环境, 以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中

优点

悲观并发控制实际上是“先取锁再访问”的保守策略, 为数据处理的安全提供了保证

缺点

在效率方面, 处理加锁的机制会让数据库产生额外的开销, 还有增加产生死锁的机会

在只读型事务处理中由于不会产生冲突, 也没必要使用锁, 这样做只能增加系统负载

会降低了并行性, 一个事务如果锁定了某行数据, 其他事务就必须等待该事务处理完才可以处理那行数

乐观的认为操作数据库不会造成冲突, 只有在对数据进行更新的时候才会进行校验. 如果冲突就返回错误让用户决定如何去做

在关系数据库管理系统里, 乐观并发控制 (又名“乐观锁”, Optimistic Concurrency Control, 缩写“OCC”) 是一种并发控制的方法

它假设多用户并发的事务在处理时不会彼此互相影响, 各事务能够在不产生锁的情况下处理各自影响的那部分数据

在提交数据更新之前, 每个事务会先检查在该事务读取数据后, 有没有其他事务又修改了该数据

如果其他事务有更新的话, 正在提交的事务会进行回滚; 乐观事务控制最早是由孔祥重 (H.T.Kung) 教授提出

乐观锁 (Optimistic Locking) 相对悲观锁而言, 乐观锁假设认为数据一般情况下不会造成冲突, 所以在数据进行提交更新的时候, 才会正式对数据的冲突与否进行检测, 如果发现冲突了, 则让返回用户错误的信息, 让用户决定如何去做

相对于悲观锁, 在对数据库进行处理的时候, 乐观锁并不会使用数据库提供的锁机制; 一般的实现乐观锁的方式就是记录 数据版本

数据版本 : 为数据增加的一个版本标识, 当读取数据时, 将版本标识的值一同读出, 数据每更新一次, 同时对版本标识进行更新

当我们提交更新的时候, 判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对, 如果数据库表当前版本号与第一次取出来的版本标识值相等, 则予以更新, 否则认为是过期数据