暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL锁都分不清，怎么面试进大厂？

MySQL锁都分不清，怎么面试进大厂？

腾讯云数据库 2020-09-15
930

作者简介：肖泽凡，腾讯TEG研发管理部小小后台攻城狮一枚，负责腾讯敏捷产品研发平台TAPD的基础功能的开发和维护，热爱技术，喜欢分享，文章首次发表于SegmentFault，博客名“X先生”，欢迎与我交流~


锁是为了解决并发环境下资源竞争的手段，其中乐观并发控制，悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段，而MySQL中的锁就是其中的悲观并发控制。


MySQL中的锁有很多种类，我们可以按照下面方式来进行分类。



一、按读写



从数据库的读写的角度来分，数据库的锁可以分为分为以下几种：

一、独占锁

又称排它锁、X锁、写锁。X锁不能和其他锁兼容，只要有事务对数据上加了任何锁，其他事务就不能对这些数据再放置X了，同时某个事务放置了X锁之后，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。

二、共享锁

又称读锁、S锁。S锁与S锁兼容，可以同时放置。

三、更新锁

又称U锁。它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。注意，MySQL并不支持U锁，SQLServer才支持U锁。

兼容性矩阵如下(+ 代表兼容， -代表不兼容)

右侧是已加的锁
X
S
U
X
-
-
-
S
-
+
+
U
-
+
-


二、按粒度



MySQL支持不同级别的锁，其锁定的数据的范围也不同，也即我们常说的锁的粒度。MySQL有三种锁级别：行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度，例如MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

一、特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行锁总是逐步获得的，因此会出现死锁。

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

下面详细介绍行锁和表锁，页锁由于使用得较少就不介绍了。

二、行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。

MySQL的行锁对S、X锁上做了一些更精确的细分，使得行锁的粒度更细小，可以减少冲突，这就是被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysqllock0lock.c:lock_rec_has_to_wait源代码推测出来的。）

行锁兼容矩阵

1. 间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。

2. 记录锁(Record Lock)：只锁记录，特定几行记录。

3. 临键锁(Next-KeyLock)：同时锁住记录和间隙，前开后闭区间(a,b]。

4. 插入意图锁(InsertIntention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

右侧是已加的锁(+ 代表兼容， -代表不兼容)
G
R
N
I
G
+
+
+
+
R
+
–
–
+
N
+
–
–
+
I
–
+
–
+

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。精确模式的检测，用在S、X和X、X之间。从这个矩阵可以看到几个特点：

1. INSERT操作之间不会有冲突：你插入你的，我插入我的。

2. GAP，Next-Key会阻止Insert：插入的数据正好在区间内，不允许插入。

3. GAP和Record，Next-Key不会冲突。

4. Record和Record、Next-Key之间相互冲突。

5. 已有的Insert锁不阻止任何准备加的锁。

6. 间隙锁（无论是S还是X）只会阻塞insert操作。

注意点

1. 对于记录锁，列必须是唯一索引列或者主键列，查询语句必须为精确匹配，如“=”，否则记录锁会退化为临键锁。

2. 间隙锁和临键锁基于非唯一索引，在唯一索引列上不存在间隙锁和临键锁。

表锁与锁表的误区

只有正确通过索引条件检索数据（没有索引失效的情况），InnoDB才会使用行级锁，否则InnoDB对表中的所有记录加锁，也就是将锁住整个表。注意，这里说的是锁住整个表，但是Innodb并不是使用表锁来锁住表的，而是使用了下面介绍的Next-Key Lock来锁住整个表。网上很多的说法都是说用表锁，然而实际上并不是，我们可以通过下面的例子来看看。

假设我们有以下的数据(MySQL8)：

    mysql>select *from users;
    +----+------+-----+
    |id|name | age |
    +----+------+-----+
    |  1| a    |1   |
    |  2| a    |1   |
    |  3| a    |1   |
    |  4| a    |1   |
    |  5| a    |1   |
    +----+------+-----+


    方法一：

    我们使用表锁锁表，并查看引擎的状态

      mysql> begin;
      Query OK, 0 rows affected (0.00sec)


      mysql> lock tables userswrite;
      Query OK, 0 rows affected (0.00sec)


      mysql>  show engine innodb status\G
      ...
      ------------
      TRANSACTIONS
      ------------
      Trx id counter 4863
      Purge donefortrx's n:o <4862undo n:o <0state: running but idle
      History list length 911
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 281479760456232,not started
      mysql tables in use 1, locked 1   ###############注意这里
      0lock struct(s),heap size 1136, 0 row lock(s)
      ...


      然后我们再通过非索引的字段查询来加锁，并查看引擎的状态

        ## 先解锁上次的表锁
        mysql> unlock tables;
        Query OK, 0 rows affected (0.00sec)


        mysql> commit;
        Query OK, 0 rows affected (0.00sec)


        mysql> begin;
        Query OK, 0 rows affected (0.00sec)


        mysql>select *from users where name ='a'for update;


        mysql>  show engine innodb status\G
        ...
        ------------
        TRANSACTIONS
        ------------
        Trx id counter 4864
        Purge donefor trx'sn:o <4862undo n:o <0state: running but idle
        History list length 911
        LIST OF TRANSACTIONS FOR EACH SESSION:
        ---TRANSACTION 4863,ACTIVE 37 sec
        2lock struct(s),heap size 1136, 6 row lock(s)    ###############注意这里
        ...


        然后我们再删除id为2，3，4的数据，然后在通过非索引的字段查询来加锁，并查看引擎的状态

          mysql> delete from users where idin(2,3,4);
          Query OK, 3 rows affected (0.00sec)


          mysql> commit;
          Query OK, 0 rows affected (0.00sec)


          mysql> begin;
          Query OK, 0 rows affected (0.00sec)


          mysql>select *from users where name ='a'for update;


          mysql>  show engine innodb status\G
          ...
          ------------
          TRANSACTIONS
          ------------
          Trx id counter 4870
          Purge donefortrx's n:o <4869undo n:o <0state: running but idle
          History list length 914
          LIST OF TRANSACTIONS FOR EACH SESSION:
          ---TRANSACTION 4869,ACTIVE 9 sec
          2lock struct(s),heap size 1136, 3 row lock(s)   ###############注意这里
          ...


          可以看到这里使用了表锁和因为没法用索引锁定特定行而转而锁住整个表是不一样的。从第二次和第三次的操作来看，lock住的row也是不同的，这是因为两者间隙的个数不同，所以可以看到使用的并不是表锁，而是Next-Key Lock。第一次锁住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞]，第二次锁住了(-∞,1],(1,5],(5,∞]。

          方法二：

          也可以通过以下语句来查看锁的信息，也可以知道用的是行锁，且是锁住了区间（插入不了数据）和记录，所以是Next-Key Lock。

            mysql> selectENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_lockswhere ENGINE_TRANSACTION_ID in (你的事务id);
            +-----------------------+-----------+-----------+
            | ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
            +-----------------------+-----------+-----------+
            |                  4889 |TABLE     | IX        |
            |                  4889 |RECORD    | X         |
            |                  4889 |RECORD    | X         |
            |                  4889 |RECORD    | X         |
            +-----------------------+-----------+-----------+
            10 rows in set (0.00 sec)


            LOCK_TYPE：对于InnoDB，可选值为 RECORD(行锁)， TABLE(表锁)

            LOCK_MODE：对于InnoDB，可选值为S[,GAP], X[,GAP],IS[,GAP],IX[,GAP], AUTO_INC和UNKNOWN。除了AUTO_INC和UNKNOWN，其他锁定模式都包含了GAP锁（如果存在）。

            具体可见 MySQL文档：
            https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

            三、表级锁

            直接对整个表加锁，影响表中所有记录，表读锁和表写锁的兼容性见上面的分析。

            MySQL中除了表读锁和表写锁之外，还存在一种特殊的表锁：意向锁，这是为了解决不同粒度的锁的兼容性判断而存在的。

            意向锁

            因为锁的粒度不同，表锁的范围覆盖了行锁的范围，所以表锁和行锁会产生冲突，例如事务A对表中某一行数据加了行锁，然后事务B想加表锁，正常来说是应该要冲突的。如果只有行锁的话，要判断是否冲突就得遍历每一行数据了，这样的效率实在不高，因此我们就有了意向表锁。

            意向锁的主要目的是为了使得 行锁 和 表锁 共存，事务在申请行锁前，必须先申请表的意向锁，成功后再申请行锁。注意：申请意向锁的动作是数据库完成的，不需要开发者来申请。

            意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表， 所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。

            意向锁分为意向读锁(IS)和意向写锁(IX)。

            表锁的兼容性矩阵

            右侧是已加的锁(+ 代表兼容， -代表不兼容)
            IS
            IX
            S
            X
            IS
            +
            +
            +
            –
            IX
            +
            +
            –
            –
            S
            +
            –
            +
            –
            X
            –
            –
            –
            –


            三、参考资料



            https://www.cnblogs.com/rjzheng/p/9950951.html
            https://dev.mysql.com/doc/refman/8.0/en/
             

            特惠体验云数据库 



            ↓↓更多惊喜优惠请点这儿~ 
            mysql面试tencentdb
            文章转载自腾讯云数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

            评论