MySQL锁的分类和演示

落日映苍穹つ 2024-03-16 11:49 44阅读 0赞

##  ##

## 1.锁的概念 ##

*  之前我们学习过多线程，多线程当中如果想保证数据的准确性是如何实现的呢？没错，通过同步实现。同步就相当于是加锁。加了锁以后有什么好处呢？当一个线程真正在操作数据的时候，其他线程只能等待。当一个线程执行完毕后，释放锁。其他线程才能进行操作！
 *  那么我们的MySQL数据库中的锁的功能也是类似的。在我们学习事务的时候，讲解过事务的隔离性，可能会出现脏读、不可重复读、幻读的问题，当时我们的解决方式是通过修改事务的隔离级别来控制，但是数据库的隔离级别呢我们并不推荐修改。所以，锁的作用也可以解决掉之前的问题！
 *  锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
 *  举例，在电商网站购买商品时，商品表中只存有1个商品，而此时又有两个人同时购买，那么谁能买到就是一个关键的问题。
    
    这里会用到事务进行一系列的操作：
    
    1.  先从商品表中取出物品的数据
    2.  然后插入订单
    3.  付款后，再插入付款表信息
    4.  更新商品表中商品的数量
    
    以上过程中，使用锁可以对商品数量数据信息进行保护，实现隔离，即只允许第一位用户完成整套购买流程，而其他用户只能等待，这样就解决了并发中的矛盾问题。
 *  在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

## 2.锁的分类 ##

*  ### 按操作分类： ###
    
     *  共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。
     *  排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
 *  ### 按粒度分类： ###
    
     *  表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
     *  行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
     *  页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
 *  ### 按使用方式分类： ###
    
     *  悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
     *  乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
 *  不同存储引擎支持的锁

<table> 
 <thead> 
  <tr> 
   <th>存储引擎</th> 
   <th>表级锁</th> 
   <th>行级锁</th> 
   <th>页级锁</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MyISAM</td> 
   <td>支持</td> 
   <td>不支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>InnoDB</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>MEMORY</td> 
   <td>支持</td> 
   <td>不支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>BDB</td> 
   <td>支持</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
 </tbody> 
</table>

## 3.演示InnoDB锁 ##

*  数据准-- 标准语法  
    SELECT语句 LOCK IN SHARE MODE;备

-- 创建db13数据库
    CREATE DATABASE db13;
    
    -- 使用db13数据库
    USE db13;
    
    -- 创建student表
    CREATE TABLE student(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(10),
    	age INT,
    	score INT
    );
    -- 添加数据
    INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95),
    (NULL,'王五',25,98),(NULL,'赵六',26,97);

### 共享锁 ###

-- 标准语法
    SELECT语句 LOCK IN SHARE MODE;

-- 窗口1
    /*
    	共享锁：数据可以被多个事务查询，但是不能修改
    */
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录。加入共享锁
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 查询分数为99分的数据记录。加入共享锁
    SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
    
    -- 提交事务
    COMMIT;

-- 窗口2
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录(普通查询，可以查询)
    SELECT * FROM student WHERE id=1;
    
    -- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
    UPDATE student SET NAME='张三三' WHERE id = 1;
    
    -- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
    UPDATE student SET NAME='李四四' WHERE id = 2;
    
    -- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
    UPDATE student SET NAME='王五五' WHERE id = 3;
    
    -- 提交事务
    COMMIT;

#### 排他锁 ####

-- 标准语法
    SELECT语句 FOR UPDATE;

-- 窗口1
    /*
    	排他锁：加锁的数据，不能被其他事务加锁查询或修改
    */
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录，并加入排他锁
    SELECT * FROM student WHERE id=1 FOR UPDATE;
    
    -- 提交事务
    COMMIT;

-- 窗口2
    -- 开启事务
    START TRANSACTION;
    
    -- 查询id为1的数据记录(普通查询没问题)
    SELECT * FROM student WHERE id=1;
    
    -- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
    SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
    
    -- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
    SELECT * FROM student WHERE id=1 FOR UPDATE;
    
    -- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
    UPDATE student SET NAME='张三' WHERE id=1;
    
    -- 提交事务
    COMMIT;

### 注意：锁的兼容性 ###

*  共享锁和共享锁 兼容
 *  共享锁和排他锁 冲突
 *  排他锁和排他锁 冲突
 *  排他锁和共享锁 冲突、

## 4.演示MyISAM锁 ##

数据准备

-- 创建product表
    CREATE TABLE product(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(20),
    	price INT
    )ENGINE = MYISAM;  -- 指定存储引擎为MyISAM
    
    -- 添加数据
    INSERT INTO product VALUES (NULL,'华为手机',4999),(NULL,'小米手机',2999),
    (NULL,'苹果',8999),(NULL,'中兴',1999);

读锁

-- 标准语法
    -- 加锁
    LOCK TABLE 表名 READ;
    
    -- 解锁(将当前会话所有的表进行解锁)
    UNLOCK TABLES;

-- 窗口1
    /*
    	读锁：所有连接只能读取数据，不能修改
    */
    -- 为product表加入读锁
    LOCK TABLE product READ;
    
    -- 查询product表(查询成功)
    SELECT * FROM product;
    
    -- 修改华为手机的价格为5999(修改失败)
    UPDATE product SET price=5999 WHERE id=1;
    
    -- 解锁
    UNLOCK TABLES;

-- 窗口2
    -- 查询product表(查询成功)
    SELECT * FROM product;
    
    -- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功)
    UPDATE product SET price=5999 WHERE id=1;

写锁

-- 标准语法
    -- 加锁
    LOCK TABLE 表名 WRITE;
    
    -- 解锁(将当前会话所有的表进行解锁)
    UNLOCK TABLES;

-- 窗口1
    /*
    	写锁：其他连接不能查询和修改数据
    */
    -- 为product表添加写锁
    LOCK TABLE product WRITE;
    
    -- 查询product表(查询成功)
    SELECT * FROM product;
    
    -- 修改小米手机的金额为3999(修改成功)
    UPDATE product SET price=3999 WHERE id=2;
    
    -- 解锁
    UNLOCK TABLES;

-- 窗口2
    -- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
    SELECT * FROM product;
    
    -- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
    UPDATE product SET price=2999 WHERE id=2;

## 5.演示悲观锁和乐观锁 ##

*  ### 悲观锁的概念 ###
    
     *  就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
     *  整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
     *  我们之前所学的行锁，表锁不论是读写锁都是悲观锁。
 *  ### 乐观锁的概念 ###
    
     *  就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
     *  但是在更新的时候会去判断在此期间数据有没有被修改。
     *  需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
 *  ### 悲观锁和乐观锁使用前提 ###
    
     *  对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
     *  如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。
 *  ### 乐观锁的实现方式 ###
    
     *  版本号
        
         *  给数据表中添加一个version列，每次更新后都将这个列的值加1。
         *  读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
         *  如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
         *  用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

-- 创建city表
    CREATE TABLE city(
    	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
    	NAME VARCHAR(20),                   -- 城市名称
    	VERSION INT                         -- 版本号
    );
    
    -- 添加数据
    INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);
    
    -- 修改北京为北京市
    -- 1.查询北京的version
    SELECT VERSION FROM city WHERE NAME='北京';
    -- 2.修改北京为北京市，版本号+1。并对比版本号
    UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

时间戳

*  和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
 *  每次更新后都将最新时间插入到此列。
 *  读取数据时，将时间读取出来，在执行更新的时候，比较时间。
 *  如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

## 6.锁的总结 ##

*  ### 表锁和行锁 ###
    
     *  行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
     *  表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！
 *  ### InnoDB锁优化建议 ###
    
     *  尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。
     *  合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。
     *  尽可能减少基于范围的数据检索过滤条件。
     *  尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
     *  在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
     *  对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。