mysql之乐观锁悲观锁（InnoDB）

╰半橙微兮° 2023-05-28 12:52 15阅读 0赞

MySQL/InnoDB的加锁，一直是一个常见的话题。例如，数据库如果有高并发请求，如何保证数据完整性？产生死锁问题如何排查并解决？下面是不同锁等级的区别

*  **表级锁**：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高 ，**并发度最低。**
 *  **页面锁**：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，**并发度一般。**
 *  **行级锁**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，**并发度也最高。**

查看数据库拥有的存储引擎类型  
**`  SHOW ENGINES`**

## **`前言`** ##

**    乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库 性能的大量开销，特别是对长[事务][Link 1]而言，这样的开销往往无法承受。相对悲观锁而言，乐观锁更倾向于开发运用。**

**乐观锁（这里说的是在数据表加字段version来标识）-通过加字段来实现，好比Java程序里面改变逻辑保证数据的准确性**

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。**当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。**

**举例：**

1、数据库表三个字段，分别是id、value、version
    
        select id,value,version from TABLE where id = #{id}
    
    2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作
        
        update TABLE
            set value=2,version=version+1
            where id=#{id} and version=#{version}

## 悲观锁 ##

与乐观锁相对应的就是悲观锁了。**悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作**，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，**悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。**

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。**共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。**

### 共享锁 ###

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。如下图所示。

![format_png][]

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据

打开第一个查询窗口

begin;/begin work;/start transaction;  (三者选一就可以)
    #(lock in share mode 共享锁)
    SELECT * from TABLE where id = 1  lock in share mode;

然后在另一个查询窗口中，对id为1的数据进行更新

update TABLE set name="www.souyunku.com" where id =1;

此时，操作界面进入了卡顿状态，过了很久超时，提示错误信息  
**如果在超时前，第一个窗口执行`commit`，此更新语句就会成功**

[SQL]update test_one set name="www.souyunku.com" where id =1;
    [Err] 1205 - Lock wait timeout exceeded; try restarting transaction

加上共享锁后，也提示错误信息

update test_one set name="www.souyunku.com" where id =1 lock in share mode;
    [SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

在查询语句后面增加 **LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。** 其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

**加上共享锁后，对于update，insert，delete语句会自动加排它锁。**

### 排它锁: ###

排他锁 exclusive lock（也叫writer lock）又称写锁。  
   名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。 **排它锁是悲观锁的一种实现**，在上面悲观锁也介绍过

若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。**排它锁会阻塞所有的排它锁和共享锁**

读取为什么要加读锁呢？防止数据在被读取的时候被别的线程加上写锁。 排他锁使用方式：在需要执行的语句后面加上**for update**就可以了

select status from TABLE where id=1 for update;

**排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。**

![format_png 1][]

**排它锁-举例： **

** **  要使用排他锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

**我们可以使用命令设置MySQL为非autocommit模式：**

set autocommit=0;
    # 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
    # 1. 开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    # 2. 查询表信息（for update加锁）
    select status from TABLE where id=1 for update;
    # 3. 插入一条数据
    insert into TABLE (id,value) values (2,2);
    # 4. 修改数据为
    update TABLE set value=2 where id=1;
    # 5. 提交事务
    commit;/commit work

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjA0NzY0_size_16_color_FFFFFF_t_70][]

[Link 1]: https://baike.baidu.com/item/%E4%BA%8B%E5%8A%A1
[format_png]: /images/20230528/903f4e0755544d88982087f65a67d9c2.png
[format_png 1]: /images/20230528/b546ba4a15ac47e0b6faea408a6afe83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjA0NzY0_size_16_color_FFFFFF_t_70]: /images/20230528/b4632f9dc8304418a039aa8ad8c18085.png