前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
腾讯云架构师技术同盟
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP腾讯云架构师技术同盟
返回腾讯云官网
社区首页 >专栏 >MySQL Cases-MySQL找出谁持有行锁（RC）

MySQL Cases-MySQL找出谁持有行锁（RC）

原创
作者头像
姚崇
修改于 2021-09-18 11:35:10
修改于 2021-09-18 11:35:10
1.3K00
代码可运行
举报
文章被收录于专栏：MySQL故障优化案例MySQL故障优化案例
运行总次数：0
代码可运行

全部关于锁文章

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

RC解决了脏读问题，未解决幻读和可重复读，那么什么是幻读和可重复读？

可重复读

另一个事务中，重点是一个事务中，两次读取的结果不同，可见RC不满足

幻读

读到了之前不存在的记录，和不可重复读没有本质区别

RC级别行锁

where列无索引无主键

代码语言：javascript
代码运行次数：0
复制
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;
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:1094:140023517226320   |                174667 |       200 |       27 | test          | t           | NULL           | NULL              | NULL            |       140023517226320 | TABLE     | IX            | GRANTED     | NULL           |
| INNODB | 140023602396568:33:4:2:140023517223216 |                174667 |       200 |       27 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000318 |
| INNODB | 140023602396568:33:4:3:140023517223216 |                174667 |       200 |       27 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000319 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------+
3 rows in set (0.00 sec)

可以看出加表级别IX和 X,REC_NOT_GAP id2为1和5的两行记录

会话2

代码语言：javascript
代码运行次数：0
复制
begin;
insert into t values(5,5,now());
不被阻塞
begin;
update t set t = now() where id2 = 5;
被阻塞，被阻塞的只是 id2<9的有记录的值。
update t set t = now() where id2 = 12;
不被阻塞

会话3

代码语言：javascript
代码运行次数：0
复制
-- 默认开启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 |
+-----------+--------------+--------------------+-------------------+----------------------------+------------------------------------------------+--------------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+-----------------+-------------+
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/com/Field List   | NULL                                           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:27.592589      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/drop_table   | drop table t                                   | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:42.925233      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6)) | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.031261      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(1,1,now())                | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.117219      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(2,5,now())                | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.131179      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(5,12,now())               | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.139273      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(10,13,now())              | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.149285      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/begin        | begin                                          | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.160209      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/update       | update t set t = now() where id2 < 9           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.160867      |                       4 | `test`.`t`   | GEN_CLUST_INDEX | RECORD      |
|       200 | KILL 157     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/select       | select * from performance_schema.data_locks    | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:35:43.682078      |                       4 | `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列 为普通索引列

代码语言：javascript
代码运行次数：0
复制
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;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+-------------------+
| 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:1095:140023517226320   |                174714 |       204 |       26 | test          | t           | NULL           | NULL              | NULL            |       140023517226320 | TABLE     | IX            | GRANTED     | NULL              |
| INNODB | 140023602396568:34:5:2:140023517223216 |                174714 |       204 |       26 | test          | t           | NULL           | NULL              | id2             |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 0x000000000321 |
| INNODB | 140023602396568:34:5:3:140023517223216 |                174714 |       204 |       26 | test          | t           | NULL           | NULL              | id2             |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 0x000000000322 |
| INNODB | 140023602396568:34:4:2:140023517223560 |                174714 |       204 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223560 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000321    |
| INNODB | 140023602396568:34:4:3:140023517223560 |                174714 |       204 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223560 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000322    |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+-------------------+
5 rows in set (0.01 sec)

会话2

代码语言：javascript
代码运行次数：0
复制
begin;
update t set t = now() where id2 = 5;
被阻塞，被阻塞的只是 id2<9 的有记录的值。
update t set t = now() where id2 = 12;
不被阻塞

会话3

代码语言：javascript
代码运行次数：0
复制
-- 默认开启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 |
+-----------+--------------+--------------------+-------------------+----------------------------+-----------------------------------------------------------+--------------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/com/Field List   | NULL                                                      | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:05.662346      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/drop_table   | drop table t                                              | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.352119      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6) , key(id2)) | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.486802      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(1,1,now())                           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.746588      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(2,5,now())                           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.844448      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(5,12,now())                          | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.866248      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(10,13,now())                         | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.878885      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/begin        | begin                                                     | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.892202      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/update       | update t set t = now() where id2 < 9                      | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:23.892780      |                       3 | `test`.`t`   | id2          | RECORD      |
|       204 | KILL 161     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/select       | select * from performance_schema.data_locks               | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:49:24.667518      |                       3 | `test`.`t`   | id2          | 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列为主键或者唯一索引

代码语言：javascript
代码运行次数：0
复制
drop table t;
create table t(id int, id2 int,t timestamp(6),unique 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;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+-------------------+
| 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:1096:140023517226320   |                174743 |       206 |       26 | test          | t           | NULL           | NULL              | NULL            |       140023517226320 | TABLE     | IX            | GRANTED     | NULL              |
| INNODB | 140023602396568:35:5:2:140023517223216 |                174743 |       206 |       26 | test          | t           | NULL           | NULL              | id2             |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 0x000000000325 |
| INNODB | 140023602396568:35:5:3:140023517223216 |                174743 |       206 |       26 | test          | t           | NULL           | NULL              | id2             |       140023517223216 | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 0x000000000326 |
| INNODB | 140023602396568:35:4:2:140023517223560 |                174743 |       206 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223560 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000325    |
| INNODB | 140023602396568:35:4:3:140023517223560 |                174743 |       206 |       26 | test          | t           | NULL           | NULL              | GEN_CLUST_INDEX |       140023517223560 | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000326    |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+-------------------+
5 rows in set (0.00 sec)

会话2

代码语言：javascript
代码运行次数：0
复制
begin;
update t set t = now() where id2 = 5;
会被阻塞

会话3

代码语言：javascript
代码运行次数：0
复制
-- 默认开启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 |
+-----------+--------------+--------------------+-------------------+----------------------------+----------------------------------------------------------------+--------------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/com/Field List   | NULL                                                           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:53:58.179301      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/drop_table   | drop table t                                                   | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:53:59.964937      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/create_table | create table t(id int, id2 int,t timestamp(6),unique key(id2)) | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.653556      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(1,1,now())                                | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.756707      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(2,5,now())                                | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.782447      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(5,12,now())                               | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.798519      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/insert       | insert into t values(10,13,now())                              | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.824510      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/begin        | begin                                                          | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.836701      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/update       | update t set t = now() where id2 < 9                           | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:04.837268      |                      17 | `test`.`t`   | id2          | RECORD      |
|       206 | KILL 163     | X,REC_NOT_GAP      | X,REC_NOT_GAP     | statement/sql/select       | select * from performance_schema.data_locks                    | update t set t = now() where id2 = 5 | test           | NULL        | 2021-08-30 16:54:23.760936      |                      17 | `test`.`t`   | id2          | 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：代表插入意向锁。

可见，不管更新列上有没有索引，锁定范围都是小于更新值且表中有记录的record值，不存在范围的情况，锁粒度都是X,REC_NOT_GAP，只锁定记录本身。

特殊情景

代码语言：javascript
代码运行次数：0
复制
sess 1:
drop table t;
create table t(id int, id2 int,t timestamp(6) ,unique 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,14,now());

会话2，让报唯一性冲突错误，可看到当前持有锁LOCK_TYPE为record的S模式锁。

代码语言：javascript
代码运行次数：0
复制
begin;
insert into t values(11,14,now());
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:1097:140023517232480   |                174782 |       207 |       30 | test          | t           | NULL           | NULL              | NULL       |       140023517232480 | TABLE     | IX        | GRANTED     | NULL               |
| INNODB | 140023602397424:36:5:5:140023517229376 |                174782 |       207 |       30 | test          | t           | NULL           | NULL              | id2        |       140023517229376 | RECORD    | S         | GRANTED     | 14, 0x00000000032C |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+--------------------+
2 rows in set (0.00 sec)

会话3会被阻塞

代码语言：javascript
代码运行次数：0
复制
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(13,13,now());
被阻塞

会话4

代码语言：javascript
代码运行次数：0
复制
select *
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 |
+-----------+--------------+--------------------+------------------------+-----------------------------+---------------------------------------------+-----------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/com/Field List    | NULL                                        | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:10.502628      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/com/Field List    | NULL                                        | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:10.502741      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/com/Field List    | NULL                                        | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:10.502899      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/begin         | begin                                       | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:48.387855      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/insert        | insert into t values(11,14,now())           | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:48.388288      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/show_warnings | show warnings                               | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:48.393043      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/begin         | begin                                       | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:54.119408      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/insert        | insert into t values(11,14,now())           | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:54.123883      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/show_warnings | show warnings                               | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:54.124350      |                       4 | `test`.`t`   | id2          | RECORD      |
|       210 | KILL 167     | S                  | X,GAP,INSERT_INTENTION | statement/sql/select        | select * from performance_schema.data_locks | insert into t values(13,13,now()) | test           | NULL        | 2021-08-30 17:07:54.227407      |                       4 | `test`.`t`   | id2          | RECORD      |
+-----------+--------------+--------------------+------------------------+-----------------------------+---------------------------------------------+-----------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
10 rows in set (0.02 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：代表插入意向锁。

这种情况会话2和会话3分别持有S锁和插入意向锁，互斥，所以产生了阻塞行为。

更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码

作者：姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server
云数据库 MySQL
TDSQL-C MySQL 版

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server
云数据库 MySQL
TDSQL-C MySQL 版
#MDL锁
#行锁
#MySQL表锁
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
目录
  • 全部关于锁文章
    • 可重复读
    • 幻读
  • RC级别行锁
  • where列无索引无主键
  • where列 为普通索引列
  • where列为主键或者唯一索引
  • 特殊情景
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2025 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论