MySql常用锁简单介绍

一时失言乱红尘 2023-06-23 02:59 39阅读 0赞

#### 前言 ####

1.  锁是计算机协调多个进程或线程并发访问某一资源的机制。
2.  在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
3.  Mysql 常用的锁主要分为 乐观锁 和悲观锁。
    
    1.  乐观锁是一种思想，在表结构设计中加入了Version控制，每次更新时对比Version 是否一致。
    2.  悲观锁一般是指由mysql 实现的锁：  
        a. 把Mysql实现的锁全部归为悲观锁，感觉也不太合理，姑且先这样；  
        b. 其中从锁的粒度上划分【 行锁 / 表锁】；  
        c. 从数据库操作上划分【共享锁（读锁）和排它锁（写锁）】和 意向锁；  
        d.行锁又可以分为：记录锁、间隙锁、临键锁。

#### 演示数据准备： ####

创建一张账户表【注意索引】：

1.  id 主键索引
2.  num普通索引
3.  存储引擎： InnoDB

CREATE TABLE `account` (
      `id` int NOT NULL AUTO_INCREMENT,
      `num` int DEFAULT NULL COMMENT '数量',
      `version` int DEFAULT '1' COMMENT '版本',
      PRIMARY KEY (`id`),
      KEY idx_num (`num`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='账户信息';
    
    INSERT INTO `work`.`account` (`id`, `num`, `version`) VALUES ('1', '2', '1');
    INSERT INTO `work`.`account` (`id`, `num`, `version`) VALUES ('6', '4', '1');
    INSERT INTO `work`.`account` (`id`, `num`, `version`) VALUES ('13', '9', '1');

#### 乐观锁： ####

1.  乐观锁是一种思想，它认为数据一般情况下不会发生冲突，在数据中加入 Version 字段，查询出数据并进行计算，更新时会比对版本判断数据是否被其他事务更新，如果 version 相同，则将数据进行更新， version ++，否则一般重新进行计算。
2.  使用场景： 行数据并发量小；若并发大时，反而会导致多个线程频繁进行重新计算，得不偿失。
3.  Java实现过程伪代码：  
    a. 查询出学生信息： select \* from account where id= 1;  
    b.经过计算后更新： update account set money = money -500 , version = version +1 where id=1 and version =1  
    c. 当有其他线程更新数据之后，version 发生了变化，本次更新失败，然后再重新 a，b 操作，一般情况下代码最多重复更新 3次。
4.  优势劣势：  
    a. 乐观并发控制相信事务之间的数据竞争(datarace)的概率是比较小的， 因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。  
    b. 乐观锁虽然没有依赖数据库提供的锁机制，也可以保证数据一致性。  
    c. 其次Mysql 在做 insert、update、delete 都加排它锁，并且操作的数据都是最新的。

#### 读写锁、行表锁、意向锁简介： ####

###### 1.读写锁与行表锁： ######

1.  读锁（共享锁）：
    
     *  针对同一份数据，多个读操作可以同时进行而不会互相影响
     *  共享锁被称为“读锁”，但实际上在可重复读级别下，innodb 通过 MVCC 机制实现了无需加锁即可以避免读写冲突，所以在可重复读的级别下，普通的读取是不加锁的。
     *  给Mysql 行加读锁，在事务中执行：select … lock in share mode，事务结束会自动释放读锁；
     *  给Mysql 表加读锁：lock table xxx read；
     *  给Mysql 表解锁：unlock tables；
2.  写锁（排它锁）：
    
     *  当前写操作没有完成前，它会阻断其他写锁和读锁
     *  给Mysql 行加写锁，在事务中执行： select … for update，事务结束会自动释放写锁，；
     *  给Mysql 表加写锁，在线程中执行：lock table xxx write；
     *  给Mysql 表解锁，在线程中执行：unlock tables；
3.  行锁注意：
    
     *  行锁的生命周期在整个事务中，事务结束自动释放锁，会影响到其他所有要操作当前行的事务。
     *  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
     *  MyISAM不支持行锁，但支持表锁， Innodb 表锁和行锁均支持。
     *  行锁可以细分为记录锁、间隙锁与临键锁，Mysql 根据加锁的sql、索引、以及数据去确定加什么类型的锁，后面有介绍。
4.  表锁注意：
    
     *  表锁过程在整个线程中，需要手动加锁和释放锁，在线程结束之前务必要解锁，避免锁表。
     *  表级锁：开销小，加锁快；不会出现死锁，但可能会锁表；锁定粒度大，发生锁冲突的概率最高，并发度最低。
5.  避免死锁的常用方法
    
     *  在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。。
     *  在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
     *  设置innodb\_lock\_wait\_timeout 锁等待超时是最为简单粗暴的办法，innodb 提供了加锁阻塞超时时间的设置， 该配置项的单位是秒数
     *  主动死锁检测  
        innodb 提供了主动死锁检测机制【innodb\_deadlock\_detect 】，innodb 在锁冲突发生时，会扫描持有该锁或在竞争该锁的事务，判断他们之间是否有可能产生死锁，一旦发现当前事务的等待会产生死锁，那么就会立即返回错误。  
        看上去主动死锁检测 + 业务重试可以解决所有的死锁问题了，但是这同样存在一定的问题。  
        由于整个主动死锁检测过程需要循环遍历所有持有或等待锁的事务两两间的持有锁情况，所以这个过程的时间复杂度是 O(n^2)，在高并发的场景下，例如有 1000 个并发的线程同时更新一行，虽然他们之间并不会产生死锁，但主动死锁检测却要进行 100 万次对比，最终造成 CPU 利用率的飙高。
     *  慎用Mysql的锁，尽量不给不存在的数据加锁，不多次加。

###### 2. 意向锁： ######

1.  意向锁分为意向共享锁和意向排它锁
2.  那么意向锁和普通的读写锁有什么区别呢？
    
     *  考虑一个事务通过 select … lock in share mode 对某一行加了共享锁，此时另一个事务要对这一行加排它锁，我们知道，第二个事务会进入阻塞等待，但如果一个事务准备给全表加排它锁呢？显然，他需要遍历全表中的所有记录，查看每一条记录的加锁状态，才能决定是否能够加锁成功，这显然是效率很低的
     *  解决办法很简单，我们只需要在对某一行加锁前，将整个表标记为“某些行已经加了共享锁”的状态，那么另一个事务对于整个表的加锁操作就不需要像我们前面所说的那样去遍历每一行了
     *  意向锁就是我们这里说的“某些行已经加了锁”的状态标识，所有的共享锁加锁前都要对表加意向共享锁，排它锁加锁前，都要对表加意向排它锁，而意向锁之间不互斥
3.  意向共享锁和意向排它锁的加锁时机,有了上面我们描述的这段话，就可以得到知道意向锁的加锁时机了：
    
     *  S 锁加锁前先加 IS 锁
     *  X 锁加锁前先加 IX 锁

#### 关于行锁的细分： ####

###### 0. 数据准备用于演示: ######

1.  account 表 ： id是主键索引 ， num 是普通索引，数据集合如下：

###### 1. 记录锁: ######

记录锁很好理解，一般要通过主键或唯一索引加锁，锁精确加在某一行上。  
**注意：** 如果id 不存在的话，记录锁会退化为 间隙锁，下面讲  
示例：

select * from account where id= 1 for update;   // 给id =1 的数据进行加锁。
    
    select * from account where id in (1,6) for update;  // 给 id =1，6 的数据进行加锁

###### 3. 间隙锁： ######

锁可以加在不存在的空闲空间，也可以是两个索引记录之间，也可能是第一个索引记录之前（负无穷，first-key）或最后一个索引之后(last-key，正无穷）的无限空间，两边开口  
表 account存在的间隙 （-∞，1）、（1,6）、（6,13），（13，∞）  
示例：

select * from account where id =3 for update;  // 间隙锁区间 （1,6）；

###### 4. 临键锁： ######

记录锁与间隙锁组合起来用就叫做Next-Key Lock，就是将键及其两边的的间隙加锁（向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间）。

关于 间隙锁 和 临键锁 的区别，博主在网上找了很多资料，也没有确定Mysql是如何选择的，但他们都是基于索引来实现的，用于解决幻读，根据不同的索引，数据，查询条件，加锁的方式也不同。

### 总结 ###

*  InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
 *  记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
 *  间隙锁和临键锁均是基于索引实现的，算法根据不同索引、数据、where 条件选择不同的锁。
 *  除了表锁需要显示加锁之外，其余锁是Mysql 提供的算法来选择的。

### END!!! ###