mysql之mysql各种锁(三）

锁分类

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks

https://juejin.cn/post/6931752749545553933

https://www.cnblogs.com/keme/p/11065025.html

https://cloud.tencent.com/developer/news/625903

一、粒度锁

1.1 全局锁

1、概念

对整个数据库实例加锁

2、应用场景

全库逻辑备份 mysqldump

3、实现方式

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

4、风险点：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。

如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

5、解决办法：

mysqldump使用参数--single-transaction，启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

1.2表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

1.2.1 表锁

1、概念

当前操作的整张表加锁，最常使用的 MyISAM 与 InnoDB 都支持表级锁定。

2、实现方式

表锁：lock tables … read/write；

释放锁 unlock tables，也可以在客户端端开的时候自动释放

例如lock tables t1 read, t2 write; 命令，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能在unlock tables之前访问其他表。

1.2.2 MDL锁

1、原理

MDL(Metadata Lock) 不需要显式使用，在访问一个表的时候会被自动加上。

2、作用

MDL 的作用是，保证读写的正确性。

你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。 3、风险点 高峰期改变数据表结构可能锁住线上查询和更新，甚至造成库挂了 4、解决办法 online dll 中间件？ （1）创建临时表  在DB上创建ghost临时表，表结构与目标表相同，ghost临时表以下简称为新表。  新表上直接应用用户提交的Alter语句，完成表结构更新。  分批次迁移原表全量数据到新表。  同时拉取解析binlog事件，将任务期间的新增数据应用到新表。   数据完全同步后，通过Rename语句使新表替换老表。

（2）解决长事务+低峰期

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。这也是为什么需要在低峰期做ddl 变更。

1.3 页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。

1.4 行级锁

1、概述

行级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。

MySQL中只有InnoDB支持行级锁，行级锁分为共享锁和排他锁。

2、实现原理

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

二、共享锁和排他锁 Shared and Exclusive Locks

InnoDB 实现了两种类型的锁机制：共享锁（S）和排他锁（X）。共享锁允许一个事务读数据，不允许修改数据，如果其他事务要再对该行加锁，只能加共享锁；排他锁是修改数据时加的锁，可以读取和修改数据，一旦一个事务对该行数据加锁，其他事务将不能再对该数据加任务锁。

注：一个事务在某一行数据上加上排它锁后，其他事务不能再在这行数据加任何锁，无法进行 update 与 delete，但是普通的 select 是可以的，因为普通的 select 不加任何锁，当 select .... from ... for update 时才会加上排它锁。

2.1 共享锁（读锁） shared Locks(S锁)

1、概念

共享锁，又称之为读锁，简称S锁，当事务A对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当事务A上的读锁被释放后，其他事务才能对其添加写锁。

2、 应用场景

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。

适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id)values (100)就不会存在这种问题了。

3、 实现方式

select …lock in share mode

4、结论

mysql 共享锁 (lock in share mode)

允许其它事务也增加共享锁读取

不允许其它事物增加排他锁 (for update)

当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁、

2.2 排他锁（写锁）Exclusive Locks(X锁)

1、概念

排它锁，又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

2、 应用场景

写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

3、实现方式

select …for update

4、结论

事务之间不允许其它排他锁或共享锁读取，修改更不可能

一次只能有一个排他锁执行 commit 之后，其它事务才可执行

三、意向锁 (表级的)

https://juejin.cn/post/6844903666332368909

InnoDB 支持多粒度锁定，允许行锁和表锁共存。它是由数据库引擎自行维护的，用户自己无需也无法操作意向锁

如果用户想要在表上面添加一个共享锁或者排他锁的时候，需要做如下两个检查：

•检查这张表的排他锁有没有被其他事务占用，如果有，那么加锁失败；

•检查这张表中的行锁有没有被其他事务占用，如果有，那么加锁失败。

针对第二个检查，如果一张表的数据量特别大，然后我们又想在这张表上添加一个表锁，如果一行一行地去遍历这张表的数据有没有被锁住，效率比较低下。意向锁的存在正是为了解决这个问题。

意向锁能够将检查行锁的时间复杂度由 O(n) 变成 O(1)，其加锁的具体做法就是，当一个事务想要获取表中某一行的（共享/排他）锁的时候，它会自动尝试给当前表的加上意向（共享/排他）锁

。然后，表锁和行锁之间的兼容互斥性就变成了表锁和意向锁之间的竞争关系，这就是上面示例中事务 B “发现” 行锁的方式。

例如，诸如 LOCK TABLES ... WRITE 之类的语句在指定的表上获取排他锁（X 锁）。 为了使多粒度级别的锁定变得可行，InnoDB 使用意向锁。 意向锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁（共享或独占）。 意向锁有两种类型：

1、 意向共享锁(IS)

意向共享锁(IS) 表示事务打算在表中的各个行上设置共享锁。

2 、意向排他锁

意向排他锁 (IX) 表示事务打算在表中的各个行上设置排他锁。

意向锁定协议如下：

在事务可以获取表中行的共享锁之前，它必须首先获取表上的 IS 锁或更强锁。

在事务可以获取表中一行的排他锁之前，它必须首先获取表上的 IX 锁。

四、记录锁、间隙锁、临键锁

https://www.jianshu.com/p/478bc84a7721

4.1 记录锁 Record Locks

记录锁是索引记录上的锁。

例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE； 防止任何其他事务插入、更新或删除 t.c1 值为 10 的行。

记录锁总是锁定索引记录，即使表没有定义索引。 对于这种情况，InnoDB 创建一个隐藏的聚簇索引并使用该索引进行记录锁定。

需要注意的是：

id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。

同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

其他实现

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

4.2 间隙锁 Gap Locks

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。也就是说间隙锁在可重复读隔离级别下才有效。

注意 RC的情况下间隙锁会失效

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

InnoDB 中的间隙锁是“纯粹抑制性的”，这意味着它们的唯一目的是防止其他事务插入间隙。 间隙锁可以共存。 一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。 共享和排他间隙锁之间没有区别。 它们彼此不冲突，并且它们执行相同的功能。

4.3 临键锁 Next-Key Locks

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

4.4 加锁规则

原则 1：加锁的基本单位是 next-key lock。 next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

五、乐观锁和悲观锁

5.1 乐观锁

1、概念

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

2、 应用场景

适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，会大大降低系统性能。

3、实现方式

实现方式

一般使用数据版本（Version）记录机制实现，在数据库表中增加一个数字类型的“version”字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

4、 实战

订单order表中id,status,version分别代表订单ID，订单状态，版本号。

1.查询订单信息

select id,status,versionfrom order where id=#{id};

2.用户支付成功

3.修改订单状态

update set status=支付成功,version=version+1 where id=#{id} and version=#{ version};

5.2 悲观锁

1、概念

悲观锁，正如其名，具有强烈的独占和排他特性，每次去拿数据的时候都认为别人会修改，对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。

2、应用场景

适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。

3、 实现方式

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

4、实战

商品goods表中id,name,number分别代表商品ID，商品名称，商品库存。

a.开启事务并关闭自动提交

setautocommit=0;

b.查询商品信息

selectid,name,number from goods where id=1 for update;

c.用户下单，生成订单

d.修改商品库存

updateset number= number-1 where id=1;

e.提交事务

commit;

说明：select...for update是MySQL提供的实现悲观锁的方式，属于排它锁，在goods表中，id为1的那条数据就被当前事务锁定了，其它的要执行select id,name,number from goods where id=1for update;的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

注意：此时MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

两阶段锁协议

死锁和死锁检测