专栏首页MySQL故障优化案例MySQL Cases-MySQL找出谁持有行锁(RR)
原创

MySQL Cases-MySQL找出谁持有行锁(RR)

全部关于锁文章

提到行锁,不得不提事务,不得不提事物隔离级别

事务隔离级别:

一个事务所做的修改,对其他事务是不可见的,好似是串行执行的。多个事务并行执行的,好似他是串行执行的,事务并发执行,但是效果和串行效果一致,一个事务所做的修改对其他事务是不可见的,好似是串行执行的。

不符合隔离性就会存在三个问题:脏读、幻读、不可重复读。或者换句话说隔离级别就是解决脏读、幻读、不可重复读.。MySQL下加锁都是对索引进行加锁。

ANSI(美国国家标准学会 AMERICAN NATIONAL STANDARDS INSTITUTE: ANSI)推出的标准只有serializable符合隔离性要求,如下:

innodb下实现如下图

innodb下RU(read-uncommitted) 什么都没解决,RR、SR 都解决了,这就是为什么MySQL在默认事务隔离下达到隔离性要求,但是锁比较重,然而RR比serializable要好,下面我们看下RC和RR级别下,锁的粒度,我们以MySQL8.0为参考。

RR级别行锁

where列无索引无主键

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执行

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执行

--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使用关联脚本查询

-- 默认开启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)
  1. IX:代表意向排他锁表锁。
  2. X:代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
  3. S:代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
  4. X,REC_NOT_GAP:代表只锁定记录本身(X)。
  5. S,REC_NOT_GAP:代表只锁定记录本身(S)。
  6. X,GAP:代表间隙锁,不锁定记录本身(X)。
  7. X,GAP:代表间隙锁,不锁定记录本身(S)。
  8. X,GAP,INSERT_INTENTION:代表插入意向锁。

where列 为普通索引列

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

sess2:
begin;
insert into t values(8,8,now());
insert into t values(10,10,now());
会被阻塞
insert into t values(22,22,now());
不会被阻塞

会话3查询到的阻塞信息如下

-- 默认开启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)
  1. IX:代表意向排他锁表锁。
  2. X:代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
  3. S:代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
  4. X,REC_NOT_GAP:代表只锁定记录本身(X)。
  5. S,REC_NOT_GAP:代表只锁定记录本身(S)。
  6. X,GAP:代表间隙锁,不锁定记录本身(X)。
  7. X,GAP:代表间隙锁,不锁定记录本身(S)。
  8. X,GAP,INSERT_INTENTION:代表插入意向锁。

where列为主键或者唯一索引

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

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信息

-- 默认开启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)
  1. IX:代表意向排他锁表锁。
  2. X:代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
  3. S:代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
  4. X,REC_NOT_GAP:代表只锁定记录本身(X)。
  5. S,REC_NOT_GAP:代表只锁定记录本身(S)。
  6. X,GAP:代表间隙锁,不锁定记录本身(X)。
  7. X,GAP:代表间隙锁,不锁定记录本身(S)。
  8. 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数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

关注作者,阅读全部精彩内容

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL Cases-MySQL找出谁持有行锁(RC)

    承接上文RR级别下的锁粒度,这篇文章看下RC模式下有哪些特点,首先说下RC解决了什么问题。

    姚崇
  • MySQL Cases-MySQL找出谁持有表锁

    表级锁对应的instruments(wait/lock/table/sql/handler)默认已开启,对应的consumers为performance_sch...

    姚崇
  • MySQL Cases-MySQL找出谁持有表锁之MDL锁

    另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一...

    姚崇
  • MySQL Cases-MySQL找出谁持有全局读锁

    全局读锁通常是由flush table with read lock;这类语句添加的。在各种备份工具为了得到一致性备份,已经在具备主从复制架构的环境中做主备切换...

    姚崇
  • [精选]详细介绍MySQL中常见的锁

    当数据库的隔离级别为Repeatable Read或Serializable时,我们来看这样的两个并发事务(场景一):

    码农编程进阶笔记
  • 小白学习MySQL - 查询会锁表?

    我们知道,Oracle中除了使用select ... for update,其他查询语句不会出现锁,即没有读锁,读一致性通过多版本解决的,可以保证在不加锁的情况...

    bisal
  • MySql 三大知识点——索引、锁、事务

    索引的优点:1. 天生排序。2. 快速查找。 索引的缺点:1. 占用空间。2. 降低更新表的速度。

    Bug开发工程师
  • 深入理解Mysql——锁、事务与并发控制

    本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖!

    Java团长
  • 深入理解 MySQL ——锁、事务与并发控制

    每个连接都会在 MySQL 服务端产生一个线程(内部通过线程池管理线程),比如一个 select 语句进入,MySQL 首先会在查询缓存中查找是否缓存了这个 s...

    杨振涛
  • 深入理解 MySQL—锁、事务与并发控制

    每个连接都会在 MySQL 服务端产生一个线程(内部通过线程池管理线程),比如一个 select 语句进入,MySQL 首先会在查询缓存中查找是否缓存了这个 s...

    数据和云
  • 死锁案例之一

    一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想...

    用户1278550
  • 深入理解 MySQL ——锁、事务与并发控制

    每个连接都会在 MySQL 服务端产生一个线程(内部通过线程池管理线程),比如一个 select 语句进入,MySQL 首先会在查询缓存中查找是否缓存了这个 s...

    2020labs小助手
  • MySQL 加锁处理分析

    背景 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信...

    java达人
  • 技术分享 | MySQL中查询会锁表 ?

    网名 bisal ,具有十年以上的应用运维工作经验,目前主要从事数据库应用研发能力提升方面的工作,Oracle ACE ,拥有 Oracle OCM & OCP...

    爱可生开源社区
  • 数据库事务系列-MySQL跨行事务模型

    说来和MySQL倒是有缘,毕业的第一份工作就被分配到了RDS团队,主要负责把MySQL弄到云上做成数据库服务。虽说整天和MySQL打交道,但说实话那段时间并没有...

    Java_老男孩
  • 划重点!你还在困惑MySQL中的"锁"吗?

    最近在学习查找MySQL中"锁"的相关资料时,发现网上各种言论观点杂乱不堪且版本混乱,很容易让人深陷其中、很是蒙圈。笔者认真研读了MySQL8.0官方指导手册,...

    luanhz
  • 【MySQL入门】之MySQL数据库的锁机制(二)

    上篇文章主要聊了全局锁和表锁,并详细分析MDL锁的作用以及可能带来的问题。今天我们主要来聊一聊Innodb存储引擎的行锁。MySQL的行锁是在引擎层由引擎自己实...

    MySQL数据库技术栈
  • 一条简单的更新语句,MySQL是如何加锁的?

    那这条语句呢?其实这其中包含太多知识点了。要回答这两个问题,首先需要了解一些知识。

    Java_老男孩
  • Mysql一分钟定位 Next-Key Lock,你需要几分钟

    查看线程模型 show variables like 'thread_handling'

    王清培

扫码关注云+社区

领取腾讯云代金券