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

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

全部关于锁文章

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

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

可重复读

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

幻读

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

RC级别行锁

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;
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

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

-- 默认开启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列 为普通索引列

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

begin;
update t set t = now() where id2 = 5;
被阻塞,被阻塞的只是 id2<9 的有记录的值。
update t set t = now() where id2 = 12;
不被阻塞

会话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 |
+-----------+--------------+--------------------+-------------------+----------------------------+-----------------------------------------------------------+--------------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       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列为主键或者唯一索引

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

begin;
update t set t = now() where id2 = 5;
会被阻塞

会话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 |
+-----------+--------------+--------------------+-------------------+----------------------------+----------------------------------------------------------------+--------------------------------------+----------------+-------------+---------------------------------+-------------------------+--------------+--------------+-------------+
|       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,只锁定记录本身。

特殊情景

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模式锁。

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会被阻塞

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

会话4

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数据库

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

    姚崇
  • 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 - 查询会锁表?

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

    bisal
  • 行锁:InnoDB 替代 MyISAM 的重要原因

    MySQL 5.5 之前的默认存储引擎是 MyISAM,5.5 之后改成了 InnoDB。InnoDB 后来居上最主要的原因就是:

    jeanron100
  • MySQL 加锁处理分析

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

    java达人
  • MySql 三大知识点——索引、锁、事务

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

    Bug开发工程师
  • 技术分享 | MySQL中查询会锁表 ?

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

    爱可生开源社区
  • 第03期:列非空与自增

    MySQL 里字段的属性很多,对性能来说,影响也是可大可小,所以针对其属性这一块有必要进行一次探究。

    爱可生开源社区
  • RR与RC隔离级别下MySQL不同的加锁解锁方式

    1)innodb_locks_unsafe_for_binlog是全局参数,影响所有session;但隔离级别可以是全局也可以是会话级别。

    沃趣科技
  • 数据库事务系列-MySQL跨行事务模型

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

    Java_老男孩
  • 一条简单的更新语句,MySQL是如何加锁的?

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

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

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

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

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

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

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

    数据和云
  • 漫谈死锁

    一 前言 死锁是每个MySQL DBA 都会遇到的技术问题,本文是自己针对死锁学习的一个总结,了解死锁是什么,MySQL如何检测死锁,处理死锁,死锁的案例,...

    用户1278550
  • 惊!MySQL MVCC原来这么简单

    MySQL InnoDB存储引擎的事务隔离级别主要是MVCC(MVCC,Multiversion Currency Control)实现的。

    黑洞代码
  • Mysql为何使用可重复读(Repeatable read)为默认隔离级别?

    群里有小伙伴面试时,碰到面试官提了个很刁钻的问题:Mysql为何使用可重复读(Repeatable read)为默认隔离级别???

    Java宝典

扫码关注云+社区

领取腾讯云代金券