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、同一张表或记录的锁的兼容性

image-20220608161414638

 

锁定读

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)悲观锁:适合写操作多的场景,因为写的操作具有排它性,可以在数据库层面,阻止其他事务对该数据的操作权限,防止读 - 写和写 - 写的冲突

image-20220609121134950

 

悲观锁(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 :唯一索引上的范围查询,会访问到不满足条件的第一个值为止

posted @   半条咸鱼  阅读(240)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示