全部关于锁文章
- 全局读锁https://cloud.tencent.com/developer/article/1869375
- 表锁 https://cloud.tencent.com/developer/article/1869546
- MDLhttps://cloud.tencent.com/developer/article/1869667
- MySQL找出谁持有行锁（RR级别）https://cloud.tencent.com/developer/article/1869793
- MySQL找出谁持有行锁（RC级别）https://cloud.tencent.com/developer/article/1869900
提到行锁，不得不提事务，不得不提事物隔离级别
事务隔离级别：
一个事务所做的修改，对其他事务是不可见的，好似是串行执行的。多个事务并行执行的，好似他是串行执行的，事务并发执行，但是效果和串行效果一致，一个事务所做的修改对其他事务是不可见的，好似是串行执行的。
不符合隔离性就会存在三个问题：脏读、幻读、不可重复读。或者换句话说隔离级别就是解决脏读、幻读、不可重复读.。MySQL下加锁都是对索引进行加锁。
ANSI（美国国家标准学会 AMERICAN NATIONAL STANDARDS INSTITUTE: ANSI）推出的标准只有serializable符合隔离性要求，如下：
innodb下实现如下图
innodb下RU(read-uncommitted) 什么都没解决，RR、SR 都解决了，这就是为什么MySQL在默认事务隔离下达到隔离性要求，但是锁比较重，然而RR比serializable要好，下面我们看下RC和RR级别下，锁的粒度，我们以MySQL8.0为参考。
RR级别行锁
where列无索引无主键
代码语言：javascript复制sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id2 = 9;
mysql> select * from performance_schema.data_locks;
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
| INNODB | 140023602396568:1090:140023517226320 | 174531 | 192 | 26 | test | t | NULL | NULL | NULL | 140023517226320 | TABLE | IX | GRANTED | NULL |
| INNODB | 140023602396568:29:4:1:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 140023602396568:29:4:2:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000301 |
| INNODB | 140023602396568:29:4:3:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000302 |
| INNODB | 140023602396568:29:4:4:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000303 |
| INNODB | 140023602396568:29:4:5:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000304 |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
6 rows in set (0.18 sec)会话2执行
代码语言：javascript复制sess2:下面的情况插入8 10 22 都是一个下面的结果
begin;
insert into t values(8,8,now());
insert into t values(10,10,now());
insert into t values(22,22,now());
都被阻塞会话3执行
代码语言：javascript复制--sys.innodb_lock_waits MySQL5.7也可以使用
mysql> select wait_started,wait_age_secs,locked_table,locked_index,locked_type,waiting_lock_mode,blocking_lock_mode from sys.innodb_lock_waits;
--------------------- --------------- -------------- ----------------- ------------- -------------------- --------------------
| wait_started | wait_age_secs | locked_table | locked_index | locked_type | waiting_lock_mode | blocking_lock_mode |
--------------------- --------------- -------------- ----------------- ------------- -------------------- --------------------
| 2021-08-30 13:40:59 | 45 | `test`.`t` | GEN_CLUST_INDEX | RECORD | X,INSERT_INTENTION | X |
--------------------- --------------- -------------- ----------------- ------------- -------------------- --------------------
1 row in set (0.22 sec)
mysql> select * from performance_schema.data_locks;
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
| INNODB | 140023602397424:1090:140023517232480 | 174536 | 193 | 16 | test | t | NULL | NULL | NULL | 140023517232480 | TABLE | IX | GRANTED | NULL |
| INNODB | 140023602396568:1090:140023517226320 | 174531 | 192 | 26 | test | t | NULL | NULL | NULL | 140023517226320 | TABLE | IX | GRANTED | NULL |
| INNODB | 140023602396568:29:4:1:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 140023602396568:29:4:2:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000301 |
| INNODB | 140023602396568:29:4:3:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000302 |
| INNODB | 140023602396568:29:4:4:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000303 |
| INNODB | 140023602396568:29:4:5:140023517223216 | 174531 | 192 | 26 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140023517223216 | RECORD | X | GRANTED | 0x000000000304 |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ----------------- ----------------------- ----------- ----------- ------------- ------------------------
7 rows in set (0.00 sec)会话3使用关联脚本查询
代码语言：javascript复制-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
select distinct c.THREAD_ID,
x.sql_kill_blocking_connection as kill_command,
x.blocking_lock_mode,
x.waiting_lock_mode,
c.event_name,
c.sql_text as blocking_sql_text,
x.waiting_query as blocked_sql_text,
c.CURRENT_SCHEMA,
c.OBJECT_NAME,
DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'UPTIME') -
c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
x.wait_age_secs as blocked_waiting_seconds,
x.locked_table,
x.locked_index,
x.locked_type
from performance_schema.events_statements_history c
inner join (
select t.THREAD_ID,
ilw.sql_kill_blocking_connection,
ilw.waiting_lock_mode,
ilw.blocking_lock_mode,
ilw.wait_age_secs,
ilw.locked_table,
ilw.waiting_query,
ilw.locked_index,
ilw.locked_type
from sys.innodb_lock_waits ilw
inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;
----------- -------------- -------------------- -------------------- ---------------------------- ------------------------------------------------ ----------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- ----------------- -------------
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode | event_name | blocking_sql_text | blocked_sql_text | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index | locked_type |
----------- -------------- -------------------- -------------------- ---------------------------- ------------------------------------------------ ----------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- ----------------- -------------
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/com/Field List | NULL | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:06.485359 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/drop_table | drop table t | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.335545 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6)) | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.527888 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/insert | insert into t values(1,1,now()) | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.674543 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/insert | insert into t values(2,5,now()) | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.692373 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/insert | insert into t values(5,12,now()) | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.704618 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/insert | insert into t values(10,13,now()) | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.714441 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/begin | begin | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.746770 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/update | update t set t = now() where id2 = 9 | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:07.747276 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
| 192 | KILL 149 | X | X,INSERT_INTENTION | statement/sql/select | select * from performance_schema.data_locks | insert into t values(10,10,now()) | test | NULL | 2021-08-30 13:39:09.834606 | 1 | `test`.`t` | GEN_CLUST_INDEX | RECORD |
----------- -------------- -------------------- -------------------- ---------------------------- ------------------------------------------------ ----------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- ----------------- -------------
10 rows in set (0.00 sec)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
where列 为普通索引列
代码语言：javascript复制sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6) , key(id2));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id2 = 9;
select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_locks;
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- --------------------
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- --------------------
| INNODB | 140023602396568:1091:140023517226320 | 174562 | 192 | 43 | test | t | NULL | NULL | NULL | 140023517226320 | TABLE | IX | GRANTED | NULL |
| INNODB | 140023602396568:30:5:4:140023517223216 | 174562 | 192 | 43 | test | t | NULL | NULL | id2 | 140023517223216 | RECORD | X,GAP | GRANTED | 12, 0x00000000030B |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- --------------------
2 rows in set (0.00 sec)会话2
代码语言：javascript复制sess2:
begin;
insert into t values(8,8,now());
insert into t values(10,10,now());
会被阻塞
insert into t values(22,22,now());
不会被阻塞会话3查询到的阻塞信息如下
代码语言：javascript复制-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
select distinct c.THREAD_ID,
x.sql_kill_blocking_connection as kill_command,
x.blocking_lock_mode,
x.waiting_lock_mode,
c.event_name,
c.sql_text as blocking_sql_text,
x.waiting_query as blocked_sql_text,
c.CURRENT_SCHEMA,
c.OBJECT_NAME,
DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'UPTIME') -
c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
x.wait_age_secs as blocked_waiting_seconds,
x.locked_table,
x.locked_index,
x.locked_type
from performance_schema.events_statements_history c
inner join (
select t.THREAD_ID,
ilw.sql_kill_blocking_connection,
ilw.waiting_lock_mode,
ilw.blocking_lock_mode,
ilw.wait_age_secs,
ilw.locked_table,
ilw.waiting_query,
ilw.locked_index,
ilw.locked_type
from sys.innodb_lock_waits ilw
inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;
----------- -------------- -------------------- ------------------------ ---------------------------- ----------------------------------------------------------- --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode | event_name | blocking_sql_text | blocked_sql_text | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index | locked_type |
----------- -------------- -------------------- ------------------------ ---------------------------- ----------------------------------------------------------- --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/rollback | rollback | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:41.780473 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/drop_table | drop table t | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:42.974121 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6) , key(id2)) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.766664 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(1,1,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.919023 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(2,5,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.954370 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(5,12,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.970091 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(10,13,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.982245 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/begin | begin | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.997404 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/update | update t set t = now() where id2 = 9 | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:58.997906 | 47 | `test`.`t` | id2 | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/select | select * from performance_schema.data_locks | insert into t values(8,8,now()) | test | NULL | 2021-08-30 13:49:59.001964 | 47 | `test`.`t` | id2 | RECORD |
----------- -------------- -------------------- ------------------------ ---------------------------- ----------------------------------------------------------- --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
10 rows in set (0.04 sec)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
where列为主键或者唯一索引
代码语言：javascript复制sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6) , primary key(id));
insert into t values(1,1,now());
insert into t values(2,5,now());
insert into t values(5,12,now());
insert into t values(10,13,now());
begin;
update t set t = now() where id = 9;
select * from performance_schema.data_locks;
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- -----------
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- -----------
| INNODB | 140023602396568:1092:140023517226320 | 174607 | 192 | 76 | test | t | NULL | NULL | NULL | 140023517226320 | TABLE | IX | GRANTED | NULL |
| INNODB | 140023602396568:31:4:5:140023517223216 | 174607 | 192 | 76 | test | t | NULL | NULL | PRIMARY | 140023517223216 | RECORD | X,GAP | GRANTED | 10 |
-------- ---------------------------------------- ----------------------- ----------- ---------- --------------- ------------- ---------------- ------------------- ------------ ----------------------- ----------- ----------- ------------- -----------
2 rows in set (0.00 sec)会话2
代码语言：javascript复制sess2:
begin;
insert into t values(8,8,now());
会被阻塞
mysql> insert into t values(11,11,now());
Query OK, 1 row affected (0.00 sec)
11不会被阻塞
阻塞时候，会话3信息
代码语言：javascript复制-- 默认开启performance_schema的情况，5.7和8.0都能用。
select /*default performance_schema level 5.7 and 8.0 both can*/ *
from (
select distinct c.THREAD_ID,
x.sql_kill_blocking_connection as kill_command,
x.blocking_lock_mode,
x.waiting_lock_mode,
c.event_name,
c.sql_text as blocking_sql_text,
x.waiting_query as blocked_sql_text,
c.CURRENT_SCHEMA,
c.OBJECT_NAME,
DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'UPTIME') -
c.TIMER_START / 1000 / 1000 / 1000 / 1000 second) AS 'blocking_session_sql_start_time',
x.wait_age_secs as blocked_waiting_seconds,
x.locked_table,
x.locked_index,
x.locked_type
from performance_schema.events_statements_history c
inner join (
select t.THREAD_ID,
ilw.sql_kill_blocking_connection,
ilw.waiting_lock_mode,
ilw.blocking_lock_mode,
ilw.wait_age_secs,
ilw.locked_table,
ilw.waiting_query,
ilw.locked_index,
ilw.locked_type
from sys.innodb_lock_waits ilw
inner join performance_schema.threads t on t.PROCESSLIST_ID = ilw.blocking_pid) x
on x.THREAD_ID = c.THREAD_ID) xx
order by xx.blocking_session_sql_start_time;
----------- -------------- -------------------- ------------------------ ---------------------------- ------------------------------------------------------------------ --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
| THREAD_ID | kill_command | blocking_lock_mode | waiting_lock_mode | event_name | blocking_sql_text | blocked_sql_text | CURRENT_SCHEMA | OBJECT_NAME | blocking_session_sql_start_time | blocked_waiting_seconds | locked_table | locked_index | locked_type |
----------- -------------- -------------------- ------------------------ ---------------------------- ------------------------------------------------------------------ --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/drop_table | drop table t | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.578418 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6) , primary key(id)) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.704013 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(1,1,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.891387 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(2,5,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.922129 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(5,12,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.933297 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/insert | insert into t values(10,13,now()) | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.944569 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/begin | begin | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.980070 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/update | update t set t = now() where id = 9 | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:20.980521 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/select | select * from performance_schema.data_locks | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:00:21.346885 | 5 | `test`.`t` | PRIMARY | RECORD |
| 192 | KILL 149 | X,GAP | X,GAP,INSERT_INTENTION | statement/sql/select | select * from t | insert into t values(8,8,now()) | test | NULL | 2021-08-30 14:02:26.835415 | 5 | `test`.`t` | PRIMARY | RECORD |
----------- -------------- -------------------- ------------------------ ---------------------------- ------------------------------------------------------------------ --------------------------------- ---------------- ------------- --------------------------------- ------------------------- -------------- -------------- -------------
10 rows in set (0.03 sec)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
总结
在表中加入如下数据，
- 无索引的情况，表中记录都被锁定了
- 普通索引，锁定了表中含有的记录且小于9的值，并且包含了大于9的那个最小值（是不是有点绕=_=//）
- 更新的是主键值的话，与二级索引一样，锁定范围为表中有的值且小于9，和表中大于9的那个最小值。
- 主键更新，加锁的区间为(-,1],(1,2],(2,5],(5,9] 四个区间
- RC级别我们后续再看
更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码
作者：姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。
