• InnoDB锁机制------测试RC、RR级别下锁的处理


    1、测试RC级别不同场景下锁的处理

    主键+RC

     关闭自动提交、调整隔离级别为RC

    [root@db ~]# vim /etc/my.cnf
    transaction_isolation=read-committed
    autocommit=0
    innodb_lock_wait_timeout=3600

    重启mysql,创建表

    [root@db ~]# systemctl restart mysqld
    
    mysql> show variables like '%wait%';
    +---------------------------------------------------+----------+
    | Variable_name                                     | Value    |
    +---------------------------------------------------+----------+
    | innodb_lock_wait_timeout                          | 3600     |
    | innodb_log_wait_for_flush_spin_hwm                | 400      |
    | innodb_spin_wait_delay                            | 6        |
    | innodb_spin_wait_pause_multiplier                 | 50       |
    | lock_wait_timeout                                 | 31536000 |
    | mysqlx_wait_timeout                               | 28800    |
    | performance_schema_events_waits_history_long_size | 10000    |
    | performance_schema_events_waits_history_size      | 10       |
    | wait_timeout                                      | 28800    |
    +---------------------------------------------------+----------+
    9 rows in set (0.01 sec)
    
    mysql> select @@transaction_isolation;
    +-------------------------+
    | @@transaction_isolation |
    +-------------------------+
    | READ-COMMITTED          |
    +-------------------------+
    1 row in set (0.00 sec)
    
    #########################################################################
    mysql
    > create table t1 (id int primary key,name varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> insert into t1 values(1,'tom'),(2,'jerry'),(3,'jack'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 2 | jerry | | 3 | jack | +----+-------+ 3 rows in set (0.00 sec)

    A终端更新数据

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from t1 where id=3;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | tom   |
    |  2 | jerry |
    +----+-------+
    2 rows in set (0.00 sec)

    B终端尝试修改数据

    mysql> select * from t1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | tom   |
    |  2 | jerry |
    |  3 | jack  |
    +----+-------+
    3 rows in set (0.00 sec)
    
    mysql> update t1 set name='john' where id =3;
    
    #由于是对主键列进行的操作,会加上行锁 record lock 这时已经产生锁等待了

    查看锁情况

    mysql> select * from sys.innodb_lock_waitsG;
    *************************** 1. row ***************************
                    wait_started: 2020-07-08 15:41:09
                        wait_age: 00:09:41
                   wait_age_secs: 581                                   ## 等待的持续时间
                    locked_table: `test`.`t1`                           ## 发生锁的对象
             locked_table_schema: test                                  ## 锁发生的库
               locked_table_name: t1                                    ## 锁发生的表
          locked_table_partition: NULL
       locked_table_subpartition: NULL
                    locked_index: PRIMARY                               ## 发生锁的键类型
                     locked_type: RECORD                                ## 锁类型:表锁、行锁
                  waiting_trx_id: 17175                                 ## 发生锁的事务ID号
             waiting_trx_started: 2020-07-08 15:03:20
                 waiting_trx_age: 00:47:30
         waiting_trx_rows_locked: 1                                     ## 锁影响的行数
       waiting_trx_rows_modified: 0
                     waiting_pid: 9                                     ## 发生锁等待的线程ID,与show processlist;语句的Id相同
                   waiting_query: update t1 set name='john' where id =3  ## 发生锁等待的语句
                 waiting_lock_id: 139989104688592:146:4:4:139989023260296
               waiting_lock_mode: X,REC_NOT_GAP                         ## 锁等待的模式,行锁未加间隙锁
                 blocking_trx_id: 17174                                  ## 阻塞其他语句的事务ID
                    blocking_pid: 8                                      ## 阻塞其他语句执行的SQL线程ID,每一个客户端lian
                  blocking_query: NULL                                   ## 阻塞的语句是什么
                blocking_lock_id: 139989104687720:146:4:4:139989023254232
              blocking_lock_mode: X,REC_NOT_GAP                          ## 阻塞的模式
            blocking_trx_started: 2020-07-08 15:02:45
                blocking_trx_age: 00:48:05
        blocking_trx_rows_locked: 1
      blocking_trx_rows_modified: 1
         sql_kill_blocking_query: KILL QUERY 8                           ## 解决锁阻塞的办法 ,如果有大事务在执行的时候,不要kill
    sql_kill_blocking_connection: KILL 8
    1 row in set (0.00 sec)

    查看连接线程ID

    mysql> show processlist;
    +----+-----------------+-----------+------+---------+------+------------------------+---------------------------------------+
    | Id | User            | Host      | db   | Command | Time | State                  | Info                                  |
    +----+-----------------+-----------+------+---------+------+------------------------+---------------------------------------+
    |  4 | event_scheduler | localhost | NULL | Daemon  | 3760 | Waiting on empty queue | NULL                                  |
    |  8 | root            | localhost | test | Sleep   | 3382 |                        | NULL                                  |
    |  9 | root            | localhost | test | Query   | 1092 | updating               | update t1 set name='john' where id =3 |
    | 10 | root            | localhost | NULL | Query   |    0 | starting               | show processlist                      |
    +----+-----------------+-----------+------+---------+------+------------------------+---------------------------------------+
    4 rows in set (0.00 sec)

    查看发生阻塞的语句是什么

    1、查阻塞的SQL线程performance_schema.threads可以查看到连接线程---------->SQL线程执行的语句是什么

    mysql> select * from performance_schema.threads where PROCESSLIST_ID=8G;
    *************************** 1. row ***************************
              THREAD_ID: 47
                   NAME: thread/sql/one_connection
                   TYPE: FOREGROUND
         PROCESSLIST_ID: 8
       PROCESSLIST_USER: root
       PROCESSLIST_HOST: localhost
         PROCESSLIST_DB: test
    PROCESSLIST_COMMAND: Sleep
       PROCESSLIST_TIME: 4191
      PROCESSLIST_STATE: NULL
       PROCESSLIST_INFO: delete from t1 where id = 3         ## 这里得出的语句是不可靠的,当在A连接线程内执行sql语句select * from t1;这里就会改变
       PARENT_THREAD_ID: 1
                   ROLE: NULL
           INSTRUMENTED: YES
                HISTORY: YES
        CONNECTION_TYPE: Socket
           THREAD_OS_ID: 1839
         RESOURCE_GROUP: USR_default
    1 row in set (0.00 sec)

    2、继续查询events_statements_history查找语句

    查询结果从下至上依次进行分析找到可能发生锁定的语句

    mysql> select * from performance_schema.events_statements_history where THREAD_ID=47G;

    唯一索引+RC

    图中是id为唯一索引,name为主键,在RC隔离级别下执行delete from t1 where id =3的加锁情况如下:

    A终端创建索引,并开启事务(实例是id为主键,name为唯一键)

    mysql> alter table t1 add unique key uk_n(name);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc t1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(10) | YES  | UNI | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from t1 where name='tom';          ## 当定位这行记录的时候,会先扫描辅助索引name列,找到辅助索引列对应的聚簇索引,再回表
    Query OK, 1 row affected (0.00 sec)

    B终端执行修改辅助索引列值操作

    mysql> update t1 set id=5 where name ='tom';

    查看锁情况

    这时锁变成了辅助索引的锁,因为上面的条件是辅助索引,delete语句会加两把锁(辅助索引的条件,和回表时对聚簇索引的锁)

    mysql> select *  from sys.innodb_lock_waits G;
    *************************** 1. row ***************************
                    wait_started: 2020-07-08 19:01:42
                        wait_age: 00:05:13
                   wait_age_secs: 313
                    locked_table: `test`.`t1`
             locked_table_schema: test
               locked_table_name: t1
          locked_table_partition: NULL
       locked_table_subpartition: NULL
                    locked_index: uk_n
                     locked_type: RECORD
                  waiting_trx_id: 17210
             waiting_trx_started: 2020-07-08 18:59:40
                 waiting_trx_age: 00:07:15
         waiting_trx_rows_locked: 1
       waiting_trx_rows_modified: 0
                     waiting_pid: 13
                   waiting_query: update t1 set id=5 where name ='tom'
                 waiting_lock_id: 139989104688592:146:5:4:139989023260296
               waiting_lock_mode: X,REC_NOT_GAP
                 blocking_trx_id: 17209
                    blocking_pid: 11
                  blocking_query: NULL
                blocking_lock_id: 139989104687720:146:5:4:139989023254232
              blocking_lock_mode: X,REC_NOT_GAP
            blocking_trx_started: 2020-07-08 19:00:28
                blocking_trx_age: 00:06:27
        blocking_trx_rows_locked: 2
      blocking_trx_rows_modified: 1
         sql_kill_blocking_query: KILL QUERY 11
    sql_kill_blocking_connection: KILL 11
    1 row in set (0.00 sec)

    如果将条件换成聚簇索引的其他行,就不会阻塞

    ## 阻塞
    mysql> update t1 set name='john' where id=1;
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
     
    ## 不阻塞
    mysql> update t1 set name='john' where id=3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    非唯一索引+RC

    相对于前两种来说,name列的约束降低了,不再是唯一索引,而是普通索引

    mysql> alter table t1 drop index uk_n;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table t1 add index idx(name);
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 1
    
    mysql> desc t1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(10) | YES  | MUL | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    
    mysql> insert into t1 values(4,'jerry'),(7,'jerry');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from t1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  3 | jack  |
    |  2 | jerry |
    |  4 | jerry |
    |  7 | jerry |
    |  1 | tom   |
    +----+-------+
    5 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from t1 where name='jerry';
    Query OK, 2 rows affected (0.00 sec)

    这时的B终端加锁情况

    ## 对于
    mysql> update t1 set id=10 where name='jerry'; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted
    mysql
    > update t1 set name='lily' where id=7; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted

    ## 对于其他聚簇索引记录的操作不会阻塞
    mysql
    > update t1 set name='lily' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

    如果name列为非唯一索引,那么所有满足条件的记录都会被加锁。同时,这些记录对应的聚簇索引记录也会被加锁,本例会加6把锁,3个主键锁,3个辅助索引锁

    无索引+RC

    mysql> alter table t1 drop index idx;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    mysql> delete from t1 where name='jerry';
    Query OK, 2 rows affected (0.00 sec)

    B终端的加锁情况

    ## 当主键值满足条件时,加锁
    mysql> update t1 set id=5 where name='jerry';
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    
    ## 不满足条件的会加锁后马上释放锁
    mysql> update t1 set id=5 where name='jack';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    若 name 列上没有索引,SQL 会走聚簇索引的全扫描进行过滤,由于过滤是由 MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上 X 锁。

    但是,为了效率考量,MySQL 做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略 

    2、测试RR级别不同场景下锁的处理

    主键+RR

    id 列是主键列,Repeatable Read 隔离级别,针对 delete from t1 where id = 10; 这条SQL,加锁与组合一:[id 主键,Read Committed]一致

    唯一索引+RR

    与组合五类似,组合六的加锁,与组合二:[id 唯一索引,Read Committed]一致。两个 X 锁,id 唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个。
    注:
    id 为唯一索引,针对 id 的并发等值删除操作,有可能会产生死锁。

    非唯一索引+RR

    Repeatable Read 隔离级别,id 上有一个非唯一索引,执行 delete from t1 where id =10; 假设选择 id 列上的索引进行条件过滤,最后的加锁行为,如下图:

    1、对于辅助索引列为非数字列的情况 

    修改隔离级别

    [root@db ~]# vim /etc/my.cnf 
    transaction_isolation=repeatable-read
    autocommit=0
    innodb_lock_wait_timeout=3600
    
    [root@db ~]# /etc/init.d/mysqld restart
    Shutting down MySQL.... SUCCESS! 
    Starting MySQL... SUCCESS! 
    mysql> select @@transaction_isolation;
    +-------------------------+
    | @@transaction_isolation |
    +-------------------------+
    | REPEATABLE-READ         |
    +-------------------------+
    1 row in set (0.01 sec)

    创建表

    mysql> create table t2(id int primary key ,name varchar(10));
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> insert into t2 values(2,'a'),(5,'f'),(6,'c'),(11,'k'),(18,'o'),(21,'f'),(19,'f');
    Query OK, 5 rows affected (0.01 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> alter table t2 add index idx_n(name);
    Query OK, 0 rows affected (7.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from t2;
    +----+------+
    | id | name |
    +----+------+
    |  2 | a    |
    |  6 | c    |
    |  5 | f    |
    | 19 | f    |
    | 21 | f    |
    | 11 | k    |
    | 18 | o    |
    +----+------+
    7 rows in set (0.00 sec)

    A终端执行delete语句后,B终端查看锁情况

    mysql> delete from t2 where name='f';
    Query OK, 3 rows affected (0.00 sec)
    ## B终端执行insert
    mysql> insert into t2 values('7','c'); ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted
    mysql> select *  from sys.innodb_lock_waits G;
    *************************** 1. row ***************************
                    wait_started: 2020-07-09 00:17:02
                        wait_age: 00:00:06
                   wait_age_secs: 6
                    locked_table: `test`.`t2`
             locked_table_schema: test
               locked_table_name: t2
          locked_table_partition: NULL
       locked_table_subpartition: NULL
                    locked_index: idx_n
                     locked_type: RECORD
                  waiting_trx_id: 17750
             waiting_trx_started: 2020-07-09 00:15:05
                 waiting_trx_age: 00:02:03
         waiting_trx_rows_locked: 2
       waiting_trx_rows_modified: 1
                     waiting_pid: 12
                   waiting_query: insert into t2 values('7','c')
                 waiting_lock_id: 140459079454160:147:5:3:140458986635232
               waiting_lock_mode: X,GAP,INSERT_INTENTION
                 blocking_trx_id: 17745
                    blocking_pid: 13
                  blocking_query: NULL
                blocking_lock_id: 140459079453288:147:5:3:140458986628824
              blocking_lock_mode: X
            blocking_trx_started: 2020-07-09 00:13:46
                blocking_trx_age: 00:03:22
        blocking_trx_rows_locked: 7
      blocking_trx_rows_modified: 3
         sql_kill_blocking_query: KILL QUERY 13
    sql_kill_blocking_connection: KILL 13
    1 row in set (0.00 sec)

    该例子中间隙锁产生会在如下区间

    从(负无穷,2),(2,5),(5,6),(6,11),(11,18),(18,19),(19,21)

    2、对于辅助索引列为数字的情况

    mysql> create table t3 (id int primary key,num int);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> alter table t3 add index k_n(num);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> insert into t3 values(1,2),(3,4),(5,6),(8,7),(10,7),(11,7),(12,9);
    Query OK, 7 rows affected (0.00 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from t3 where num=7 for update ;
    +----+------+
    | id | num  |
    +----+------+
    |  8 |    7 |
    | 10 |    7 |
    | 11 |    7 |
    +----+------+
    3 rows in set (0.00 sec)

    B终端测试

    mysql> select * from t3;
    +----+------+
    | id | num  |
    +----+------+
    |  1 |    2 |
    |  3 |    4 |
    |  5 |    6 |
    |  8 |    7 |
    | 10 |    7 |
    | 11 |    7 |
    | 12 |    9 |
    +----+------+
    7 rows in set (0.00 sec)
    mysql> insert into t3 value(15,8);
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    mysql
    > insert into t3 value(7,8); ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> insert into t3 value(15,6); ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted
    mysql> insert into t3 value(16,5); Query OK, 1 row affected (0.00 sec) mysql> insert into t3 value(7,5); Query OK, 1 row affected (0.00 sec) mysql> insert into t3 value(9,6); ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted

      mysql> insert into t3 value(9,9);
      ^C^C -- query aborted
      ERROR 1317 (70100): Query execution was interrupted

    当num取值为7的时候,会向左取到6为左区间,向右取到9为右区间,这样区间的访问就是[6,9],做update的时候有下面的情况

    mysql> update t3 set id=6 where num=6;
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    mysql
    > update t3 set id=4 where num=6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

    上面的阻塞式因为(id=6,num=6)是在辅助索引范围内的(即(id=5,num=6)与(id=8,num-7)之间)

    辅助索引为范围的情况

    mysql> select * from t3;
    +----+------+
    | id | num  |
    +----+------+
    |  1 |    2 |
    |  3 |    4 |
    |  5 |    6 |
    |  8 |    7 |
    | 11 |    7 |
    | 12 |    9 |
    +----+------+
    6 rows in set (0.00 sec)
    mysql> select * from t3 where num > 7 for update;
    +----+------+
    | id | num  |
    +----+------+
    | 12 |    9 |
    +----+------+
    1 row in set (0.00 sec)

    B终端测试

    mysql> update t3 set id = 10 where num=9;
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    
    mysql> insert into t3 values(10,7);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into t3 values(13,7);
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    
    
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into t3 values(10,8);
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted
    
    mysql> insert into t3 values(10,9);
    ^C^C -- query aborted
    ERROR 1317 (70100): Query execution was interrupted

    这时由于辅助索引范围是num > 4,所以左区间取值为4,右区间是正无穷

    总结 

    Innodb下的记录锁record lock(也叫行锁raw lock),间隙锁(GAP),next-key锁统统属于排他锁(Mutex lock)

    record_lock + GAP = next-key lock

    行锁

    对表中的每一行记录施加的锁,就是行锁

    间隙锁

    生活中排队的场景,小明,小红,小花三个人依次站成一排,此时,如何让新来的小刚不能站在小红旁边,这时候只要将小红和她前面的小明之间的空隙封锁,将小红和她后面的小花之间的空隙封锁,那么小刚就不能站到小红的旁边。
    这里的小红,小明,小花,小刚就是数据库的一条条记录。
    他们之间的空隙也就是间隙,而封锁他们之间距离的锁,叫做间隙锁。

    间隙锁的作用

    防止幻读:
    (1)防止间隙内有新数据被插入
    (2)防止已存在的数据,更新成间隙内的数据

    间隙锁的使用条件

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

    next-key锁

    行锁和间隙锁同时存在的时候,就是下一键锁
  • 相关阅读:
    数据结构排序算法之堆排序
    整数划分
    二叉树的递归与非递归
    螺旋数组
    【图的DFS】图的DFS非递归算法
    Docker实战部署应用——Tomcat
    Docker实战部署应用——Redis
    Docker实战部署应用——MySQL5.7
    Docker其他操作:查看内部细节、IP、删除容器
    Docker数据目录相关操作
  • 原文地址:https://www.cnblogs.com/zh-dream/p/13276687.html
Copyright © 2020-2023  润新知