MySQL学习（五）：锁

概述

锁的分类
- 操作类型
  - 读锁（共享锁S）
    - 针对同一操作，多个读操作可以同时进行不会互相影响
  - 写锁（排他锁X）
    - 当前锁没有执行完成之前，会阻断其他写锁和读锁
- 数据操作粒度
  - 行锁
  - 表锁
- 其他锁
  - 乐观锁
    - 概念
      - 很乐观，认为操作不会带来并发问题，在修改之前再去判断是否会有并发问题
    - 实现方式
      - （1）CAS
        
        先比较后更新，对数据更新前先读取数据，和更新前的数据（备份数据）比较，如果改变了则不更新，并抛出异常
      - （2）增加版本字段
        
        新增一个版本字段，每当进行更新操作就将版本字段+1，更新前比较当前的版本字段与初始化读取时候的版本字段比较，如果相同就代表没有修改则更新字段，不相同就不修改。相较于CAS解决了ABA的问题，也就是说在CAS中，如果有其他线程对数据进行了+1、-1操作，虽然看起来数据没有改变，但是确实对数据进行了修改，如果CAS则无法发现数据已修改过
  - 悲观锁
    - 概念
      - 认为每次操作都会带来异常场景，因此每次操作前都需要获得相应的锁才能够操作
- 不同存储引擎的锁
  - MyISAM（表锁）
    - 读锁
      - MyISAM的读默认是加读锁
    - 写锁
      - MyISAM的DML默认加写锁
  - InnoDB（行锁）
    - 读锁
      - 事务T对数据对象A加上S锁，那么其他事务也只能对A加S锁，无法加X锁，但是普通的select xxxx from xxx 查找不会受锁机制的影响，因为其不涉及锁机制。这样能保证事务T再为释放S锁之前其他事务无法修改数据A。InnoDB通过lock in share mode加读锁，但是注意只锁覆盖索引
    - 写锁
      - 允许获取排他锁的事务读写数据，阻止其他事务获得数据的共享锁和排他锁。InnoDB默认对DML操作默认加写锁，select 可以通过for update加X锁，并且会锁住所有索引，不仅仅是覆盖索引
三锁

表锁（偏读）
- 特点
  - 一次性对一张表整体加锁，偏向MyISAM引擎，开销小，加锁快；无死锁，锁定粒度大，发生锁冲突概率最高，并发度最低。在MyISAM中,因为不支持事务,因此只能用lock和unlock控制.InnoDB中，表锁还可以通过事务控制
- MyISAM的使用
  - 1.操作
    - 加锁：locak table 表1 read/write ,表2 read/write ,...
    - 释放锁(所有)：unlock tables ;
    - 查看哪些表加了锁
      - show open tables ; 1代表被加了锁
    - 分析锁的严重程度
```
  Table_locks_immediate :即可能获取到的锁数,即立刻能加锁的表数

  Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的 锁竞争)

  一般建议：

  Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎
```
  - 2.锁的调度问题
    - MyISAM存储引擎的读锁和写锁是互斥的，就是串行的，但是当一个请求请求表的写锁，另一个请求请求表的读锁，此时引擎会优先写锁，哪怕在锁等待队列中读锁先于写锁等待，此时也会优先写锁，因为MyISAM认为写更重要，所以MyISAM不适用于大量写的场景，因为此时会导致读等待过长或读不到
    - 当然这是可以设置的：
      - 通过指定启动参数low-priority-updates(默认是off/0)，使MyISAM引擎默认给予读请求以优先的权利。
      - 通过执行命令SET LOW_PRIORITY_UPDATES=1/on，使该连接发出的更新请求优先级降低。
      - 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
    - 高并发问题
      - MyISAM也允许通过参数的设置来调节写的时候数据的插入
        
        当concurrent_insert设置为NEVER(0)时，不允许并发插入。当concurrent_insert设置为AUTO(1)时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。当concurrent_insert设置为ALWAYS(2)时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
行锁（偏写）
- 特点
  - 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁定粒度小，发生锁冲突概率低，并发度最高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）
  - 索引失效或者压根就没有索引时，会怎么办呢？我看过好多文章，都是说会变为表锁，但实际并不是这样，其实还是行锁
- InnoDB的行锁
  
  对于InnoDB表，本文主要讨论了以下几项内容：
  （1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
  （2）介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。
  在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同
  - 1.与MyISAM最大的不同
    - （1）支持事务，并且事务默认自动提交
    - （2）采用了行级锁
  - 查看锁的竞争情况
    - show status like 'innodb_row_lock%';
      
      如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因
  - 2事务概念
  - 3.事务并发带来的问题
    - （1）脏读
    - （2）不可重复读
    - （3）幻读
    - （4）更新丢失
  - 4.四种隔离级别的加锁情况
    - （1）读未提交：而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销
    - （2）读已提交：每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的，所以说RC级别下的快照是每个select都一份，而RR情景下是事务中第一个select开始就会读取快照，持续在整个事务期间内，
    - （3）可重复读：通过快照（MVCC + undolog）的形式，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读已提交则是每次执行语句的时候都重新生成一次快照
    - （4）串行化：将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束
  - 5.无索引问题
    
    加锁的过程要分有索引和无索引两种情况，比如下面这条语句
```
update user set age=11 where id = 1
id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

而下面这条语句

update user set age=11 where age=10
表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。
```
  - 6.意向共享锁和意向排它锁
    
    为了允许行锁和表锁共存，实现多粒度锁机制,InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁
    - 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
    - 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
  - 7.InnoDB的锁兼容性列表
    - 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
      
      意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁
- 8.什么时候使用表锁
  - （1）事务需要更新表的大部分数据、表又比较大，这个时候使用行锁会比较耗费性能并导致事务执行时间较长
  - （2）事务涉及的表比较多，容易导致死锁且造成大量事务回滚
  - 使用注意
    - （1）Lock table虽然可以给InnoDB加表级锁，但是表锁不是属于存储引擎进行管理，而是由上一层MySQL server进行管理。仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁
    - （2）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句
```
SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;
```
- 9.关于死锁
  - 不同存储引擎下的死锁
    - MyISAM总是一次性获得所有的锁，因此不会发生死锁的情况
    - InnoDB一般会检测到死锁，并使一个线程释放锁并回滚，在外部锁等场景下InnoDB也不一定能够完全检测到，这个时间可以通过设置锁等待超时参数innodb_lock_wait_timeout来避免锁等待的时间过长而导致的不必要的开销
  - 死锁的常用方法
    
    如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。
    - （1）程序中不同的会话访问表的顺序最好相同，不然很容易发送死锁，造成T1、T2互相等待的场景
    - （2）程序批量处理数据时，如果事先对数据排序，保证每个线程按照固定的顺序来处理数据会大大避免死锁
    - （3）涉及更新的事务中不应该申请了共享锁又去更新数据，会造成死锁
    - （4）在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题
  - 如何排查死锁：
    
    SELECT * FROM information_schema.innodb_trx：查找当前运行中的事务，根据状态定位到当前阻塞的锁id
- 间隙锁
  - 概念
    - 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP LOCK），间隙锁和行锁合称Next-Key Lock
  - next-key lock概念：
    - next-key lock是innodb的锁算法之一，其等于间隙锁+行锁，当mysql针对记录加锁的基本单位就是next-key lock，并且锁会根据下面的优化条件等退化为行锁或间隙锁，或仍然为next-key lock
- 危害
  - 由于会锁定这个范围内的所有键值，即便这个键值不存在，因此再锁定时间内，这些键值可能无法进行写操作，会导致性能上的危害
- 生效原则
  - （1）加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间，前开后闭就是(]
  - （2）查找过程中能够访问到的对象才会加锁
- 锁的优化
  - （1）索引上的等值查询，给唯一索引加锁时，next -key lock退化为行锁
  - （2）索引上面的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
- 如何锁定一行
  - select xxx for update：锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit，也会锁间隙
页锁
- 锁定粒度界于表锁和行锁之间；开销和加锁时间界于表锁和行锁之间；会出现死锁；并发度一般。使用页级锁定的主要是BerkeleyDB存储引擎
适应场景
- 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
思维导图

参考链接：

https://blog.csdn.net/qq_44766883/article/details/105879308

# MySQL 数据库

MySQL学习（五）：锁

概述

锁的分类

三锁

表锁（偏读）

行锁（偏写）

页锁

适应场景

思维导图

参考链接：

评论

相关推荐

相关文章

Your browser is out-of-date!