InnoDB锁机制------测试RC、RR级别下锁的处理 1、测试RC级别不同场景下锁的处理 2、测试RR级别不同场景下锁的处理 总结
主键+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锁
行锁和间隙锁同时存在的时候，就是下一键锁