MySQL 各种锁1

MySQL知识总结

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

22 锁

事务一致性问题

出现并发事务对同一数据进行读写写写时会出现一致性问题,对于写写情况,多个未提交的事务对于同一记录进行改动时,需要串行执行,实现的方式是对记录进行加锁

写写情况

事务对记录进行修改时,查看内存中是否有锁结构与该记录关联,锁的基本信息有trx信息(与锁关联的事务)、is_waitting(标志是否等待),持有锁的事务提交后,会唤醒等待中事务。

读写情况

不同隔离级别下可以解决不同的事务并发问题,MySQL的可重复读隔离级别下,很大程度解决了幻读,MySQL解决脏读不可重复读幻读的方案有:

  1. MVCC:通过生成Read View,控制了事务对记录版本的可见性,使得事务只能读取到Read View生成之前已提交的事务做的修改,MVCC中,读取记录的历史版本与改动记录的最新版本并不冲突,读写不冲突
  2. 加锁:有些业务场景下不允许读取记录的历史版本,而要读取记录的最新版本,对于这种需求可以使用加锁完成,这将使得读写操作需要串行执行。

提示

使用加锁解决幻读的方式会有点复杂,因为需要找到新插入的记录。

一致性读

事务利用MVCC的读取操作称为一致性读快照读,读已提交、可重复读的 隔离级别下,普通select语句都是这种方式。这种方式不会对任何记录加锁。

锁定读

MySQL中的锁有

  • 共享锁(Shared Lock,S锁):读取记录时,需要获取记录的S锁,S锁与X锁不兼容。
  • 排它锁(Exclusive Lock,X锁):修改记录时,需要获取记录的X锁,X锁与S锁、X锁不兼容。

锁定读的语句

对读取的记录加S锁

select *
from single_table lock in share mode;

对读取的记录加X锁

select *
from single_table for update;

写操作

写操作包括insertupdatedelete,执行这些操作会加锁

  • update:对记录进行update操作有3种情况
    • 没修改键值且更新的列占用的存储空间不变,需要在B+树定位到记录的位置,再获取记录的X锁,然后在记录的原位置进行修改
    • 没修改键值但至少1个更新的列占用的空间有变化,需要定位到该记录,获取记录的X锁,将记录删除,最后再插入一条记录,被删除的记录上的锁会被转移到新插入的记录上
    • 更新了键值,就需要对原记录进行delete、执行insert操作插入更新后的记录
  • delete:在B+树定位到记录的位置,再获取记录的X锁,执行delete mark操作
  • insert:一般情况下,新插入一条记录受隐式锁保护,不在内存中创建锁结构

在B+树定位到被修改记录的位置,再获取记录的X锁看做成是获取X锁的锁定读操作

一般情况下,事务提交或终止时,事务加的锁才会释放。

多粒度锁

一个事务可以对表加锁,表锁的粒度比行锁小,性能消耗低,表锁有

  • S锁,别的事务可以获取表中记录S锁表S锁
  • X锁,与S锁、X锁不兼容

表的X锁与其他锁不兼容,在加表X锁时,需要判断表是否有记录S、X锁,为了提高效率,InnoDB设计了意向锁

  • 意向共享锁(Intention Shared Lock,IS锁):事务准备加记录S锁时,向表加一个IS锁
  • 意向排它锁(Intention Exclusive Lock,IX锁):事务准备加记录X锁时,向表加一个IX锁
  • IS锁IX锁兼容
  • 表X锁与任何锁不兼容

MySQL的行锁与表锁

InnoDB表锁

表S、X锁

对表进行selectinsertupdatedelete操作时,不会加表锁

alter tabledrop tableDDL语句和增删改查操作,这2类语句操作不能同时执行,其中的一类会被阻塞,但这是通过server层使用元数据锁实现的,并不是使用InnoDB的表锁实现的。

InnoDB的表锁只有在特殊情况下会使用,在系统变量autocommit = 0innodb_table_locks = 1情况下,可以执行一下语句:

  • lock tables single_table read;:表S锁
  • lock tables single_table write;:表X锁
  • 使用InnoDB的表锁会降低并发能力

表IS、IX锁

即前文介绍的锁

表级Auto-inc锁

系统给auto_increment修饰的列进行递增赋值的方式有2个:

  • Auto-inc锁:插入语句执行时要加锁,然后为插入语句分配递增值,没有持有Auto-inc锁的事务执行插入操作会被阻塞,插入语句完成后就释放锁(插入的记录数量不能确定,一般使用这种方式)
  • 轻量级锁:插入语句生成auto_increment修改列的分配的值时加锁,生成需要的值后就将锁释放(插入记录的值可以确定,一般采用这种方式)

innodb_autoinc_lock_mode 系统变量的值

  • 0:一律使用Auto-inc锁
  • 1:2中方式混用
  • 2:一律使用轻量级锁(可能造成不同事务的自增值是交叉的,主从复制场景不安全)

InnoDB行锁

行锁有Record LockGap LockNext-Key LockInsert Intention Lock

Record Lock (lock_rec_not_gap)

对一条记录加的锁

Gap Lock (lock_gap)

MySQL在可重复读隔离级别下,很大程度解决了幻读,解决的方式除了MVCC外,还有加间隙锁,给记录加间隙锁,不允许事务在范围(上一条记录, 被加锁记录)插入记录,只有持有了间隙锁的事务提交后,将锁释放,其它事务才可以在该范围内插入记录。

间隙锁有S、X之分,但它们的作用是一样的,防止幻影记录的插入。

如何保证范围(最后一条记录, +∞)不插入幻影记录?在数据页中,有2条伪记录,对应上界(页面最小的记录)、下界(页面最大的记录),只要对下界加上间隙锁,就可以保证该范围不出现幻读。

Next-Key Lock (lock_ordinary)

临键锁本质是记录锁间隙锁的组合,能保护记录且阻止新记录插入到记录前。

Insert Intention Lock (lock_insert_Intention)

事务在插入一条记录时,需要判断插入位置是否有间隙锁,如果有,就需要在内存生成一个插入意向锁并等待,持有意向锁的事务不会互相阻塞,意向锁不会阻止其他事务继续在该记录上加任何类型的锁。

隐式锁

在内存生成锁有一定的成本,InnoDB有隐式锁,一般情况下,插入操作不会在内存中生成锁,但这会出现一些问题需要解决:

  • 有事务立即执行锁定读,如果允许这种情况,就出现脏读
  • 有事务立即执行对这条记录继续更新操作,如果允许这种情况,就出现脏写

解决上面的问题需要依靠事务Id

  • 聚簇索引记录:记录上有trx_id记录着最后改动此记录的事务Id,如果要对记录加锁,首先会判断事务Id对应的事务是否是活跃状态,如果不是则正常读取,如果是则帮助该事务对记录加X锁,并给自己加对应的锁,设置为等待状态。
  • 二级索引记录:索引记录上没有trx_id,但二级索引页面Page Header有属性page_max_trx_id记录了最近对页面更改的最大事务Id,如果当前事务Id大于该值,说明对该页面修改的事务已经提交,否则需要回表在聚簇索引执行上面的判断

隐式锁起到了延迟创建锁的作用,避免部分消耗,它起到的作用是一样的

InnoDB锁内存结构

InnoDB在对多条记录加锁时,记录的锁可以放到一个锁结果,但要符合以下条件

  1. 在同一个事务进行的加锁
  2. 被加锁的记录在同一个页面
  3. 加锁类型一样
  4. 等待状态一样

锁的结构

  1. 锁的事务信息:指针,可以得到事务的相关信息

  2. 索引信息:对行锁来说,要记录记录所属的索引

  3. 表锁/行锁信息

    • 表锁:对哪个表加锁

    • 行锁

      • Space Id:记录所在的表空间
      • Page Number:记录所在页面的页号
      • n_bits:比特位的个数,对应下方的一堆比特位
  4. type_mode:32位的比特位,分为3部分

    • lock_mode:低4位

      • lock_IS
      • lock_IX
      • lock_S
      • lock_X
      • lock_auto_inc
    • lock_type:5~8位

      • lock_table
      • lock_rec
    • rec_lock_type:其余高位

      • lock_rec_not_gap
      • lock_gap
      • lock_ordinary
      • lock_insert_intention
      • lock_wait:表示锁中is_waitting,对应的比特位为1,则为等待状态
      • 其他类型
  5. 其他信息:管理系统运行过程中的各种锁结构,而设计了各种哈希表、链表

  6. 一堆比特位:对行锁来说,比特位对应着页面中的记录,比特位映射记录头信息中heap_no`,为了编码方便,映射的方式比较特殊

提示

假如事务T1记录R加锁后,事务T2记录R加锁,但状态会被设置为等待状态,并不再继续对其他记录加锁(T2要对多条记录加锁,但一开始对记录R加锁就等待),当T1释放记录R的锁后,T2可以获得记录R的锁,并继续对其他记录加锁,并且能复用T1记录R加锁的锁结构

语句加锁过程

  • 普通select语句
  • 锁定读语句
  • 半一致性语句
  • insert语句

表结构

create table hero (
    id      int          not null primary key,
    name    varchar(100) null,
    country varchar(100) null
);

create index idx_name
    on hero (name);

insert into hero
values (1, 'l刘备', '蜀'),
       (3, 'z诸葛亮', '蜀'),
       (8, 'c曹操', '魏'),
       (15, 'x荀彧', '魏'),
       (20, 's孙权', '吴');

普通select语句

在不同的隔离级别下,普通select语句具有不同的表现,说MySQL的可重复读隔离级别级别下很大程度避免了幻读,出现下面这种情况会出现幻读。

begin;
select * from hero where id =30;

# 事务T2执行并提交 insert into hero values(30,'x','x');

update hero set name='xx' where id=30;

# 幻读 可以查询到id为30的记录
select * from hero where id =30;

序列化隔离级别

系统变量autocommit = 0时,普通的select语句会被转换为S锁的锁定读,如果该变量为1,普通的select语句不会加锁,因为自动提交默认一个事务只有一个语句,只执行一条语句不会出现不可重复读幻读

锁定读

锁定读包括:

  • select ... lock in share mode
  • select ... for update
  • update
  • delete

为记录加锁会受其他因素影响

  1. 事务的隔离级别
  2. 语句执行时使用的索引的类型
  3. 语句类型
  4. 匹配模式:扫描区间是单点扫描区间,那么匹配模式就是精确匹配
  5. 是否是唯一性搜索:在扫描某个区间的记录前,就确定了扫描区间只有一条记录称为唯一性搜索,查询需要符合一下条件:
    • 匹配模式是精确匹配
    • 使用的索引是主键索引、唯一索引(查询条件不可以是索引列 is Null的形式,索引包含多个列,需要利用所有列)

一般语句加锁过程

事务执行过程中加的锁一般需要在事务提交、终止时才会释放锁,但在隔离级别在读已提交及以下,会提前将不符合查询条件记录的锁释放。

把锁定读的执行看作是依次读取若干个扫描区间记录,一般情况下,读取扫描区间记录的过程是:

  1. 在B+树子节点定位扫描区间的第一条记录,作为当前记录

  2. 对记录加锁:一般情况下,可重复读级别及以上临键锁读已提交及以下记录锁

  3. 判断索引条件下推的条件是否成立:条件索引下推只适用于select语句

    • 这个过程不会释放锁
    • 这个过程会判断是否符合扫描区间,不符合扫描区间则向server层返回查询完成信息
    • 符合索引条件下推条件:执行4
    • 不符合索引条件下推条件:执行7
  4. 回表操作:使用的是二级索引则回表,对聚簇索引记录加记录锁

  5. 判断边界条件是否成立

    • 不符合边界条件:向server层返回查询完成的信息(读已提交及以下释放锁
  6. server层判断其他条件是否成立

    • 其他条件成立:将记录发送给客户端
    • 其他条件不成立:读已提交及以下释放锁
  7. 获取下一条记录,重新执行2

提示

updatedelete语句,对被更新、被删除的二级索引记录加的锁是隐式锁读已提交隔离级别及以下采用的是半一致性读

查询的匹配模式是精确匹配

  • 隔离级别读已提交及以下:不会为扫描区间后的下一条记录加锁
  • 隔离级别可重复读及以上:为下一条记录加间隙锁,扫描区间没有记录也加间隙锁(不是精确匹配,则加临键锁
select *
from hero
where name = 'c曹操' for update;
nameid
c曹操 临键锁(可重复读)8
l刘备 间隙锁(可重复读)1
s孙权20
x荀彧15
z诸葛亮3

扫描区间没有记录间隙锁(l刘备记录前)

select *
from hero
where name = 'g关羽' for update;
nameid
c曹操8
l刘备 间隙锁(可重复读)1
s孙权20
x荀彧15
z诸葛亮3

不是精确匹配,则加临键锁

select *
from hero
where name 'd'
  and name < 'l' for update; 
nameid
c曹操8
l刘备 临键锁(可重复读)1
s孙权20
x荀彧15
z诸葛亮3

查询使用聚簇索引,扫描区间是左闭右开则定位的扫描区间的第一条记录将使用记录锁

select *
from hero
where id = 8 for update;
idname
1l刘备
3z诸葛亮
8 记录锁(可重复读)c曹操
15 临键锁(可重复读)x荀彧
20 临键锁(可重复读)s孙权
下界 临键锁(可重复读)

查询是唯一性搜索,只要记录没有被delete mark,则加记录锁

select *
from hero
where id = 8 for update;

从右到左扫描且隔离级别可重复读及以上,则匹配的第一条记录的下一条记录加间隙锁

select *
from hero force index (idx_name)
where name 'c曹操'
  and name <= 'x荀彧'
  and country != '吴'
order by name desc for update;
nameid
c曹操8
l刘备 临键锁(可重复读)1
s孙权 临键锁(可重复读)20
x荀彧 临键锁(可重复读)15
z诸葛亮 间隙锁(可重复读)3

半一致性读

隔离级别读已提交及以下执行update语句时将使用半一致性读,当update语句读取已经被其他事务加锁的记录时,InnoDB会将该记录最新版本读出来,判断版本是否符合update的查询条件,如果符合,则对其加锁(防止update语句被别的事务阻塞),如果不符合则跳过

Insert语句

insert语句一般情况下是依靠隐式锁保护插入的记录的,事务在插入一条记录前,需要定位到B+树的位置,如果下一条记录加了间隙锁,事务就会为该记录加上插入意向锁,事务进入等待状态。

特殊情况

重复键

插入一条新记录时,需要确定新记录在B+树中的位置,在确定位置时发现记录中的主键、唯一索引列待插入的记录的主键、唯一索引列相同时,就会报错。在报错前,会为该记录将锁(记录锁或临键锁)。

外键检查

设置外键后,对子表进行插入时需要进行外键检查

  • 在主表上对应列上找到值:往子表插入记录前,会对主表的对应记录加S记录锁
  • 在主表上对应列上找不到值:插入失败,需要对记录加间隙锁(可重复读)

查看事务加锁情况

INNODB_LOCKS、INNODB_LOCK_WAITS表在MySQL 8被移除

select *
from information_schema.INNODB_TRX;

select *
from information_schema.INNODB_LOCKS;

select *
from information_schema.INNODB_LOCK_WAITS;
show engine innodb status;

# 设置系统变量,可以查看更多内容
set global innodb_status_output_locks = on;
# 事务部分
------------
TRANSACTIONS
------------
Trx id counter 63843
Purge done for trx's n:o < 63783 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284106697672496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 63842, ACTIVE 428 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 16316, query id 2700 localhost 127.0.0.1 root


#事务部分,更多内容
------------
TRANSACTIONS
------------
Trx id counter 63845
Purge done for trx's n:o < 63783 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284106697672496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 63843, ACTIVE 201 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 16316, query id 2793 localhost 127.0.0.1 root

# 给hero表加上了意向独占锁
TABLE LOCK table `mysql_run`.`hero` trx id 63843 lock mode IX

# 锁的结构 Space Id是384,页号3,n_bits是80,索引为主键索引
RECORD LOCKS space id 384 page no 3 n bits 80 index PRIMARY of table 

# 锁是记录锁
`mysql_run`.`hero` trx id 63843 lock_mode X locks rec but not gap


Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000f66d; asc      m;;
 2: len 7; hex eb000001c40110; asc        ;;
 3: len 7; hex 6ce58898e5a487; asc l      ;;#6ce58898e5a487 是 ‘l刘备’的utf8编码
 4: len 3; hex e89c80; asc    ;;
  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:创作都市 设计师:CSDN官方博客 返回首页
评论

打赏作者

011eH

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值