锁
MySQL 并发事务访问相同记录的情况
1、读-读
(1)并发事务相继读取相同的记录
(2)读取操作本身不会对记录有任何影响,不会引起问题,所以允许这种情况发生
2、写-写
(1)并发事务相继改动相同的记录
(2)发生脏写问题,任何一种隔离级别都不允许这种问题的发生
(3)所以在多个未提交事务相继对一条记录做改动时,需要让它们排队执行,通过锁实现排队过程
(4)锁是一个内存中的结构,在事务执行前,是没有锁结构和记录进行关联的,
(5)锁结构的两个重要属性:trx 信息:代表这个锁结构是哪个事务生成;is_waiting:代表当前事务是否在等待
(6)不加锁:不需要在内存中生成对应锁结构,可以直接执行操作
(7)获取锁失败 / 加锁失败:在事务 A 提交之前,另一个事务 B 也想对该记录做改动,则查看是否有锁结构与这条记录关联,发现有一个锁结构与之关联后,也生成一个锁结构与这条记录关联,不过锁结构的 is_waiting 属性值为 true ,表示当前事务需要等待
(8)获取锁成功 / 加锁成功:在事务 A 提交之后,就释放该事务生成的锁结构,若存在事务 B 在等待获取锁,则把事务 B 对应的锁结构 is_waiting 属性设置为 false,然后把该事务对应的线程唤醒,让它继续执行
3、读-写、写-读
(1)一个事务进行读取操作,另一个进行改动操作
(2)可能发生脏读、不可重复读、幻读
(3)各个数据库厂商对 SQL 标准支持都可能不一样,MySQL 在 REPEATABLE READ 隔离级别上已经解决幻读
并发问题的解决方案
1、解决并发下的脏读、不可重复读、幻读
2、方案一:读操作利用多版本并发控制(MVCC),写操作进行加锁
(1)MVCC:生成一个 ReadView,通过 ReadView 找到符合条件的记录版本,历史版本由 undo 日志构建
(2)查询语句只能读到在生成 ReadView 之前,已提交事务所做的更改,不能查看在生成 ReadView 之前未提交的事务,或之后才开启的事务所做更改
(3)写操作针对最新版本的记录,读记录的历史版本和改动记录的最新版本本身并不冲突,即采用 MVCC 时,读、写不冲突
(4)普通 SELECT 在 READ COMMITTED、REPEATABLE READ 隔离级别下会使用 MVCC 读取记录
(5)在 READ COMMITTED 隔离级别下,一个事务在执行过程中,每次执行 SELECT 操作时,都会生成一个 ReadView,ReadView 就保证事务不可以读取到未提交事务所做更改,即避免脏读
(6)在 REPEATABLE READ 隔离级别下,一个事务在执行过程中,只有第一次执行 SELECT 操作才会生成一个 ReadView,之后 SELECT 操作都复用 ReadView,即避免不可重复读、幻读
3、方案二:读、写操作都采用加锁的方式
(1)若业务场景不允许读取记录的旧版本,而是每次都必须去读取记录的最新版本,比如:银行存款的事务中,在读取记录时需要对其进行加锁操作,即读操作和写操作需要排队执行
(2)可以通过对记录加锁,来解决脏读、不可重复读
(3)无法采用加行锁方式,解决幻读,因为当前事务在第一次读取记录时幻影记录并不存在
(4)需要加表锁才能解决幻读
(5)READ COMMITTED 解决脏读问题,在数据读取时添加共享锁,执行完语句后释放锁,数据写入时添加排它锁,事务提交后释放锁
(6)REPEATABLE READ 解决不可重复读问题,在数据读取时添加共享锁,事务提交后释放锁,数据写入时添加排它锁,事务提交后释放锁
4、对比
(1)MVCC:读-写并不冲突,性能更高
(2)加锁:读-写需要排队执行,影响性能
(3)一般情况下采用 MVCC 解决读-写并发执行问题,特殊业务必须采用加锁
锁不同角度的分类
对数据操作的类型划分
1、读锁 / 共享锁:用 S 表示,针对同一份数据,多个事务的读操作,可以同时进行相互不阻塞
2、写锁 / 排他锁:用 X 表示,当前写操作没有完成前,它会阻断其他写锁和读锁,确保在给定的时间里,只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源
3、对于 InnoDB,读锁、写锁可以加在表上,也可以加在行上
4、同一张表或记录的锁的兼容性
锁定读
1、读取一条记录时,需要获取该记录 S 锁,是不严谨的,有时需要在读取记录时,就获取记录 X 锁,来禁止别的事务读写该记录,为此 MySQL 提出两种特殊 SELECT 语句格式
2、对读取的记录加 S 锁
SELECT …… LOCK IN SHARE MODE;
#或(8.0 新增语法)
SELECT …… FOR SHARE;
3、对读取的记录加 X 锁
SELECT …… FOR UPDATE;
4、MySQL 8.0 新特性
(1)在 5.7 及之前版本,SELECT …… FOR UPDATE,如果获取不到锁,则一直等待,直到 innodb_lock_wait_timeout 超时
(2)在 8.0 版本中,SELECT ... FOR UPDATE,添加 NOWAIT、SKIP LOCKED 语法,跳过锁等待,或者跳过锁定
(3)NOWAIT:如果查询的行已经加锁,会立即报错返回
(4)SKIP LOCKED:如果查询的行已经加锁,只返回结果中不包含被锁定的行
写操作
1、DELETE:先在 B+ 树中定位到这条记录的位置,然后获取这条记录的 X 锁,再执行 delete mark 操作
2、UPDATE
(1)情况一:未修改该记录的键值,并且被更新的列占用的存储空间在修改前后未发生变化,则先在 B+ 树中定位到这条记录的位置,然后再获取一下记录的 X 锁,最后在原记录的位置进行修改操作
(2)情况二:未修改该记录的键值,并且至少有一个被更新的列,占用存储空间在修改前后发生变化,则先在 B+ 树中定位到这条记录的位置,然后获取一下记录的 X 锁,将该记录彻底删除掉,即把记录彻底移入垃圾链表,最后再插入一条新记录,新插入的记录由 INSERT 操作提供的隐式锁进行保护
(3)情况三:修改该记录的键值,则相当于在原记录上做 DELECT 操作之后,再一次 INSERT 操作,加锁操作需要按照 DELETE、INSERT 规则进行
3、INSERT:一般情况下,新插入一条记录的操作并不加锁,通过隐式锁的结构,保护新插入的记录,在本事务提交前,不被别的事务访问
从数据操作的粒度划分
1、尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但管理锁耗费资源,涉及获取、检查、释放锁等动作,因此数据库系统需要平衡高并发响应、系统性能
2、锁的粒度分为表级锁、页级锁、行锁
表锁
1、该锁会锁定整张表,是 MySQL 中最基本的锁策略,并不依赖于存储引擎,并且表锁是开销最少的策略
2、由于表级锁一次会将整个表锁定,所以可以很好的避免死锁的问题,锁的粒度大所带来最大的负面影响,就是出现锁资源争用的概率也会最高,导致并发率大打折扣
表级别的 S 锁、X 锁
1、在对某个表执行 SELECT、INSERT、DELETE、UPDATE 时,InnoDB 不会为这个表添加表级别的 S 锁或 X 锁
(1)在对某个表执行 ALTER TABLE、DROP TABLE 这类的 DDL 语句时,其他事务对这个表并发执行 SELECT、INSERT、DELETE、UPDATE 语句会发生阻塞
(2)同理,某个事务中对某个表执行 SELECT、INSERT、DELETE、UPDATE 时,在其他会话中对这个表执行 DDL 语句也会发生阻塞
(3)以上过程通过在 server 层使用元数据锁(Metadata Locks,简称 MDL)结构实现
2、手动增加表锁
(1)一般情况下,不会使用 InnoDB 提供的表级别 S 锁和 X 锁,只在一些特殊情况下,如崩溃恢复过程中使用
(2)手动获取 InnoDB 加表级别的 S 锁
LOCK TABLES 表名 READ;
(3)手动获取 InnoDB 加表级别的 X 锁
LOCK TABLES 表名 WRITE;
(4)尽量避免在 InnoDB 的表上使用 LOCK TABLES 手动锁表,因为并不会提供额外保护,只会降低并发能力
3、查看表上加过的锁
SHOW OPEN TABLES;
#或
SHOW OPEN TABLES WHERE In_use > 0;
4、释放当前表的表锁
UNLOCK tables;
5、不同存储引擎
(1)MyISAM 在执行 SELECT 前,会给涉及的所有表加读锁,在执行增删改操作前,会给涉及的表加写锁
(2)InnoDB 不会为表添加表级别的读锁或写锁
8、MySQL 表级锁有两种模式
(1)表共享读锁(Table Read Lock)
(2)表独占写锁(Table Write Lock)
意向锁 (intention lock)
1、InnoDB 支持多粒度锁(multiple granularity locking),允许行级锁与表级锁共存,意向锁是其中一种表锁
(1)意向锁为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存
(2)意向锁是一种不与行级锁冲突的表级锁
(3)表明某个事务正在某些行持有锁,或该事务准备去持有锁
2、分类
(1)意向共享锁(intention shared lock,IS):事务有意向对表中的某些行加共享锁(S 锁),事务要获取某些行的 S 锁,必须先获得表的 IS 锁
(2)意向排他锁(intention exclusive lock,IX):事务有意向对表中的某些行加排他锁(X 锁),事务要获取某些行的 X 锁,必须先获得表的 IX 锁
(3)意向锁是由存储引擎维护,用户无法手动操作意向锁,在为数据行加共享锁 / 排他锁之前,InooDB 会先获取该数据行所在数据表的对应意向锁
3、若事务试图在该表级别上,应用共享锁或排它锁
(1)如果没有意向锁存在,则需要去检查各个页或行是否存在锁
(2)如果存在意向锁,则受到由其他事务控制的表级别意向锁的阻塞,新事物在锁定该表前,不必检查各个页或行锁,而只需检查表上的意向锁,即给更大一级别的空间示意里面是否已经上过锁
(3)如果事务想要获取数据表中某些记录的共享锁,就需要在数据表上添加意向共享锁;如果事务想要获取数据表中某些记录的排它锁,就需要在数据表上添加意向排他锁
(4)意向锁会告诉其他事务已经有人锁定了表中的某些记录
4、兼容互斥性
(1)意向锁之间
(2)意向锁与表排他锁 / 普通共享锁,意向锁不会与行级共享锁 / 行级排他锁互斥
5、意向锁的并发性
(1)意向锁不会与行级共享锁 / 行级排他锁互斥
(2)意向锁并不会影到多个事务对不同数据行加排他锁时的并发性
(3)意向锁在保证并发性的前提下,实现行锁和表锁共存,且满足事务隔离性要求
自增锁(AUTO-INC 锁)
1、插入自增数据的三种方式
(1)简单插入(Simple inserts):可以预先确定要插入的行数(当语句被初始处理时)的语句,包括没有嵌套子查询的单行和多行 INSERT……VALUES() 和 REPLACE 语句
(2)批量插入(Bulk inserts):不知道要插入的行数(和所需自动递增值的数量)的语句,比如:INSERT……SELECT、REPLACE……SELECT、LOAD DATA 语句,但不包括纯 INSERT,InnoDB 在每处理一行,为 AUTO_INCREMENT 列分配一个新值
(3)混合模式插入(Mixed-mode inserts):Simple inserts 语句,但是指定部分新行的自动递增值;另一种混合模式插入是 INSERT……ON DUPLICATE KEY UPDATE
2、MySQL 采用自增锁实现插入自增数据
(1)AUTO-INT 锁是当向使用含有 AUTO_INCREMENT 列的表中插入数据时,需要获取的一种特殊的表级锁
(2)在执行插入语句时,在表级别加一个 AUTO-INT 锁,然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值,在该语句执行结束后,再把 AUTO-INT 锁释放
(3)一个事务在持有 AUTO-INC 锁的过程中,其他事务的插入语句都要被阻塞,可以保证一个语句中分配的递增值是连续的,但并发性不高
(4)InnoDB 通过 innodb_autoinc_lock_mode 不同取值,来提供不同的锁定机制,来显著提高SQL语句的可伸缩性和性能
3、innodb_autoinc_lock_mode = 0
(1)传统锁定模式
(2)在此锁定模式下,所有类型 INSERT 都会获得一个特殊的表级 AUTO-INC 锁,用于插入具有 AUTO_INCREMENT 列的表
(3)该模式每当执行 INSERT 时,都会得到一个表级 AUTO-INC 锁,使得语句中生成 auto_increment 为顺序,且在 binlog 中重放时,保证 master 与 slave 中数据 auto_increment 是相同的
(4)因为是表级锁,当在同一时间多个事务中执行 INSERT 时,对于 AUTO-INC 锁的争夺会限制并发能力
4、innodb_autoinc_lock_mode = 1
(1)连续锁定模式
(2)在 MySQL 8.0 之前,连续锁定模式为默认
(3)对于 Bulk Inserts 仍使用 AUTO-INC 表级锁,并保持到语句结束,这适用于所有 INSERT……SELECT、REPLACE……SELECT、LOAD DATA语句
(4)同一时刻只有一个语句可以持有 AUTO-INC 锁
(5)对于 Simple inserts(要插入的行数事先已知),则通过在 mutex(轻量锁)的控制下,获得所需数量的自动递增值,避免表级 AUTO-INC 锁, 它只在分配过程的持续时间内保持,而不是直到语句完成
(6)不使用表级 AUTO-INC 锁,除非 AUTO-INC 锁由另一个事务保持
(7)如果另一个事务保持 AUTO-INC 锁,则 Simple inserts 仍需等待 AUTO-INC 锁,将其视作 Bulk Inserts
5、innodb_autoinc_lock_mode = 2
(1)交错锁定模式
(2)从 MySQL 8.0 开始,交错锁模式是默认设置
(2)在该模式下,所有类型 INSERT 语句都不会使用表级 AUTO-INC 锁,并且可以同时执行多个语句
(3)这是最快和最可拓展的锁定模式,但是当使用基于语句的复制或恢复方案时,从二进制日志重播 SQL 语句时,这是不安全的
(4)在此模式下,自动递增值保证在所有并发执行的所有类型的 INSERT 语句中是唯一且单调递增,但由于多个语句可以同时生成数字,即跨语句交叉编号,为任何给定语句插入的行生成的值可能不是连续的
(5)如果执行 Simple inserts,其中要插入的行数已提前知道,为单个语句生成的数字不会有间隙
(6)当执行 Bulk inserts、Mixed-mode inserts 时,在由任何给定语句分配的自动递增值中可能存在间隙
元数据锁(MDL 锁)
1、MySQL 5.5 引入 meta data lock,简称 MDL 锁,属于表锁
2、作用:保证读写的正确性
3、当对一个表增删改查时,加 MDL 读锁
4、当对表做结构变更时,加 MDL 写锁
5、读锁之间不互斥,可以有多个线程同时对一张表增删改查
6、读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性,解决 DML、DDL 之间的一致性问题
7、不需要显式使用,在访问一个表的时候会被自动加上
InnoDB 中的行锁
1、行锁:Row Lock,也称为记录锁,锁住某一行(某条记录)
2、MySQL 服务器层并没有实现行锁机制,行级锁只在存储引擎层实现
3、优点:锁定力度小,发生锁冲突概率低,可以实现的并发度高
4、缺点:对于锁的开销比较大,加锁会比较慢,容易出现死锁情况
5、InnoDB、MyISAM 最大不同有两点:一是支持事务;二是采用了行级锁
记录锁(Record Locks)
1、只把一条记录锁上,官方的类型名称为:LOCK_REC_NOT_GAP
2、记录锁分类:S 型记录锁、X 型记录锁
3、当一个事务获取一条记录的 S 型记录锁后,其他事务也可以继续获取该记录的 S 型记录锁,但不可以继续获取 X 型记录锁
4、当一个事务获取一条记录的 X 型记录锁后,其他事务既不可以继续获取该记录的 S 型记录锁,也不可以继续获取 X 型记录锁
间隙锁(Gap Locks)
1、MySQL 在 REPEATABLE READ 隔离级别下,可以解决幻读
2、InnoDB 提出 Gap Locks,官方类型名称为:LOCK_GAP
3、间隙锁只为防止插入幻影记录
4、虽然有共享间隙锁、独占间隙锁,但是它们起到的作用是相同的
5、如果对一条记录加间隙锁,并不会限制其他事务对这条记录加记录锁或继续加间隙锁
6、给一条记录加间隙锁,只是不允许其他事务往这条记录前边的间隙插入新记录
7、若
8、间隙锁的引入,可能会导致同样的语句锁住更大的范围,影响并发度
临键锁(Next-Key Locks)
1、既锁住某条记录,又阻止其他事务在该记录前边的间隙插入新记录
2、官方类型名称为:LOCK_ORDINARY
3、Next-Key Locks 在存储引擎 InnoDB、事务级别在可重复读的情况下,使用的数据库锁,Innodb 默认锁为 Next-Key locks
4、本质:记录锁 + 间隙锁,既能保护该条记录,又能阻止别的事务将新记录插入被保护记录前边的间隙
插入意向锁(Insert Intention Locks)
1、InnoDB 规定事务在等待时,需要在内存中生成一个锁结构,表明有事务想在某个间隙中插入新记录
2、官方类型名称为:LOCK_INSERT_INTENTION
3、插入意向锁是一种间隙锁(行锁),不是意向锁(表锁),在 INSERT 时产生
4、该锁用以表示插入意向,当多个事务在同一区间(gap),插入位置不同的多条数据时,事务之间不需要互相等待
5、特性
(1)一种特殊的间隙锁,可以锁定开区间内的部分记录
(2)插入意向锁之间互不排斥,所以即使多个事务在同一区间插入多条记录,只要记录本身(主键、唯一索引)不冲突,则事务之间就不会出现冲突等待
(3)插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁
页锁
1、在页的粒度上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录
2、当使用页锁时,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行
3、页锁的开销介于表锁和行锁之间,会出现死锁
4、锁定粒度介于表锁和行锁之间,并发度一般
5、每个层级的锁数量是有限制的,因为锁会占用内存空间,锁空间的大小是有限的,当某个层级的锁数量超过该层级的阈值时,就会进行锁升级
6、锁升级:用更大粒度的锁替代多个更小粒度的锁,比如 InnoDB 中行锁升级为表锁,好处是占用的锁空间降低,但数据的并发度也下降
从对待锁的态度划分:乐观锁、悲观锁
1、乐观锁和悲观锁并不是锁,而是锁的设计思想
2、适用场景
(1)乐观锁:适合读操作多的场景,相对来说写的操作比较少,优点在于程序实现,不存在死锁问题,但阻止不了除了程序以外的数据库操作
(2)悲观锁:适合写操作多的场景,因为写的操作具有排它性,可以在数据库层面,阻止其他事务对该数据的操作权限,防止读 - 写和写 - 写的冲突
悲观锁(Pessimistic Locking)
1、悲观锁总是假设最坏的情况,共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程
2、比如:行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁,当其他线程想要访问数据时,都需要阻塞挂起
3、SELECT …… FROM UPDATE 是 MySQL 中悲观锁
(1)SELECT …… FROM UPDATE 语句执行过程中所有扫描的行都会被锁上
(2)因此在 MySQL 中用悲观锁必须确定使用了索引,而不是全表扫描,否则将会把整个表锁住
4、悲观锁不适用的场景较多,存在一些不足,因为悲观锁大多数情况下,依靠数据库的锁机制来实现,以保证程序的并发访问性,同时这样对数据库性能开销影响也很大,特别是长事务
乐观锁(Optimistic Locking)
1、乐观锁认为对同一数据的并发操作不会总发生,属于小概率事件,不用每次都对数据上锁,但是在更新时,会判断在此期间该数据是否被更新,即不采用数据库自身的锁机制,而是通过程序来实现
2、在程序上,可以采用版本号机制或者 CAS 机制实现
3、乐观锁适用于多读的应用类型,这样可以提高吞吐量
4、乐观锁的版本号机制
(1)在表中设计一个版本字段 version,第一次读时,会获取 version 字段的取值
(2)然后对数据进行更新或删除时,会执行 UPDATE …… SET version=version+1 WHERE version=version
(3)此时如果已经有事务对这条数据进行了更改,修改就不会成功
(4)类似 SVN、CVS 版本管理系统,当修改代码进行提交时,首先会检查当前版本号与服务器上的版本号是否一致,如果一致就可以直接提交,如果不一致就需要更新服务器上的最新代码,然后再进行提交
5、乐观锁的时间戳机制
(1)时间戳和版本号机制一样,在更新提交时,将当前数据的时间戳和更新之前取得的时间戳进行比较
(2)如果两者一致则更新成功,否则就是版本冲突
按加锁的方式划分:显式锁、隐式锁
隐式锁
1、一个事务在执行 INSERT 操作时
(1)如果即将插入的间隙已经被其他事务加间隙锁,则本次 INSERT 会阻塞,并且当前事务会在该间隙上加一个插入意向锁,否则一般情况下 INSERT 操作是不加锁的
(2)如果一个事务首先插入了一条记录,此时并没有在内存生产与该记录关联的锁结构,然后另一个事务立即使用 SELECT …… LOCK IN SHARE MODE 语句读取这条记录,即获取这条记录的 S 锁,或者使用 SELECT …… FOR UPDATE 语句读取这条记录,即获取这条记录的 X 锁,可能产生脏读;立即修改这条记录,即获取这条记录的 X锁,可能产生脏写
(3)事务 id 解决(2)
2、情景一:聚簇索引记录
(1)有一个 trx_id 隐藏列,该隐藏列记录着最后改动该记录的事务 id
(2)如果在当前事务中新插入一条聚簇索引记录后,该记录的 trx_id 隐藏列代表的的就是当前事务的事务 id,如果其他事务此时想对该记录添加 S 锁或 X 锁时,先查看该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务
(3)如果是,则帮助当前事务创建一个 X 锁,即为当前事务创建一个锁结构,is_waiting 属性是 false,然后自己进入等待状态,即为自己也创建一个锁结构,is_waiting 属性是 true
3、情景二:二级索引记录
(1)本身并没有trx_id隐藏列,但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性,该属性代表对该页面做改动的最大的事务 id
(2)如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务 id,说明对该页面做修改的事务都已经提交,否则就需要在页面中定位到对应的二级索引记录,然后回表找到它对应的聚簇索引记录,然后再重复情景一的做法
4、一个事务对新插入的记录可以不显示的加锁(生成一个锁结构),但是由于事务 id 的存在,相当于加了一个隐式锁
5、其他事务在对这条记录加 S 锁或 X 锁时,由于隐式锁的存在,会先帮助当前事务生成一个锁结构,然后自己再生成一个锁结构后进入等待状态
6、隐式锁是一种延迟加锁的机制,从而来减少加锁的数量
7、隐式锁在实际内存对象中并不含有这个锁信息,只有当产生锁等待时,隐式锁转化为显式锁
8、隐式锁的逻辑过程
(1)InnoDB 的每条记录中都有一个隐含 trx_id 字段,这个字段存在于聚簇索引的 B+Tree 中
(2)在操作一条记录前,首先根据记录中的 trx_id,检查该事务是否是活动的事务(未提交或回滚),如果是活动的事务,首先将隐式锁转换为显式锁,即为该事务添加一个锁
(3)检查是否有锁冲突,如果有冲突,创建锁,并设置为 waiting 状态,如果没有冲突不加锁,跳到(5)
(4)等待加锁成功,被唤醒,或者超时
(5)写数据,并将自己的 trx_id 写入 trx_id 字段
显式锁
1、通过特定的语句进行加锁,称为显式加锁
2、显示加共享锁
SELECT …… LOCK IN SHARE MODE;
3、显示加排它锁
SELECT …… FOR UPDATE;
全局锁
1、对整个数据库实例加锁,让整个库处于只读状态
2、其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)、更新类事务的提交语句
3、使用场景:做全库逻辑备份
4、全局锁的命令
Flush tables with read lock;
死锁
1、两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环
2、产生死锁的必要条件
(1)两个或者两个以上事务
(2)每个事务都已经持有锁,并且申请新的锁
(3)锁资源同时只能被同一个事务持有或者不兼容
(4)事务之间因为持有锁和申请锁导致彼此循环等待
3、关键:两个(或以上)Session 加锁的顺序不一致
4、处理死锁方式一
(1)等待,直到超时
(2)innodb_lock_wait_timeout=50s
(3)即当两个事务互相等待时,当一个事务等待时间超过设置的阈值时,就将其回滚,另外事务继续进行
(4)在 InnoDB 中,innodb_lock_wait_timeout 用来设置超时时间
(5)缺点:对于在线服务来说,这个等待时间往往是无法接受的
(7)若此值修改过短,容易误伤普通的锁等待
5、处理死锁方式二
(1)使用死锁检测进行死锁处理
(2)InnoDB 提供 wait-for graph 算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph 算法都会被触发
(3)主动的死锁检测机制,要求数据库保存:锁的信息链表、事务等待链表
(4)死锁检测的原理:构建一个以事务为顶点、锁为边的有向图,判断有向图是否存在环,存在即有死锁
(5)一旦检测到回路、有死锁,这时 InnoDB 会选择回滚 undo 量最小的事务,让其他事务继续执行,innodb_deadlock_detect=on 表示开启这个逻辑
(6)缺点:每个新的被阻塞的线程,都要判断是不是由于自己的加入导致了死锁,这个操作时间复杂度是 O(n),如果 100 个并发线程同时更新同一行,要检测 100 * 100=10000
6、解决主动的死锁检测机制的问题
(1)方式一:关闭死锁检测,若能确保业务一定不会出现死锁,可以临时把死锁检测关掉,意味着可能会出现大量的超时,这是业务有损
(2)方式二:控制并发度,那么死锁检测的成本很低,就不会出现这个问题,对于相同行的更新,在进入引擎之前排队,在 InnoDB 内部就不会有大量的死锁检测工作;或通过将一行改成逻辑上的多行来减少锁冲突
锁的内存结构
1、加锁本质:在内存中创建一个锁结构与之关联
2、对不同记录加锁时,如果符合以下条件的记录会放到一个锁结构中
(1)在同一个事务中进行加锁操作
(2)被加锁的记录在同一个页面中
(3)加锁的类型相同
(4)等待状态相同
3、InnoDB 中的锁结构
4、锁所在的事务信息
(1)不论是表锁还是行锁,都是在事务执行过程中生成的,哪个事务生成这个锁结构,这里就记录这个事务的信息
(2)此锁所在的事务信息,在内存结构中只是一个指针,通过指针可以找到内存中,关于该事务的更多信息,如事务 id 等
5、索引信息:对于行锁来说,需要记录加锁的记录是属于哪个索引的,也是一个指针
6、表锁信息:记载对哪个表加的锁,还有其他信息
7、行锁
(1)Space ID:记录所在表空间
(2)Page Number:记录所在页号
(3)n_bits:对于行锁,一条记录就对应着一个比特位,一个页面中包含很多记录,用不同的比特位来区分哪一条记录加锁,在行锁结构的末尾放置一些比特位,n_bits 代表使用多少比特位,n_bits 值一般比页面中记录条数多,为了之后在页面中插入新记录后,不至于重新分配锁结构
8、type_mode
(1)一个 32 位的数,被分成 lock_mode、lock_type、rec_lock_type 三个部分
9、锁的模式(lock_mode),占用低 4 位,可选的值如下
(1)LOCK_IS(十进制 0):表示共享意向锁,即 IS 锁
(2)LOCK_IX(十进制 1):表示独占意向锁,即 IX 锁
(3)LOCK_S(十进制 2):表示共享锁,即 S 锁
(4)LOCK_X(十进制 3):表示独占锁,即 X 锁
(5)LOCK_AUTO_INC(十进制 4):表示 AUTO-INC 锁
(6)在 InnoDB 中,LOCK_IS、LOCK_IX、LOCK_AUTO_INC 都是表级锁的模式,LOCK_S、LOCK_X 既可以算是表级锁的模式,也可以是行级锁的模式
10、锁的类型(lock_type),占用第 5~8 位,现阶段只使用第 5 位,第 6 位
(1)LOCK_TABLE(十进制 16),即当第 5 个比特位置为 1 时,表示表级锁
(2)LOCK_REC(十进制 32),即当第 6 个比特位置为 1 时,表示行级锁
11、行锁的具体类型(rec_lock_type),使用其余的位来表示,只有在 lock_type 值为 LOCK_REC 时,即该锁为行级锁时,才会被细分为更多的类型
(1)LOCK_ORDINARY(十进制 0):表示 next-key 锁
(2)LOCK_GAP(十进制 512):即当第 10 个比特位置为 1 时,表示间隙锁
(3)LOCK_REC_NOT_GAP(十进制 1024):即当第 11 个比特位置为 1 时,表示记录锁
(4)LOCK_INSERT_INTENTION(十进制 2048):即当第 12 个比特位置为 1 时,表示插入意向锁
12、is_waiting
(1)基于内存空间的节省,把 is_waiting 放到 type_mode 中
(2)LOCK_WAIT(十进制 256):当第 9 个比特位置为 1 时,表示 is_waiting 为 true,当前事务尚未获取到锁,处在等待状态;当这个比特位为 0 时,表示 is_waiting 为 false,当前事务获取锁成功
13、其他信息:为了更好的管理系统运行过程中生成的各种锁结构,而设计各种哈希表和链表
14、一堆比特位
(1)如果是行锁结构的话,在该结构末尾还放置了一堆比特位,比特位的数量是由 n_bits 表示
(2)InnoDB 数据页中的每条记录,在记录头信息中都包含一个 heap_no 属性,伪记录 Infimum 的 heap_no 值为 0,Supremum 的 heap_no 值为 1,之后每插入一条记录,heap_no 值就增加 1
(3)锁结构最后一堆比特位,对应着一个页面中的记录,一个比特位映射一个 heap_no,即一个比特位映射到页内的一条记录
MySQL 锁的监控
1、通过检查 InnoDB_row_lock 等状态变量来分析系统上的行锁的争夺情况
(1)Innodb_row_lock_current_waits:当前正在等待锁定的数量
(2)Innodb_row_lock_time:从系统启动,到现在锁定总时间长度(等待总时长)
(3)Innodb_row_lock_time_avg:每次等待所花平均时间(等待平均时长)
(4)Innodb_row_lock_time_max:从系统启动,到现在等待最长的一次所花的时间;
(5)Innodb_row_lock_waits:系统启动后,到现在总共等待的次数(等待总次数)
2、MySQL 把事务和锁的信息记录在 information_schema 库中
(1)涉及三张表:INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS
(2)MySQL 5.7 及之前,通过 information_schema.INNODB_LOCKS 查看事务的锁情况,但只能看到阻塞事务的锁;如果事务并未被阻塞,则在该表中看不到该事务的锁情况
(3)MySQL8.0 删除 information_schema.INNODB_LOCKS,添加 performance_schema.data_locks,可以通过 performance_schema.data_locks 查看事务的锁情况,和 MySQL 5.7 及之前不同,performance_schema.data_locks 不但可以看到阻塞该事务的锁,还可以看到该事务所持有的锁,同时,information_schema.INNODB_LOCK_WAITS 被 performance_schema.data_lock_waits 代替
间隙锁加锁规则
1、间隙锁是在可重复读隔离级别下才会生效的
(1)next-key lock:间隙锁 + 行锁,如果切换到读提交隔离级别,去掉间隙锁的部分,只剩下行锁的部分
(2)为了解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row,即读提交隔离级别 + binlog_format=row
(3)业务不需要可重复读的保证,这样考虑到读提交下操作数据的锁范围更小(没有间隙锁)
2、加锁规则
(1)原则 1:加锁的基本单位是 next-key lock,next-key lock 是前开后闭区间
(2)原则 2:查找过程中访问到的对象才会加锁,任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上,在主键上也要加一把锁
(3)优化 1:索引上的等值查询,给唯一索引加锁时,next-key lock 退化为行锁,即如果 InnoDB 扫描一个主键,或一个唯一索引,则 InnoDB 只会采用行锁方式来加锁
(4)优化 2 :索引上(不一定为唯一索引)的等值查询,向右遍历时,且最后一个值不满足等值条件时,next-keylock 退化为间隙锁
(5)一个 bug :唯一索引上的范围查询,会访问到不满足条件的第一个值为止
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战