MySQL的锁

最新推荐文章于 2024-03-18 10:58:54 发布

择维士

最新推荐文章于 2024-03-18 10:58:54 发布

阅读量275

点赞数

分类专栏：数据库文章标签： mysql 索引锁

本文链接：https://blog.csdn.net/scugxl/article/details/103161450

版权

数据库专栏收录该内容

14 篇文章 3 订阅

订阅专栏

Abstract

在Mysql如何实现隔离级别 - 可重复读和读提交源码分析中介绍了MySQL中如何实现读提交和可重复读主要是通过版本链+ReadView组成的MVCC实现的. 但是其中还有一个很重要的点就是MySQL如何用可重复读解决幻读问题的. 本文将演示(1)MySQL的可重复读中没有幻读, PostGreSQL有的. (2) MySQL中的锁.

幻读现象

什么是幻读

不同的隔离级别可能产生不同的"不稳定现象".
比如脏读-- 读到未提交的数据. 不可重复读 – 对单条记录的数据在同一事务中不能重复读取. 幻读 – 对于同一事务中的同一语句发现前后返回的记录条数不一样. 那么多出来的或者消失的行在使用者看来就是"Phantom Records". 像影子一样凭空出现了. 它一般发生在select … from xx where 或者 update from xxx where 这种.

MySQL怎么解决的?

在MySQL的可重复读隔离级别中解决了幻读现象, 在开始试验之前, 我们需要了解下MySQL中事务并发控制相关的第3个组件----锁. 其他两个分别是: 版本链 (存储多个版本数据); ReadView (看到应该看到的数据); 而锁则防止插入或者修改会产生错误的数据.

MySQL中的锁

MySQL的锁大体上分为行锁和表锁. 而行锁又分为如下几种:

LOCK_REC_NOT_GAP : 单个行记录上的锁
LOCK_GAP: 间隙锁, 锁定一个范围但是不包括记录本身. 主要用来解决幻读情况
LOCK_ORDINARY : 锁定一个范围并锁定记录本身. 就是这里的next-key lock. 注意值代表的含义是(上一个indexrecord, 当前record]

MySQL演示

假设如下的表tt (a int primary key, b int); 然后在b上有非唯一索引.
数据如下:
在这里插入图片描述
如下同时开始两个会话, 并且设置当前session隔离级别为repeatable read进行试验.
set session transaction isolation level repeatable read

Session1	Session2
begin; select * from tt where b = 30 for update;
	尝试: insert into tt values (29, 29); – 失败 block insert into tt values (29, 31); --失败block insert into tt values (29, 18); --成功 insert into tt values (31, 35); – 成功 insert into tt values (29, 20); – 成功 insert into tt values (31, 34); --失败

看出来了吗? 前面2次插入都失败了. 但是后面2次都成功了. 这里跟b的值有关系. 注意到我们会话一的select好像以某种gap锁的方式锁住了b值为(20, 34]的范围. 注意集合开闭.

如何验证??

在MySQL 8.0 中可以通过如下查询查到锁住的数据和类型:
SELECT * FROM performance_schema.data_locks\G;
输出如下:

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:1064:140393245168136
ENGINE_TRANSACTION_ID: 19512
            THREAD_ID: 49
             EVENT_ID: 83
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140393245168136
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:7:5:5:140393236938776
ENGINE_TRANSACTION_ID: 19512
            THREAD_ID: 49
             EVENT_ID: 83
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: tt_b
OBJECT_INSTANCE_BEGIN: 140393236938776
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 30, 30
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:7:4:5:140393236939128
ENGINE_TRANSACTION_ID: 19512
            THREAD_ID: 49
             EVENT_ID: 83
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140393236939128
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 30
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:7:5:7:140393236939480
ENGINE_TRANSACTION_ID: 19512
            THREAD_ID: 49
             EVENT_ID: 83
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: tt_b
OBJECT_INSTANCE_BEGIN: 140393236939480
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 34, 44

第1行是table tt的IX 表锁(for update导致的意向排它锁).
第2行, 记录索引tt_b上值为30的排它锁.
第3行,主键上的值为30的排它锁. – 为啥会有2,3两个锁? 是因为MySQL中的索引机制. 辅助索引和主键索引其实是分开存储的. 这个暂时不说了.
第4行是一个GAP锁, 锁住了b=34, a=44的记录.

关于GAP锁

MySQL的文档说的还是比较复杂的. 比如上面的例子中取决于b是主键? 还是有唯一索引? 有索引但是不是unique的? 或者没有索引. 其实锁住的记录都不同.
如果是主键索引select * from tt where a = 30 for update输出如下:

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:1064:140393245168136
ENGINE_TRANSACTION_ID: 19520
            THREAD_ID: 49
             EVENT_ID: 87
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140393245168136
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:7:4:5:140393236938776
ENGINE_TRANSACTION_ID: 19520
            THREAD_ID: 49
             EVENT_ID: 87
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140393236938776
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 30

插入值为(29, 29); (31, 29);的okay.
如果b是unique索引(与索引一致.)

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:1064:140393245168136
ENGINE_TRANSACTION_ID: 19554
            THREAD_ID: 49
             EVENT_ID: 102
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140393245168136
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140392970423376:7:4:5:140393236938776
ENGINE_TRANSACTION_ID: 19554
            THREAD_ID: 49
             EVENT_ID: 102
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tt
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140393236938776
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 30
2 rows in set (0.01 sec)

总结

简单总结下: (1) 单纯的select 不会加锁. 但是select for update 和 update xx where 这种都会有对应的GAP锁添加. (2) 为啥select * from tt where b = 30 for update 会锁住临近的记录呢? 我觉得这个可能是出于代码一致性的考虑. 不论是select for update或者update where这种, 为了防止幻读现象产生, 所以周围的记录被锁住了. 因为b列上不是唯一索引, 那么我们如何防止别人的update b=30呢? 直接防止别人修改完事.