MySQL -乐观锁与悲观锁

乐观锁与悲观锁是数据库的一种思想，和其他的排它锁，共享锁之类的不是一类含义。在并发的情况下，采用乐观锁或者悲观锁可以防止数据问题。

悲观锁

定义

悲观锁是一种对数据库操作持一种保守态度的思想，即所有事务对数据库的操作都会产生冲突，。悲观锁的处理方式是为当前事务中的操作数据上锁，其他事务也操作相同数据的话需要等待当前事务完成。通过这种方式来保证数据的完整性。

示例

MySQL中使用悲观锁的话，需要关闭自动提交功能，因为在事务中，数据一旦提交，就会更新到数据库中。关闭事务提交：

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)


-- 查询当前事务提交状态
mysql> show variables 'autocommit';
ERROR 1064 (42000): 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 ''autocommit'' at line 1
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MySQL触发悲观锁的sql语句：

select ···· from tablename for update;

我们假设一个给员工涨薪的场景（这里可以实现无限涨薪，嗯~~），下面场景因为是手动提交事务，所以不用设置事务关闭状态。

薪资表：

CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- 数据
INSERT INTO `test01`.`salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES ('10001', '10000', '1986-06-26', '1987-06-26');
INSERT INTO `test01`.`salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES ('10002', '72527', '1996-08-03', '1997-08-03');
INSERT INTO `test01`.`salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES ('10003', '90000', '1996-08-03', '1997-08-03');
INSERT INTO `test01`.`salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES ('10004', '2000', '2004-01-22', '2021-01-22');

悲观锁涉及到行锁定以及表锁定，当索引确定时，会使用行锁，当索引不确定以及无索引时，会使用表锁。当前场景里面，emp_no是主键索引。

示例1：主键索引查询，行锁。查询emp_no为10001员工的数据。

同时开启A和B两个事务，A事务查询emp_no为10001的数据时，可以查出来数据，B这个时候再次查询数据，会等待A事务结束之后才能出来数据，如果等待超时会报等待超时的错误。

若此时A事务结束，那么B事务的结果会出来；

锁定相同行时会等待，若是A事务和B事务查询的不是相同数据呢？

上面图片证明，若是查询不是同一索引时，互相不会受到影响。

示例2：非主键索引查询，行锁。加一行员工名称字段，并且加索引

alter table salaries add name varchar(20) not null comment '员工名称';
alter table salaries add index index_name (name);


update salaries set name = 'aa' where emp_no = '10001';
update salaries set name = 'bb' where emp_no = '10002';
update salaries set name = 'cc' where emp_no = '10003';
update salaries set name = 'dd' where emp_no = '10004';

开启两个事务，查询名称为aa的工资信息,与主键索引查询是一样的效果。

示例3：非索引列查询，表锁。开启两个事务，分别查询from_date为‘1986-06-26’，‘1996-08-03’的数据。

虽然两个事务查询的不是同一行数据，但是B事务查询仍然被堵塞了。因此，非索引列查询时是表锁。

示例4：索引范围查找，表锁。两个事务分别查询emp_no大于10003的列表，emp_no等于10001的数据

虽然A事务中的数据不包含B事务所要查询的数据，但是B事务仍然要等待A事务结束之后才能查询出数据。因此范围查找是表锁。

总结

综合上面的示例可以看出，悲观锁利用数据库的锁机制虽然可以很好的将两个事务隔离开，保证事务的隔离性以及数据的完整性，但是性能低下，耗时较长。现在大多数公司都不会采用这种机制维护并发事务。一般的思路是采用乐观锁，下面学习一下乐观锁机制。

乐观锁

定义

乐观锁是对数据库操作持乐观态度，即所有事务操作都不会引发数据冲突。乐观锁在并发事务中采用版本控制保证数据的完整性。其实版本控制只是其中的一种方式，也可以采用其他可以保证数据一致性的方法，比如时间戳控制之类。如果并发事务冲突，会让用户去处理后续事务。

乐观锁的图（网上好多，随便拿了一个）

示例

继续以salaries表为例，对salaries表进行更新操作，salaries表添加一个版本字段，用于控制版本，并且初始化每条数据版本为1：

我以Spring Boot+ Mybatis项目为例，测试两个事务同时对一条数据做修改，同时更新emp_no为10001的这条数据，查看是否都能更新成功。介绍一下各种java类

实体SalariesVO：

package com.myproject.demo.salaries.vo;

import lombok.Data;

import java.time.LocalDate;

@Data
public class Salaries {
    private String empNo;
    private Integer salary;
    private String name;
    private LocalDate fromDate;
    private LocalDate toDate;
    private Integer version;
}

接口SalariesMapper

package com.myproject.demo.salaries.mapper;

import com.myproject.demo.salaries.vo.Salaries;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

@Mapper
@Component
public interface SalariesMapper {
    Salaries getSalariesById(@Param("empNo") String id, @Param("version") String version);

    int add(@Param("item") Salaries salaries);

    int update(@Param("item") Salaries salaries, @Param("newVersion") Integer version);
}

mapper对应的xml:SalariesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.myproject.demo.salaries.mapper.SalariesMapper">
    <resultMap id="baseMap" type="com.myproject.demo.salaries.vo.Salaries">
        <id column="emp_no" property="empNo"></id>
        <result column="salary" property="salary"></result>
        <result column="from_date" property="fromDate"></result>
        <result column="to_date" property="toDate"></result>
        <result column="name" property="name"></result>
        <result column="version" property="version"></result>
    </resultMap>
    <select id="getSalariesById" resultMap="baseMap">
        select * from salaries
        where emp_no = #{empNo}
    </select>

    <insert id="add">
        insert into salaries(emp_no,salary,from_date,to_date,name)
        values(#{item.empNo}, #{item.salary}, #{item.fromDate},#{item.toDate}, #{item.name})
    </insert>

    <update id="update">
        update salaries set
        salary = #{item.salary},
        from_date = #{item.fromDate},
        to_date = #{item.toDate},
        version = version + 1
        where emp_no = #{item.empNo}
        and version = #{item.version}
    </update>
</mapper>

逻辑类SalariesService:

package com.myproject.demo.salaries.service;

import com.myproject.demo.salaries.mapper.SalariesMapper;
import com.myproject.demo.salaries.vo.Salaries;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDate;

@Service
public class SalariesService {
    @Autowired
    SalariesMapper salariesMapper;

    public void updateSalaries() {
        Salaries salaries1 = salariesMapper.getSalariesById("10001");
        int result1 = update1(salaries1);
        int result2 = update2(salaries1);
        System.out.println("result1===>" + (result1 == 1 ? "更新成功" : "更新失败"));
        System.out.println("result2===>" + (result2 == 1 ? "更新成功" : "更新失败"));
    }

    public int update1(Salaries salaries) {
        salaries.setSalary(salaries.getSalary() + 1000);
        salaries.setFromDate(LocalDate.now());
        salaries.setToDate(LocalDate.of(2022, 10, 30));
        return salariesMapper.update(salaries);
    }

    public int update2(Salaries salaries) {
        salaries.setSalary(salaries.getSalary() + 500);
        salaries.setFromDate(LocalDate.now());
        salaries.setToDate(LocalDate.of(2022, 10, 30));
        return salariesMapper.update(salaries);
    }
}

update1()方法和update2()方法分别代表两次相同版本的更新操作，查看是否都能成功更新：

可以看到，update操作时版本相同都为4，第一个更新操作执行成功，第二个失败，说明版本控制避免了脏数据的产生。

总结

乐观锁在高并发的情况下，可以在众多事务操作同一条数据的情况下，只保证一条事务成功执行。不知道有没有听说过MySQL的MVCC机制，我在学习乐观锁的过程中，发现乐观锁和MVCC机制都是在用版本控制并发事务数据不受干扰。我研究了一下，发现两者还是有很大不同的，接下来介绍一下MVCC机制：

MVCC

定义

MVCC机制是MySQL中的一种针对并发事务的数据保护机制，并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。。它使用事务版本控制来操作当前查询数据是哪个事务的版本。在我们平时接触的MySQL数据表之外，其实每个表默认都会加当前事务id以及删除事务id来控制当前查询数据信息。之前我有写过一篇《事务隔离级别》的文章，里面的可重复读就是用的MVCC(multi-version concurrency control)机制，拿两个事务简单来说，开启两个事务开始的数据都是一致的，事务1和事务2的salary字段值都是10000，之后事务2将salary+5000，并且提交，那么事务1查询，salary字段就变为了15000了。这样的效果其实是MVCC机制的作用。

注意：事务开启的标准不是输入begin或者start transaction，而是在他们之后执行的第一个操作数据表语句才算开启事务，事务id才会生成。

实践一下，数据库里面存储的id为12的age为19，开启两个事务1和2，在事务2中更新id为12 的age为18，提交。在1事务中查询，id=12的age为18，是2事务提交后的结果。

MVCC机制是这样的：

insert，update, delete等改表数据等操作：在当前版本下改变数据结果。

select 查询操作：会根据以下条件查询事务版本所对应的快照数据：创建事务id<=max(当前事 务id，快照点已提交最大事务id）删除事务id> max(当前事 务id，快照点已提交最大事务id）。

用上面图片里面的举例：快照点已提交最大事务=2，当前事务id=1。那么数据就等于：创建事务id<=2,快照点已提交的没有删除事务，所以删除事务id>1,因此查出来的数据包含更新事务id<=2,删除事务id>1的数据之和。

MVCC和乐观锁的区别（从知乎上摘抄：https://www.zhihu.com/question/27876575/answer/71836010）：

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读

乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试。乐观并发控制类似自选锁。乐观并发控制适用于低数据争用，写冲突比较少的环境