MySQL Cases-MySQL找出谁持有行锁（RC）
全部关于锁文章
- 全局读锁https://cloud.tencent.com/developer/article/1869375
- 表锁 https://cloud.tencent.com/developer/article/1869546
- MDLhttps://cloud.tencent.com/developer/article/1869667
- MySQL找出谁持有行锁（RR级别）https://cloud.tencent.com/developer/article/1869793
- MySQL找出谁持有行锁（RC级别）https://cloud.tencent.com/developer/article/1869900
承接上文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)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
where列 为普通索引列
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)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
where列为主键或者唯一索引
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)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
可见，不管更新列上有没有索引，锁定范围都是小于更新值且表中有记录的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)- IX：代表意向排他锁表锁。
- X：代表Next-Key Lock锁定记录本身和记录之前的间隙(X)。
- S：代表Next-Key Lock锁定记录本身和记录之前的间隙(S)。
- X,REC_NOT_GAP：代表只锁定记录本身(X)。
- S,REC_NOT_GAP：代表只锁定记录本身(S)。
- X,GAP：代表间隙锁，不锁定记录本身(X)。
- X,GAP：代表间隙锁，不锁定记录本身(S)。
- X,GAP,INSERT_INTENTION：代表插入意向锁。
这种情况会话2和会话3分别持有S锁和插入意向锁，互斥，所以产生了阻塞行为。
更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码
作者：姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。
举报
腾讯云开发者
