mysql锁机制

mysql支持的锁有哪些？有哪些使用场景

• 从锁的粒度上分Mysql支持的锁表级锁、行级锁（InnoDB）、页级锁
• 从锁的操作上说可以分读锁和写锁
• 从实现方式上分乐观锁和悲观锁
• 使用场景
  • 修改数据库表结构会自动加表级锁——元数据锁
  • 更新数据未使用索引，行级锁会上升为表级锁
  • 更新数据使用索引，行级锁会使用行级锁

锁分类

• 乐观锁
  • 程序实现
    • 版本号
    • 时间戳
• 悲观锁
  • 表级锁
    • 表锁（Mysql layer）——手动添加
      • read lock（加读锁后可以加读锁，不能加写锁）
      • write lock（加写锁后不能加读锁，也不能加写锁）
    • 元数据锁（Mysql Layer）——自动添加
      • CURD加读锁
      • DDL加写锁
    • 意向锁（InnoDB）——内部使用
      • 共享读锁（IS）
      • 排他写锁（IX）
  • 行级锁（InnoDB）
    • 共享读锁（S）——手动添加
      • select …… lock in share mode
    • 排他写锁（X）——自动添加
      • DML（insert、update、delete）
      • select …… for update

表级锁

• MySQL Server层表现

• 每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低

• 应用在MyISAM、InnoDB、BDB等存储引擎中。

• MySQL的表级锁有两种：

  • 一种是表锁
  • 一种是元数据锁（meta data lock ， MDL）。

• 表锁有两种表现形式

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

• 表读锁

  • session1: lock table mylock read; -- 给mylock表加读锁
    1

  • session1: select * from mylock; -- 可以查询
    1

  • session1: select * from tdep; -- 不可以访问非锁定表
    1

  • session2: select * from mylock; -- 可以查询 没有锁
    1

  • session2: update mylock set name='x' where id = 2; -- 修改阻塞，自动加行写锁
    1

  • session1: unlock tables; -- 释放表锁
    1

  • session2: Rows matched:1 Changed:1 Warning: 0 -- 修改执行完成
    1

  • session1: select * from tdep; -- 可以访问
    1

• 表写锁

  • session1: lock table mylock write; -- 给mylock表加写锁
    1

  • session1: select * from mylock; -- 可以查询
    1

  • session1: select * from tdep; -- 不能访问非锁定的表
    1

  • session1: update mylock set name = 'y' where id = 2; -- 可以执行
    1

  • session2: select * from mylock; -- 查询阻塞
    1

  • session1: unlock tables; -- 释放表锁
    1

  • session2: 4 row in set (22.57 sec) -- 查询执行完成
    1

  • session1: select * from tdep; -- 可以访问
    1

元数据锁

• MDL（metaDataLock）元数据：表结构

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

• 元数据读锁

  • session1: begin; -- 开启事务
    1

  • session2: alter table mylock add f int; -- 修改阻塞
    1

  • session1: commit; -- 提交事务 或者 rollback 释放读锁
    1

  • query ok, 0 row affected (38.67 sec) -- 修改完成
    Records: 0 Duplicates: 0 Warnings: 0
    1
    2

• 元数据写锁

  • 在高并发的情况下，在修改表结构的时候，进行增删改查

行级锁

• InnoDB实现
• 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。
• 应用在存储引擎中。
• RecordLock锁：记录锁，锁定单个行记录的锁。RC、RR隔离级别都支持。
• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。RR隔离级别支持。
• Next-key Lock 锁：行锁和间隙锁组合，同时锁住数据，并且锁住数据前面的Gap。RR隔离级别支持。

行锁是什么？有哪些分类，原理是什么？

行读锁

• 查看行锁状态

  • show status like 'innodb_row_lock%';
    1

  • session1: begin; -- 开启事务未提交
    
    		  select * from mylock where ID = 1 lock in share mode; -- 手动加id=1的行读锁，使用索引
    1
    2
    3

  • session2: update mylock set name = 'y' where id = 2; -- 未锁定该行可以修改
    1

  • session2: update mylock set name = 'y' where id = 1; -- 锁定该行修改阻塞
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时
    1
    2

  • session1: commit; -- 提交事务 或者 rollback 释放行读锁
    1

  • session2: udpate mylock set name = 'y' where id = 1; -- 修改成功
    		Query OK, 1 row affected (0.00 sec)
    		Row matched: 1 Changed: 1 Warnings: 0
    1
    2
    3

  • 注： 使用索引加行锁 ， 未锁定的行可以访问

行读锁升级为表锁

• session1: begin; -- 开启事务未提交
  			-- 手动加name='c'的行读锁， 未使用索引
  		  select * from mylock where name = 'c' lock in share mode;
  1
  2
  3

• session2: update mylock set name = 'y' where id = 2; -- 修改阻塞 未用索引行锁升级为表锁
  1

• session1: commit; -- 提交事务 或者 rollback 释放读锁
  1

• update mylock set name = 'y' where id = 2; -- 修改成功
  
  Query OK, 1 row affected
  Rows matched: 1 Changed: 1 Warnings: 0
  1
  2
  3
  4

• 注：未使用索引行锁升级为表锁

行写锁

• session1: begin;	-- 开启事务未提交
  		  -- 手动加id=1的行写锁，
  		  select * from mylock where id = 1 for update;
  1
  2
  3

• session2: select * from mylock where id = 2; -- 可以访问
  1

• session2: select * from mylock where id = 1; -- 可以读， 不加锁
  1

• session2: select * from mylock where id = 1 lock in share mode; -- 加读锁被阻塞
  1

• session1: commit; -- 提交事务 或者 rollback 释放写锁
  1

• 执行成功
  1

• 主键索引产生记录锁

行级锁分类

按照功能分类，分为两种：

• 共享锁（S）：允许事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他写锁（X）：允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁（不是读）和排他写锁

行锁原理

主键加锁

• 加锁行为：仅在 id=10 的主键索引记录上加 X 锁

唯一键加锁

• 加锁行为：先在唯一索引 id 上加 id = 10 的 X 的锁，再在 id=10 的主键索引记录上加 X 锁

非唯一键加锁

• 加锁行为：对满足 id=10 条件的记录和主键分别加 X 锁，然后在（6，c)_{（10，b）、（10，b）}（10，d）、（10，d）~（11，f）间隙分别加Gap锁

无索引加锁

• 加锁行为：表里所有行和间隙均加X锁。由于InnoDB引擎锁机制是基于索引实现记录锁定的，因此没有索引时会导致全表锁定。
• 加锁过程：如果没有索引会把所有记录都加锁，并把所有缝隙都锁上，所以全表都锁住了。

Navicat关闭事务自动提交

• 先看一个事务是否是自动提交的

  show variables like 'autocommit'
  1

什么是读写锁？什么是排他锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁使用独占的方式来保证在只有一个版本的情况下事务之间相互隔离，所以锁可以理解为单版本控制。

引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或者叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

读写锁

• 读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁
• 如果几个事务之间没有共享数据项，完全可以并行被处理
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KltF8oNV-1660601993563)(C:\Users\14576\AppData\Roaming\Typora\typora-user-images\image-20220806151815230.png)]

排他锁

• 引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ubjAELQQ-1660601993566)(C:\Users\14576\AppData\Roaming\Typora\typora-user-images\image-20220806151554835.png)]
• 注意，在整个事务1结束之前，锁是不会被释放的，所以，事务2必须等到事务1结束之后开始

死锁是如何产生的？

• 加锁是实现数据库并发控制的一个非常重要的技术。当两个事务的锁发生冲突，互相等待对方的锁释放，不能继续执行事务逻辑，就会出现死锁，严重影响应用的正常执行。
• 死锁的现象主要有：表锁死锁、行级锁死锁、共享锁转换为排他锁。

表锁死锁

• 产生原因：用户A由于用户B已经锁住表B，它必须等待用户释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁产生了。
• 解决方案
  • 调整程序的逻辑
  • 尽量避免同时锁定两个资源

行级锁死锁

• 在事务中执行了一条不满足条件的for update操作，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。
• 解决方案
  • SQL语句中不要使用太复杂的关联多表的查询，优化索引
• 产生原因2
  • 两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。
  • 解决方案
    • 在同一个事务中，尽可能做到一次锁定所需要的所有资源
    • 按照id对资源排序，然后按顺序进行处理
    • 采用MVCC机制处理

共享锁转换为排他锁

• 产生原因
  • 事务A 查询一条记录，然后更新该条记录；此时事务 B 也更新该条记录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A再执行更新操作时，此处发生死锁，因为事务A需要排他锁做更新操作。但是无法授权该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。
• 解决方案
  • 避免引发同时对同一条记录多次操作；
  • 使用乐观锁进行控制

select …… for update是表锁还是行锁

答

• 如果查询时用到了索引就是行锁
• 如果查询时没有用到索引就是表锁

举例

建表语句

CREATE TABLE `test` (
  `task_value` text,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `text_no` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_text_no` (`text_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7