mysql 锁（标贝科技）

标贝科技 https://ai.data-baker.com/#/?source=qwer12

填写邀请码fwwqgs，每日免费调用量还可以翻倍

mysql 锁

锁类型

类型

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

• • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
  • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

• • 最大程度的支持并发，同时也带来了最大的锁开销
  • 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的
  • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

锁粒度和兼容

意向锁

• 实际应用中InnoDB许多行级锁与表级锁共存

• 未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向（在索引树上打一个标记，其他表锁看到意向锁必须等待）

意向锁分类：

• 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

• 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

意向锁获取

• 事务要获得某些行的S锁，必须先获得表的IS锁
• 事务要获得某些行的X锁，必须先获得表的IX锁

意向锁的兼容

• InnoDB使用共享锁，可以提高读读并发
• 为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性
• InnoDB使用插入意向锁，可以提高插入并发
• 由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，可以并行

锁冲突兼容

InnoDB 锁实现方式

行锁

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时
  别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点

间隙锁

• 间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：防止间隙内有新数据被插入

• innodb自动使用条件：

  1.事务级别在RR级别下
  2.检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

• 对记录之间的间隙锁定

next-key锁

• next-key锁的目的是解决可重复度
• 实现方案：间隙锁+行锁

隔离级别和锁

rc更新

rc 插入

rr更新

rr插入

一致性非锁定读原理(MVCC)

多版本并发控制。MVCC是一种并发控制的方法。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

当前读和快照读

• 当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
• 快照读：不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
• MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

MVCC原理

依赖记录中的 3个隐式字段，undo日志 ，Read View

隐式字段

• DB_TRX_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
• DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
• DB_ROW_ID：6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

undo日志

• insert undo log
  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log
  事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

undolog记录流程

1. 开启事务，修改该行(记录)数据时，数据库会先对该行加`排他锁`
2. 在`undo log`中增加当前行的拷贝副本
3. 拷贝完毕后，修改该行`name`为标贝科技，并且修改隐藏字段的事务ID为当前`事务1`的ID, 我们默认从`1`开始，之后递增，回滚指针指向拷贝到`undo log`的副本记录，既表示我的上一个版本就是它
4. 事务提交后，释放锁

执行事务：

undolog日志:

1. 开启事务，修改该行数据，数据库先为该行加锁
2. 把该行数据拷贝到`undo log`中，作为旧记录，发现该行记录已经有`undo log`了，那么最新的旧数据作为链表的表头，插在该行记录的`undo log`最前面
3. 修改该行`age`为18岁，并且修改隐藏字段的事务ID为当前`事务2`的ID, 那就是`2`，回滚指针指向刚刚拷贝到`undo log`的副本记录
4. 事务提交，释放锁

执行事务：

undolog日志:

序号3、2、1通过回滚指针串联起来

Read View

• 事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
• Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

ReadView和事务

• read uncommitted隔离级别事务：直接读取记录的最新版本

• serializable隔离级别事务：使用加锁的方式来访问记录

• RC和RR隔离级别事务：需要用到版本链概念，核心问题是如何判断版本链中哪个版本是当前事务可见的

• readview中四个比较重要的概念：

• • m_ids：表示在生成readview时，当前系统中活跃的读写事务id列表
  • min_trx_id：表示在生成readview时，当前系统中活跃的读写事务中最小的事务id，也就是m_ids中最小的值
  • max_trx_id：表示生成readview时，系统中应该分配给下一个事务的id值
  • creator_trx_id：表示生成该readview的事务的事务id

• 有了readview，在访问某条记录时，按照以下步骤判断记录的某个版本是否可见

• • 1、如果被访问版本的trx_id，与readview中的creator_trx_id值相同，表明当前事务在访问自己修改过的记录，该版本可以被当前事务访问

  • 2、如果被访问版本的trx_id，小于readview中的min_trx_id值，表明生成该版本的事务在当前事务生成readview前已经提交，该版本可以被当前事务访问

  • 3、如果被访问版本的trx_id，大于或等于readview中的max_trx_id值，表明生成该版本的事务在当前事务生成readview后才开启，该版本不可以被当前事务访问

  • 4、如果被访问版本的trx_id，值在readview的min_trx_id和max_trx_id之间，就需要判断trx_id属性值是不是在m_ids列表中

  • • 如果在：说明创建readview时生成该版本的事务还是活跃的，该版本不可以被访问
    • 如果不在：说明创建readview时生成该版本的事务已经被提交，该版本可以被访问

• 生成readview时机

• • RC隔离级别：每次读取数据前，都生成一个readview
  • RR隔离级别：在第一次读取数据前，生成一个readview
    

struct trx_t {
    /* 事务ID */
    trx_id_t    id;        /*!< transaction id */
    /* 一致性读的快照 */
    ReadView*    read_view;    /*!< consistent read view used in the transaction, or NULL if not yet set */
    // 省略一大堆属性...
}

class ReadView {
    /**
     * 创建这个快照的事务ID
     */
    trx_id_t    m_creator_trx_id;
    /**
     * 生成这个快照时处于活跃状态的事务ID的列表，
     * 是个已经排好序的列表
     */
    ids_t        m_ids;
    /** 
     * 高水位线：id大于等于 m_low_limit_id 的事务都不可见。
     * 在生成快照时，它被赋值为“下一个待分配的事务ID”（会大于所有已分配的事务ID）。
     */
    trx_id_t    m_low_limit_id;
    /**
     * 低水位线：id小于m_up_limit_id的事务都不可见。
     * 它是活跃事务ID列表的最小值，在生成快照时，小于m_up_limit_id的事务都已经提交（或者回滚）。
     */
    trx_id_t    m_up_limit_id;

    // 判断事务是否可见的方法
    bool changes_visible(){}
    // 关闭快照的方法
    void close(){}
    // ...
}

/* 判断某个事务的修改对当前事务是否可见 */
bool changes_visible(){
        /**
         * 可见的情况：
         *  1. 小于低水位线，即创建快照时，该事务已经提交(或回滚)
         *  2. 事务ID是当前事务。
         */
        if (id < m_up_limit_id || id == m_creator_trx_id) {
            return(true);
        }
        if (id >= m_low_limit_id) { /* 高于水位线不可见，即创建快照时，该事务还没有提交 */
            return(false);

        } else if (m_ids.empty()) { /* 创建快照时，没有其它活跃的读写事务时，可见 */
            return(true);
        }
        /**
         * 执行到这一步，说明事务ID在低水位和高水位之间，即 id ∈ [m_up_limit_id, m_low_limit_id)
         * 需要判断是否属于在活跃事务列表m_ids中，
         * 如果在，说明创建快照时，该事务处于活跃状态（未提交），修改对当前事务不可见。
         */
        // 获取活跃事务ID列表，并使用二分查找判断事务ID是否在 m_ids中
        const ids_t::value_type*    p = m_ids.data();
        return(!std::binary_search(p, p + m_ids.size(), id));
}
bool lock_clust_rec_cons_read_sees()
{
    // 获取修改这个数据行的事务ID
    trx_id_t    trx_id = row_get_rec_trx_id(rec, index, offsets);

    // 调用 changes_visible() 判断是否可见，如果不可见则取查找undolog
    return(view->changes_visible(trx_id, index->table->name));
}

MVCC整体请求流程

针对记录 X 的操作事务（RR或RC隔离级别下）

当前的m_low_limit_id = 4+1=5 、 trx_id_t m_up_limit_id = 1,readView:[事务1,2,3]

m_low_limit_id = 4+1=5 //下一个事务的id
m_up_limit_id = 1	// 活跃的最小事务id
read_view = [事务1,事务2,事务3] //活跃事务
m_creator_trx_id = 2		// 当前事务
    /**
     * 事务2的可查询情况(事务1,3不修改数据)
     *  RC隔离级别下
     *      可以查到事务4提交的数据
     *  RR隔离级别下
     *      可以查到事务0提交的数据
     *
     * 事务2的可查询情况(事务1,3修改数据)
     *  RC隔离级别下
     *      可以查到事务4的数据
     *  RR隔离级别下
     *      可以查到事务0的数据
     */

参考文献
[1]https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html
[2]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_packets.html#sect_protocol_basic_packets_packet
[3]https://www.jianshu.com/p/5e6b33d8945f
[4]https://cloud.tencent.com/developer/article/1768901
[5]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods.html
[6]https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase.html
[7]https://dev.mysql.com/doc/internals/en/
[8]https://www.cnblogs.com/wyq178/p/11576065.html
[9]Mysql技术内幕：InnoDB存储引擎 (第2版). 姜承尧
[10]MySQL运维内参：MySQL、Galera、Inception核心原理与最佳实践. 周彦伟，王竹峰，强昌金
[11]https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
[12]https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html