Mysql 锁相关

Mysql 锁

隔离级别和锁

在读未提交，读取数据不用加共享锁，

读已提交，读操作需要加共享锁，不过在语句执行完释放

可重复读，读操作加共享锁，事务提交之前不释放，必须等待事务执行完毕之后释放

序列化，锁定整个范围的键，并一直持有锁，直到事务结束

锁

行级锁，是粒度最小的锁，只对当前操作行加锁，会出现死锁，发生锁冲突概率低

表级锁，表示对当前整个表加锁，发生锁冲突概率大，不会出现死锁，并发度最低

innodb实现锁的算法

1. record lock ，单个行记录上的锁
2. gap lock 间隙锁，锁定一个范围，不包括记录本身
3. next-key lock 行锁+间隙锁

next-key /gap 锁都是可以解决幻读问题，不会允许多个事务插入数据到一个范围

死锁是两个或多个事务，在同一个时刻，互相请求都另外一个事务占用的资源

乐观锁，悲观锁怎么实现

悲观锁，在查询的时候就把事务锁起来，直到提交了事务。使用了mysql的锁机制

乐观锁，假设不会有并发问题，在提交时检查数据的完整性。一般使用版本号和cas算法实现

锁的分类

1. 全局锁，加了全局锁，意味着整个数据库都是只读状态了，主要用于全局逻辑备份 当前连接断开锁也会释放

   1. flush tables with read lock
      

      ​ 这个时候其它线程对表的操作，数据的增删改都会被阻塞住、更新类事务的提交语句 DML(数据更新语句，增删改) DDL(表结构的修改语句) 都会被阻塞

   2. 但是让整个库都只读，很危险

      1. 当主库做备份时，主库所有更新，业务就得停下
      2. 当从库做备份时，从库只读，就不会执行主库过来的binlog，会导致主从延迟
      3. 如果不加锁，备份也会有问题

   3. 为什么要加全局锁 flush tables with read lock 而不是执行set global readonly=true的方式呢

      1. 有的系统，使用readOnly这个变量来判断是主库还是从库
      2. 加锁之后，数据库异常了会自动释放锁，而执行set global readonly = tue则不会释放锁

   4. 使用什么来更好的备份数据库？

      1. 对于可重复读隔离级别的数据库引擎，innodb来说。可以使用事务，备份数据库时先开启事务，这时就会创建一个read view 。再备份期间这个read view保持不变。而且由于mvcc的支持，其它事务的操作都可以正常进行。
      2. 官方提供的工具 mysqldump -single-transaction 备份时会先开启事务

2. 表锁

   1. 表锁

      1. 加锁语句

         1. lock tables 表名 read；共享锁
         2. lock tables 表名 write 独占锁
         3. 使用unlock tables 主动释放全部表锁

      2. 对于加共享锁的，所有线程都不能写，但是可读，本线程写会直接报错，其它线程写会阻塞。加写锁的，本线程可读写，其它线程读写被阻塞

      3. 举个例子, 如果在某个线程A中执行lock tables s2 read, s3 write; 这个语句，则其他线程写s2、读 写s3的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读s2、读写s3的操作。连写s2都不允许，自然也不能访问其他表。

   

    ![在这里插入图片描述](https://img-blog.csdnimg.cn/d2907b5f20644079ac4dd191d3c1864a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5aSp5LiK6aOe55qE5LqR5Lyg5aWH,size_20,color_FFFFFF,t_70,g_se,x_16)
   

   2. 元数据锁MDL，为了防止DDL和DML并发修改冲突

      1. 每执行一条DDL、DML语句都会自动申请MDL锁，DDL申请MDL写锁，DML申请MDL读锁。对一个表做增删改查操作的时候，加MDL读锁；当

         要对表做结构变更操作的时候，加MDL写锁

      2. 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

      3. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

      4. 申请MDL的操作会形成一个队列，队列中写锁获取优先于读锁获取。写锁等待(无法申请到写锁)，一旦队列中有写锁等待，则阻塞当前操作和后续

      5. 事务中的MDL锁，会持续被持有，直到事务结束。但是当有DDL操作时，会隐式释放锁，保证元数据独占锁的释放

      6. 一个场景

         1. 线程A启动一个事务，然后执行一个select，获取MDL读锁。但不释放，模拟长事务
         2. 线程B，执行select，此时不会被阻塞，读读共享
         3. 线程C，修改了表结构，此时会去获取MDL写锁。但是读锁还被持有，所有写锁获取不到，进入写锁等待。
         4. 此时线程C被阻塞后，后续所有crud，或者DDL操作都会被阻塞
         5. 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。
         6. 那么如何安全的修改表结构？比如增加一个字段
            1. 首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务
            2. 如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
               1. 这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面 设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。
               2. MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAITn这个语法。
                  1. ALTER TABLE tbl_name NOWAIT add column …
                  2. ALTER TABLE tbl_name WAIT N add column …

   3. 意向锁 快速判断有没有行锁-独占行锁

      1. 意向共享锁和意向独占锁是表级锁，不会和行级的独占和共享锁冲突，意向锁之间也不会冲突，只会和共享表锁和独占表锁发生冲突

   4. AUTO-INC 锁 自增字段使用

3. 行锁 就是针对数据表中行记录的锁

   1. Record Lock 记录锁 锁当前记录

   2. Gap Lock 间隙锁 锁一个范围，不包括当前记录

   3. next-key 记录+间隙

   4. 行锁

      1. 两阶段锁

         1. InnoDB中行锁是需要时加上，但并不是执行完语句后就释放，而是等待事务提交或回滚之后释放。这就是两阶段锁协议
         2. 再事务中尽量把并发高的，最有可能造成锁冲突的往事务后面放，尽量减少行锁被持有的时间。

      2. 死锁和死锁检测

         1. 死锁

         

           3.  可以看到线程B压根就没更新，我更新的也不是同一行。所有也就模拟不了死锁。
         
           4. 正常模拟 应该是 线程A 开启事务修改id 1  线程B 开启事务修改 id 2 然后线程 下一步 线程A更新 id 2 线程B 更小 id 1这样就会死锁了
         
              两个线程互相持有着对方想要的资源。
         

         2. 可能模拟失败是因为行锁 锁的 是索引，挺复杂的之后再看

         3. 死锁检测

            1. 当出现死锁以后，有两种策略：

               • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。

               • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

​ 2. 死锁检测是耗费CPU的，其它方法

​ 1. 临时关闭死锁检测

​ 2. 控制并发度

​ 3. 将一行记录 改成逻辑上的多行，累加求多行。