80-lock
supremum 上确界;最小上界;上限;超绝
pseudo 英 [ˈsjuːdəʊ] 美 [ˈsudoʊ]。adj. 伪;假的;冒充的。n. 伪君子;假冒的人
phantom 英 [ˈfæntəm] 美 [ˈfæntəm]。n. 幻影;幽灵;幻象;鬼;鬼魂;幻觉。adj. 幻象的;像鬼的;幽灵似的;幻觉的;虚幻的
instrument 英 /ˈɪnstrəmənt n. 仪表
create table mysql_lock (
`primary` int not null primary key,
`unique` int null,
normal int null,
value int null,
constraint idx_unique unique (`unique`)
);
create index idx_normal on mysql_lock (normal);
insert into mysql_lock
values (10, 11, 12, 13),
(20, 21, 22, 23),
(30, 31, 32, 33),
(40, 41, 42, 43);
1. 锁的基本介绍
- 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂
- 相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制
- MyISAM和Memory存储引擎采用的是表级锁（table-level locking）
- InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁（table-level locking），但默认情况下是采用行级锁
- 表级锁（table-level locking）：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
- 更适合于以查询为主，只有少量按索引条件更新数据的应用。eg：Web应用
- 行级锁（row-level locking）：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
- 更适合于有大量按索引条件并发更新，同时又有并发查询的应用。eg：一些在线事务处理（OLTP）系统
- 表级锁（table-level locking）：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
- OLTP（On-Line_Transaction_Processing）：联机事务处理过程。对当前的data处理，时效性，立刻返回结果
- OLAP（On-Line_Analytic_Processing）：联机分析处理过程，对历史data进行分析处理，对未来决策产生影响
2. 粒度分类
- 全局锁：操作锁定DB所有表
- 表级锁：操作锁定整张表
- 行级锁：操作锁定对应的数据行
3. lock_status
- Mysql_5.7及之前，可以通过
information_schema.innodb_locks查看Trx的lock情况。但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况 - Mysql_8.0删除了
information_schema.innodb_locks，添加了performance_schema.data_locks。不但可以看到阻塞该Trx的锁，还可以看到该Trx所持有的锁 performance_schema.data_locks更通用，其他的Engine的Trx也可以查询
# 1. mysql_Trx
select * from information_schema.innodb_trx;
select trx_started, trx_query, trx_operation_state, trx_isolation_level
from information_schema.innodb_trx;
# 2.1. lock_info（Mysql5.7）
select * from information_schema.innodb_locks;
# 2.2. lock_info（Mysql8.0）
select * from performance_schema.data_locks;
select EVENT_ID as event_id,
THREAD_ID as thread_id,
ENGINE_TRANSACTION_ID as trx_id,
INDEX_NAME as index_name,
LOCK_TYPE as lock_type,
LOCK_MODE as lock_mode,
LOCK_STATUS as lock_status,
LOCK_DATA as lock_data
from performance_schema.data_locks;
# 3. 查询Mysql当前连接thread状态
show processlist;
# 4. 查看metadata_locks，需要开启instruments（Mysql8.0默认开启）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
# lock_type：EXCLUSIVE（排他锁），SHARED_READ（共享锁）
# lock_status："PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。27线程正在等待28线程释放SHARED_READ
select object_type, object_schema, object_name, lock_type, lock_duration, lock_status, owner_thread_id
from performance_schema.metadata_locks;
# 锁争用比较严重。eg：InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 18702 |
| Innodb_row_lock_time_avg | 18702 |
| Innodb_row_lock_time_max | 18702 |
| Innodb_row_lock_waits | 1 |
+-------------------------------+-------+
4. 全局锁
# 全局锁，不需要开启Trx，直接执行
flush tables with read lock;
# 解锁
unlock tables;
# 1. DQL，ok
select * from mysql_lock;
select * from mysql_lock for share;
# 2. DML，block_wait
select * from mysql_lock for update;
insert into mysql_lock (id) values (rand());
update mysql_lock set value = rand() where id = '1';
# 3. DDL，block_wait
alter table mysql_lock add column java int;
# 备份库，在windows命令行中执行
mysqldump -uroot -proot mysql_lock > mysql_lock.sql
# 底层通过快照读来实现数据备份，不用再加全局锁
mysqldump --single-transaction -uroot -proot mysql_lock > mysql_lock.sql
1. 缺点
- 主库备份，业务停摆
- 从库备份，会暂停主库同步的binlog，导致主从延迟
- 在InnoDB中，备份时加
--single-transaction参数来完成不加锁的一致性数据备份
5. Table_lock
- 表锁
- 表共享读锁（table_read_lock）
- 表独占写锁（table_write_lock）
- 元数据锁（MetaData_Lock，MDL）
- 意向锁
1. MDL
- 元数据锁（MetaData_Lock）
- 8. MDL
2. 意向锁
6. MyISAM
- 不支持Trx
- 表共享读锁（Table_Read_Lock），不会阻塞其他用户的读，但是会阻塞写
- A可以DQL。不能DDL、DML
- B可以DQL。DDL、DML（block_wait）
- 表独占写锁（Table_Write_Lock），会阻塞其他用户的读和写
- A可以DQL、DDL、DML
- B的DQL、DDL、DML（block_wait）
- table_lock的session，不能访问其他table
- 《S》与《X》，《S》与《X》之间是串行的
- MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置
LOW_PRIORITY_UPDATES参数，或在insert, update, delete语句中指定LOW_PRIORITY选项来调节读写锁的争用
# 如果`Table_locks_waited`的值比较高，则说明存在着较严重的表级锁争用情况
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 352 |
| Table_locks_waited | 2 |
+-----------------------+-------+
1. sql
- DQL，会自动给涉及的表加《S》
- DML，会自动给涉及的表加《X》
- 这个过程并不需要用户干预，不需要显式加锁。下例中的加锁时为了演示效果
create table myisam
(
id varchar(36) not null comment '主键id' primary key,
name varchar(50) null comment '名称',
auth_key varchar(50) null comment '密码',
sex enum ('男', '女') null comment '性别',
age tinyint null comment '年龄',
birthday datetime null comment '最近ip地址',
salary double(10, 2) default 0.00 null comment '薪水',
ip int unsigned null comment 'ip地址',
remark varchar(255) null comment '备注',
crt_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
upd_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
)
comment 'myisam表' engine = MyISAM;
# session1
# 0. 解锁
unlock tables;
# 1. 《S》表共享读锁。SHARED_READ_ONLY
lock tables myisam read;
+-----------+------------------+--------------+----------------+-------------+-----------+---------------+--------------+
|OBJECT_TYPE|OBJECT_SCHEMA |OBJECT_NAME |LOCK_TYPE |LOCK_DURATION|LOCK_STATUS|OWNER_THREAD_ID|OWNER_EVENT_ID|
+-----------+------------------+--------------+----------------+-------------+-----------+---------------+--------------+
|TABLE |mca |myisam |SHARED_READ_ONLY|TRANSACTION |GRANTED |50 |180 |
|TABLE |performance_schema|metadata_locks|SHARED_READ |TRANSACTION |GRANTED |55 |146 |
+-----------+------------------+--------------+----------------+-------------+-----------+---------------+--------------+
# 2. 《X》表独占写锁。SHARED_NO_READ_WRITE
lock tables myisam write;
+-----------+------------------+--------------+--------------------+-------------+-----------+---------------+--------------+
|OBJECT_TYPE|OBJECT_SCHEMA |OBJECT_NAME |LOCK_TYPE |LOCK_DURATION|LOCK_STATUS|OWNER_THREAD_ID|OWNER_EVENT_ID|
+-----------+------------------+--------------+--------------------+-------------+-----------+---------------+--------------+
|GLOBAL |NULL |NULL |INTENTION_EXCLUSIVE |STATEMENT |GRANTED |50 |210 |
|SCHEMA |mca |NULL |INTENTION_EXCLUSIVE |TRANSACTION |GRANTED |50 |210 |
|TABLE |mca |myisam |SHARED_NO_READ_WRITE|TRANSACTION |GRANTED |50 |210 |
|TABLE |performance_schema|metadata_locks|SHARED_READ |TRANSACTION |GRANTED |55 |162 |
+-----------+------------------+--------------+--------------------+-------------+-----------+---------------+--------------+
# 3. session1只能DQL
# DQL
select * from myisam;
# 3.1. 有lock的session，不能访问lock以外的表
select * from mysql_lock;
# 3.2. DML
# [HY000][1099] Table 'myisam' was locked with a READ lock and can't be updated
insert into myisam (id) values (uuid());
# [HY000][1099] Table 'myisam' was locked with a READ lock and can't be updated
update myisam set salary = rand() where id = '1';
# 3.3. DDL
# [HY000][1099] Table 'myisam' was locked with a READ lock and can't be updated
alter table myisam add column java int;
# session2
# S_lock：DQL，ok；DML、DDL，block_wait
# X_lock：DQL、DML、DDL，block_wait
# 4.1. DQL，ok
select * from myisam;
# 4.2. DML
insert into myisam (id) values (uuid());
update myisam set salary = rand() where id = '1';
# 4.3. DDL
alter table myisam add column java int;
# 5.1. 查询不到lock_info
select * from performance_schema.data_locks;
# 5.2. 能查询到MDL
select * from performance_schema.metadata_locks;
2. 并发插入问题
- MyISAM表的读和写是串行的，总体而言的。在一定条件下，MyISAM也支持查询和插入操作的并发执行（相当于RR隔离级别）
lock tables my_lock read local;增加关键字local- InnoDB 引擎不存在这个问题
# session1
# lock read_lock
lock tables myisam read local;
# can_select，但是查询不到session2_insert的数据
# unlock 后即可看到插入的data（感觉和《可重复读》隔离级别是一样的）
select * from myisam;
# unlock read_lock
unlock tables;
# session2
# can_select，可以查询到session2_insert的数据
select * from myisam;
# can_insert
insert into myisam (id, salary) values (uuid(), rand());
7. InnoDB
- row_lock是基于index实现的，如果不通过索引访问数据，InnoDB会使用table_lock
- 表未定义primary时，InnoDB自动创建隐藏的聚簇索引（GEN_CLUST_INDEX）
- lock是从table或row上请求、获取、持有的。《Trx请求lock》《Trx获取lock》《Trx持有lock》是书面语
- row_lock、table_lock
- where后有独立index（row_lock）
- where后有组合index，最左前缀（row_lock）
- where后无index（table_lock）
performance_schema.data_locks并不显示所有lock，隐式的lock，需要触发
1. 支持Trx
2. select
1. 一致性非锁定读
- consistent nonlocking read
- 不加锁（Serializable隔离级别加锁）
- InnoDB采用多版本并发控制（MVCC, multiversion concurrency control）来增加读操作的并发性
- MVCC：基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁。在T1读取的同一时刻，T2可以自由的修改T1所读取的数据
- 隔离级别为RU和Serializable时不需要MVCC，因此，只有RC和RR时，才存在MVCC，才存在一致性非锁定读
2. 锁定读
- locking read
# 共享锁
select... for share # Mysql8引入
select... lock in share mode
# 排他锁
select... for update
3. 八种锁
1 . 共享锁、排他锁
- Shared_Locks、Exclusive_Locks
- 并不是一种具体的锁，而是锁的模式，每种锁都有shared、exclusive两种模式
- 共享锁《Shared_Locks》《S锁》
- A可以DQL。DDL、DML会升级为X锁。可以操作other_row、other_table
- B可以DQL。DDL、DML（block_wait）
- 加锁
1. `select... lock in share mode` 或 `select... lock for share`
2. unique_key_Duplicate后当前事务会对该key
3. `create table new_table select * from old_table`对old_table
- 排他锁《Exclusive_Locks》《X锁》
- A可以DQL、DDL、DML。可以操作other_row、other_table
- B的DQL、DDL、DML（block_wait）
- 加锁
1. update, delete, insert
2. `select... for update`
2. 意向锁
- Intention_Locks
- LOCK_MODE：《IS》《IX》
- 表锁
- 《Intention_Shared_Locks》《IS锁》
- 《Intention_Exclusive_Locks》《IX锁》
- Aim：支持多粒度锁（multiple_granularity_locking）
- Trx请求table_lock时，直接通过《IS》《IX》判断row_lock
- Trx在获取row_lock之前，必须首先获取table上的《IS》《IX》或表上的更强的锁
3. 记录锁
- Record_Locks
- LOCK_MODE：《S,REC_NOT_GAP》《X,REC_NOT_GAP》
- 行锁，锁定index
- 未定义primary时，InnoDB自动创建隐藏的聚簇索引（GEN_CLUST_INDEX）
4. 间隙锁
- Gap_Locks
- LOCK_MODE：《S,GAP》《X,GAP》
- 范围：(满足条件index ~ 第一个不满足条件的index) 之间的间隙
- Aim：《gap_lock》阻塞《insert_intention_lock》。隔离级别RR利用此特性来解决《phantom_row问题》
- 《shared_gap_lock》==《exclusive_gap_lock》
+-------+------+------+-----+
|primary|unique|normal|value|
+-------+------+------+-----+
|10 |11 |12 |13 |
|20 |21 |22 |23 |
|30 |31 |32 |33 |
|40 |41 |42 |43 |
+-------+------+------+-----+
begin;
select ENGINE_LOCK_ID as lock_id,
ENGINE_TRANSACTION_ID as trx_id,
INDEX_NAME as index_name,
LOCK_TYPE as lock_type,
LOCK_MODE as lock_mode,
LOCK_STATUS as lock_status,
LOCK_DATA as lock_data
from performance_schema.data_locks;
commit;
1. Insert_Intention_Locks
select * from mysql_lock where normal = 22 for update;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|179 |50 |2853 |NULL |TABLE |IX |GRANTED |NULL |
|179 |50 |2853 |idx_normal|RECORD |X |GRANTED |22, 20 | # 下一键锁
|179 |50 |2853 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 | # 记录锁
|179 |50 |2853 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 | # 间隙锁
+--------+---------+------+----------+---------+-------------+-----------+---------+
- 《Next-Key_Locks》锁定了
(12, 22] - 《Record_Locks》锁定了22
- 《gap_lock》锁定了
(22, 32) - 下一键锁《Next-Key_Locks》 + 记录锁《Record_Locks》+ 间隙锁《Gap_Locks》来封锁
(12, 32)区间
# T2。《X,GAP,INSERT_INTENTION》(15, 22)，与T1间隙锁冲突
insert into mysql_lock (`primary`, normal) values (1, 15);
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|51 |59 |2856 |NULL |TABLE |IX |GRANTED |NULL |
|51 |59 |2856 |idx_normal|RECORD |X,GAP,INSERT_INTENTION|WAITING |22, 20 |
|290 |50 |2855 |NULL |TABLE |IX |GRANTED |NULL |
|290 |50 |2855 |idx_normal|RECORD |X |GRANTED |22, 20 |
|290 |50 |2855 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |20 |
|290 |50 |2855 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
# 《X,GAP,INSERT_INTENTION》(25, 32)，与T1间隙锁冲突
insert into mysql_lock (`primary`, normal) values (1, 25);
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|67 |59 |2857 |NULL |TABLE |IX |GRANTED |NULL |
|67 |59 |2857 |idx_normal|RECORD |X,GAP,INSERT_INTENTION|WAITING |32, 30 |
|290 |50 |2855 |NULL |TABLE |IX |GRANTED |NULL |
|290 |50 |2855 |idx_normal|RECORD |X |GRANTED |22, 20 |
|290 |50 |2855 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |20 |
|290 |50 |2855 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
# 成功
insert into mysql_lock (`primary`, normal) values (1, 35);
2. upd《phantom_row》
# 1. t1 (12, 32)
update mysql_lock set normal = 100 where normal = 22;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|382 |50 |2859 |NULL |TABLE |IX |GRANTED |NULL |
|382 |50 |2859 |idx_normal|RECORD |X |GRANTED |22, 20 |
|382 |50 |2859 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
|382 |50 |2859 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 2. t2
update mysql_lock set normal = 22 where normal = 42;
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|131 |59 |2868 |NULL |TABLE |IX |GRANTED |NULL |
|131 |59 |2868 |idx_normal|RECORD |X |GRANTED |42, 40 |
|131 |59 |2868 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |40 |
|131 |59 |2868 |idx_normal|RECORD |X,GAP |GRANTED |100, 20 |
|131 |59 |2868 |idx_normal|RECORD |X,GAP,INSERT_INTENTION|WAITING |22, 20 |
|382 |50 |2859 |NULL |TABLE |IX |GRANTED |NULL |
|382 |50 |2859 |idx_normal|RECORD |X |GRANTED |22, 20 |
|382 |50 |2859 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |20 |
|382 |50 |2859 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
|99 |59 |2859 |idx_normal|RECORD |X,REC_NOT_GAP |GRANTED |100, 20 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
# t1隐式锁《X,REC_NOT_GAP》
- 问题：T1把所有22的行更新为100，T2把42的行更新为22。如果T2不被阻塞，T1的where条件岂不是多出了一行，即：T1出现了《phantom_row》
- T1加的锁：
(12, 32) - T2加的锁：
(32, 100)。T2触发T1隐式锁《X,REC_NOT_GAP》(100, 20) - T2设置《insert_intention_lock》
(22, 32)。与 T1《gap_lock》(22, 32)冲突，T2被阻塞
5. 下一键锁
- Next-Key Locks
- LOCK_MODE：《S》《X》
- 范围：(锁定index记录 + 该index前面的间隙]
- 《supremum pseudo-record》：无穷值。伪记录（pseudo-record）
- 《supremum pseudo-record》的《next-key_lock》即max_index后面的间隙
select * from mysql_lock where `primary` > 40 for update;
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data |
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|623 |50 |2873 |NULL |TABLE |IX |GRANTED |NULL |
|623 |50 |2873 |PRIMARY |RECORD |X |GRANTED |supremum pseudo-record|
+--------+---------+------+----------+---------+---------+-----------+----------------------+
6. 插入意向锁
- Insert Intention Locks
- LOCK_MODE：《S,GAP,INSERT_INTENTION》《X,GAP,INSERT_INTENTION》
- 隐式锁。在insert时显现出来
- 范围：（插入值 ~ 下一个索引值）
- insert前，持有row《insert_intention_lock》
- insert后，持有该row的《index_record_lock》
- shared == exclusive
- 隐式锁
作用：
- 《gap_lock》会阻塞《insert_intention_lock》。事实上，《gap_lock》存在意义即阻塞《insert_intention_lock》
- 隔离级别RR，正是利用此特性来解决《phantom_row问题》
- 《gap_lock》相互不会阻塞
- 《insert_intention_lock》相互不会阻塞
- 《insert_intention_lock》也不会阻塞《gap_lock》
1. insert前
# 1. t1
select * from mysql_lock where normal = 22 for update ;
# 2. t2: insert前《X,GAP,INSERT_INTENTION》
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,22,1);
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|165 |59 |2884 |NULL |TABLE |IX |GRANTED |NULL |
|165 |59 |2884 |idx_normal|RECORD |X,GAP,INSERT_INTENTION|WAITING |22, 20 |
|826 |50 |2883 |NULL |TABLE |IX |GRANTED |NULL |
|826 |50 |2883 |idx_normal|RECORD |X |GRANTED |22, 20 |
|826 |50 |2883 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |20 |
|826 |50 |2883 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
2. insert后
# 1. t1
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,15,1);
# 2. t2: insert后，隐式行锁《X,REC_NOT_GAP》
select * from mysql_lock where normal = 15 for update ;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|150 |59 |2882 |NULL |TABLE |IX |GRANTED |NULL |
|150 |59 |2882 |idx_normal|RECORD |X |WAITING |15, 1 |
|730 |50 |2877 |NULL |TABLE |IX |GRANTED |NULL |
|150 |59 |2877 |idx_normal|RECORD |X,REC_NOT_GAP|GRANTED |15, 1 | # t1隐式行锁
+--------+---------+------+----------+---------+-------------+-----------+---------+
7. 自增锁
- AUTO-INC Locks
- LOCK_MODE：《AUTO-INC》
- 表级锁
- insert开始时请求该lock，insert结束后释放该lock（注意：是语句结束后，而不是事务结束后）
- 《AUTO-INC locks》是在语句结束后被释放，较难在
performance_schema.data_locks中查看到
# 默认值1。代表连续，事务未提交则id永久丢失
show variables like 'innodb_autoinc_lock_mode'
8. 空间索引
- Predicate Locks for Spatial Indexes
- 很少用到MySQL的空间索引。所以忽略此类型的锁
4. 加锁分析
- primary《X,REC_NOT_GAP》《X》《X,GAP》
- row对应的other_index《X,REC_NOT_GAP》(index_data, primary_data)
- 更新了索引列，即增加《X,REC_NOT_GAP》 (index_data, primary_data)
- primary之外的任何index上加《S》《X》时，都需要在primary加《index_record_lock》
- unique_index唯一搜索条件，获取《index_record_lock》，不锁定gap
- unique_index作范围搜索，依然会锁定gap，并且在primary上设置锁
- normal_index搜索或扫描，每一个索引设置《next-key_lock》
- 在第一个不满足搜索条件的index设置《gap_lock》或《next-key_lock》
- 一般，等值条件设置《gap_lock》，范围条件设置《next-key_lock》
- insert前，设置《insert_intention_lock》。insert后设置《Record_Locks》
1. primary
# 1.1. 等值《X,REC_NOT_GAP》
select * from mysql_lock where `primary` = 20 for update;
update mysql_lock set value = 12 where `primary` = 20;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|254 |50 |2428 |NULL |TABLE |IX |GRANTED |NULL |
|254 |50 |2428 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 1.2. 不等值《X,GAP》
select * from mysql_lock where `primary` = 15 for update;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|301 |50 |2429 |NULL |TABLE |IX |GRANTED |NULL |
|301 |50 |2429 |PRIMARY |RECORD |X,GAP |GRANTED |20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 1.3. 范围值《X》
select * from mysql_lock where `primary` > 20 for update;
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data |
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|348 |50 |2430 |NULL |TABLE |IX |GRANTED |NULL |
|348 |50 |2430 |PRIMARY |RECORD |X |GRANTED |supremum pseudo-record|
|348 |50 |2430 |PRIMARY |RECORD |X |GRANTED |30 |
|348 |50 |2430 |PRIMARY |RECORD |X |GRANTED |40 |
+--------+---------+------+----------+---------+---------+-----------+----------------------+
# 2.1. t1
select * from mysql_lock where `primary` = 20 for update;
# 2.2. t2: insert: 《S,REC_NOT_GAP》
insert into mysql_lock (`primary`, `unique`, normal, value) values (20,1,1,1);
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|241 |55 |2432 |NULL |TABLE |IX |GRANTED |NULL |
|241 |55 |2432 |PRIMARY |RECORD |S,REC_NOT_GAP|WAITING |20 |
|395 |50 |2431 |NULL |TABLE |IX |GRANTED |NULL |
|395 |50 |2431 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 3.1. delete不存在的index《X,GAP》
delete from mysql_lock where `primary` = 15;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|455 |50 |2433 |NULL |TABLE |IX |GRANTED |NULL |
|455 |50 |2433 |PRIMARY |RECORD |X,GAP |GRANTED |20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 3.2. delete已存在的index《X,REC_NOT_GAP》
delete from mysql_lock where `primary` = 20;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|532 |50 |2434 |NULL |TABLE |IX |GRANTED |NULL |
|532 |50 |2434 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
2. unique_index
- unique_index作等值查询，《X,REC_NOT_GAP》、primary《X,REC_NOT_GAP》
# 1.1. 等值《X,REC_NOT_GAP》
select * from mysql_lock where `unique` = 21 for update;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|597 |50 |2436 |NULL |TABLE |IX |GRANTED |NULL |
|597 |50 |2436 |idx_unique|RECORD |X,REC_NOT_GAP|GRANTED |21, 20 |
|597 |50 |2436 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 1.2. 不等值《X,GAP》
select * from mysql_lock where `unique` = 15 for update;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|660 |50 |2437 |NULL |TABLE |IX |GRANTED |NULL |
|660 |50 |2437 |idx_unique|RECORD |X,GAP |GRANTED |21, 20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 1.3. 范围值《X》
select * from mysql_lock where `unique` > 21 for update;
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data |
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
|723 |50 |2438 |NULL |TABLE |IX |GRANTED |NULL |
|723 |50 |2438 |idx_unique|RECORD |X |GRANTED |supremum pseudo-record|
|723 |50 |2438 |idx_unique|RECORD |X |GRANTED |31, 30 |
|723 |50 |2438 |idx_unique|RECORD |X |GRANTED |41, 40 |
|723 |50 |2438 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |30 |
|723 |50 |2438 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |40 |
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
# 2.1. t1
select * from mysql_lock where `unique` = 21 for update;
# 2.2. t2: insert: 《S》
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,21,1, 1);
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|257 |55 |2440 |NULL |TABLE |IX |GRANTED |NULL |
|257 |55 |2440 |idx_unique|RECORD |S |WAITING |21, 20 |
|786 |50 |2439 |NULL |TABLE |IX |GRANTED |NULL |
|786 |50 |2439 |idx_unique|RECORD |X,REC_NOT_GAP|GRANTED |21, 20 |
|786 |50 |2439 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 3.1. delete不存在的index《X,GAP》
delete from mysql_lock where `unique` = 15;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|846 |50 |2441 |NULL |TABLE |IX |GRANTED |NULL |
|846 |50 |2441 |idx_unique|RECORD |X,GAP |GRANTED |21, 20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 3.2. delete已存在的index《X,REC_NOT_GAP》
delete from mysql_lock where `unique` = 21;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|908 |50 |2442 |NULL |TABLE |IX |GRANTED |NULL |
|908 |50 |2442 |idx_unique|RECORD |X,REC_NOT_GAP|GRANTED |21, 20 |
|908 |50 |2442 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
3. normal_index
- normal_index等值查询，《X》+《X,GAP》+《X,REC_NOT_GAP》primary
# 1.1. 等值。PRIMARY《X,REC_NOT_GAP》；normal《X》+《X,GAP》
select * from mysql_lock where normal = 22 for update;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|1052 |50 |2450 |NULL |TABLE |IX |GRANTED |NULL |
|1052 |50 |2450 |idx_normal|RECORD |X |GRANTED |22, 20 |
|1052 |50 |2450 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
|1052 |50 |2450 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 1.2. 不等值
select * from mysql_lock where normal = 15 for update;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|1115 |50 |2451 |NULL |TABLE |IX |GRANTED |NULL |
|1115 |50 |2451 |idx_normal|RECORD |X,GAP |GRANTED |22, 20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 1.3. 范围值
select * from mysql_lock where normal > 22 for update;
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data |
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
|1178 |50 |2452 |NULL |TABLE |IX |GRANTED |NULL |
|1178 |50 |2452 |idx_normal|RECORD |X |GRANTED |supremum pseudo-record|
|1178 |50 |2452 |idx_normal|RECORD |X |GRANTED |32, 30 |
|1178 |50 |2452 |idx_normal|RECORD |X |GRANTED |42, 40 |
|1178 |50 |2452 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |30 |
|1178 |50 |2452 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |40 |
+--------+---------+------+----------+---------+-------------+-----------+----------------------+
# 2.1. t1
select * from mysql_lock where normal = 22 for update;
# 2.2. t2: insert: 《X,GAP,INSERT_INTENTION》
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,13, 1);
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|289 |55 |2454 |NULL |TABLE |IX |GRANTED |NULL |
|289 |55 |2454 |idx_normal|RECORD |X,GAP,INSERT_INTENTION|WAITING |22, 20 |
|1241 |50 |2453 |NULL |TABLE |IX |GRANTED |NULL |
|1241 |50 |2453 |idx_normal|RECORD |X |GRANTED |22, 20 |
|1241 |50 |2453 |PRIMARY |RECORD |X,REC_NOT_GAP |GRANTED |20 |
|1241 |50 |2453 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
# 3.1. delete不存在的index
delete from mysql_lock where normal = 15;
+--------+---------+------+----------+---------+---------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data|
+--------+---------+------+----------+---------+---------+-----------+---------+
|1301 |50 |2456 |NULL |TABLE |IX |GRANTED |NULL |
|1301 |50 |2456 |idx_normal|RECORD |X,GAP |GRANTED |22, 20 |
+--------+---------+------+----------+---------+---------+-----------+---------+
# 3.2. delete已存在的index
delete from mysql_lock where normal = 22;
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|1363 |50 |2457 |NULL |TABLE |IX |GRANTED |NULL |
|1363 |50 |2457 |idx_normal|RECORD |X |GRANTED |22, 20 |
|1363 |50 |2457 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |20 |
|1363 |50 |2457 |idx_normal|RECORD |X,GAP |GRANTED |32, 30 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
4. no_index
- 进行表锁
select * from mysql_lock where value = 23 for update;
# 1. 定义primary。锁all_primary《X》
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode|lock_status|lock_data |
+--------+---------+------+----------+---------+---------+-----------+----------------------+
|1460 |50 |2459 |NULL |TABLE |IX |GRANTED |NULL |
|1460 |50 |2459 |PRIMARY |RECORD |X |GRANTED |supremum pseudo-record|
|1460 |50 |2459 |PRIMARY |RECORD |X |GRANTED |10 |
|1460 |50 |2459 |PRIMARY |RECORD |X |GRANTED |20 |
|1460 |50 |2459 |PRIMARY |RECORD |X |GRANTED |30 |
|1460 |50 |2459 |PRIMARY |RECORD |X |GRANTED |40 |
+--------+---------+------+----------+---------+---------+-----------+----------------------+
# 2. 未定义primary，锁GEN_CLUST_INDEX。InnoDB自动创建隐藏的聚集索引（GEN_CLUST_INDEX）
+--------+---------+------+---------------+---------+---------+-----------+----------------------+
|event_id|thread_id|trx_id|index_name |lock_type|lock_mode|lock_status|lock_data |
+--------+---------+------+---------------+---------+---------+-----------+----------------------+
|1538 |50 |2493 |NULL |TABLE |IX |GRANTED |NULL |
|1538 |50 |2493 |GEN_CLUST_INDEX|RECORD |X |GRANTED |supremum pseudo-record|
|1538 |50 |2493 |GEN_CLUST_INDEX|RECORD |X |GRANTED |0x000000000300 |
|1538 |50 |2493 |GEN_CLUST_INDEX|RECORD |X |GRANTED |0x000000000301 |
|1538 |50 |2493 |GEN_CLUST_INDEX|RECORD |X |GRANTED |0x000000000302 |
|1538 |50 |2493 |GEN_CLUST_INDEX|RECORD |X |GRANTED |0x000000000303 |
+--------+---------+------+---------------+---------+---------+-----------+----------------------+
5. 不同隔离级别加锁
- 任何隔离级别，sql语句执行时，都是先由InnoDB执行索引扫描
- 然后，结果集给mysql_server，server再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集
《gap_lock》是用来阻止《phantom_row》的，而RC时是允许《phantom_row》。所以，RC时禁用了《gap_lock》。上图中，RC时没有在索引上设置《gap_lock》或《next-key_lock》
6. 死锁
主键或唯一索引，《duplicate-key_error》时，会加共享锁。可能导致死锁
- 死锁的可能性并不受隔离级别影响，因为隔离级别改变的是读操作的行为，而死锁是由写操作产生的
- 死锁并不可怕，Mysql会选择一个牺牲者。在系统变量
innodb_lock_wait_timeout指定的秒数达到后，自动回滚牺牲者事务 - Mysql5.7开始，新加入了系统变量
innodb_deadlock_detect（默认ON），开启此变量，sql不会再等待，一旦探测到死锁，就立即回滚牺牲者事务
# 1. t1
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,1,1);
# 2. t2
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,1,1);
# 3. t3
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,1,1);
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|36 |63 |2891 |NULL |TABLE |IX |GRANTED |NULL |
|36 |63 |2891 |PRIMARY |RECORD |S,REC_NOT_GAP|WAITING |1 |
|232 |59 |2890 |NULL |TABLE |IX |GRANTED |NULL |
|232 |59 |2890 |PRIMARY |RECORD |S,REC_NOT_GAP|WAITING |1 |
|1076 |50 |2889 |NULL |TABLE |IX |GRANTED |NULL |
|232 |59 |2889 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
- T1 rollback，T2、T3死锁
- T1释放了primary上的《index_record_lock》。T2、T3持有《S,REC_NOT_GAP》，T2、T3请求《X,REC_NOT_GAP》，双方谁都不会放弃已经得到的《S,REC_NOT_GAP》（T2放弃《S》，T3即刻获取《X》）
# 1. t1 存在的primary。记录锁
delete from mysql_lock where `primary` = 10;
# 2. t2
insert into mysql_lock (`primary`, `unique`, normal, value) values (10,1,1,1);
# 3. t3
insert into mysql_lock (`primary`, `unique`, normal, value) values (10,1,1,1);
+--------+---------+------+----------+---------+-------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+-------------+-----------+---------+
|280 |59 |2909 |NULL |TABLE |IX |GRANTED |NULL |
|280 |59 |2909 |PRIMARY |RECORD |S,REC_NOT_GAP|WAITING |10 |
|84 |63 |2908 |NULL |TABLE |IX |GRANTED |NULL |
|84 |63 |2908 |PRIMARY |RECORD |S,REC_NOT_GAP|WAITING |10 |
|1232 |50 |2907 |NULL |TABLE |IX |GRANTED |NULL |
|1232 |50 |2907 |PRIMARY |RECORD |X,REC_NOT_GAP|GRANTED |10 |
+--------+---------+------+----------+---------+-------------+-----------+---------+
# 1. t1 不存在的primary。间隙锁
delete from mysql_lock where `primary` = 1;
# 2. t2
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,1,1);
# 3. t3
insert into mysql_lock (`primary`, `unique`, normal, value) values (1,1,1,1);
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|event_id|thread_id|trx_id|index_name|lock_type|lock_mode |lock_status|lock_data|
+--------+---------+------+----------+---------+----------------------+-----------+---------+
|264 |59 |2902 |NULL |TABLE |IX |GRANTED |NULL |
|264 |59 |2902 |PRIMARY |RECORD |X,GAP,INSERT_INTENTION|WAITING |10 |
|68 |63 |2901 |NULL |TABLE |IX |GRANTED |NULL |
|68 |63 |2901 |PRIMARY |RECORD |X,GAP,INSERT_INTENTION|WAITING |10 |
|1170 |50 |2900 |NULL |TABLE |IX |GRANTED |NULL |
|1170 |50 |2900 |PRIMARY |RECORD |X,GAP |GRANTED |10 |
+--------+---------+------+----------+---------+----------------------+-----------+---------+
8. MDL
- 元数据锁（MetaData_Lock）
- MySQL表结构变更你不可不知的Metadata Lock详解
- 解决MySQL 5.7中定位DDL被阻塞的问题
Waiting for table metadata lock肯定不会陌生，一般进行alter操作时被阻塞了。在show processlist时，线程的状态是在等metadata lock- 在线上进行DDL操作时，相对于其可能带来的系统负载，其实，最担心的还是MDL可能导致的阻塞问题
- 一旦DDL操作因获取不到MDL被阻塞，后续其它针对该表的其它操作都会被阻塞。如阻塞稍久，会看到Threads_running飙升，CPU告警
- 一般建议将DDL操作放到业务低峰期做
- 避免对系统负载产生较大影响
- 减少DDL被阻塞的概率
1. MDL引入的背景
- MDL是MySQL 5.5.3引入的，主要用于解决两个问题
1. RR下不可重复读
- 演示环境 MySQL 5.5.0。虽然是RR隔离级别，但在开启事务的情况下，第二次查询却没有结果
session1> begin;
session1> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
session2> alter table t1 add c1 int;
# 由于session2添加字段DDL，session1不可重复读
session1> select * from t1;
Empty set (0.00 sec)
session1> commit;
session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
2. 主从复制问题
- 包括主从数据不一致，主从复制中断等
- 主库0条结果
session1> create table t1(id int, name varchar(10)) engine=innodb;
session1> begin;
session1> insert into t1 values(1, 'a');
session2> truncate table t1;
session1> commit;
session1> select * from t1;
Empty set (0.00 sec)
- 从库一条结果
session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
- 看看binlog的内容，可以看到，truncate操作记录在前，insert操作记录在后
# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;
# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;
# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1,'a')
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;
- 如果T2执行
drop table操作，还会导致主从中断 - 如果T2执行
alter table操作，其依旧会被阻塞，阻塞时间受innodb_lock_wait_timeout参数限制
mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)
2. MDL概念
- MDL为了保护处于一个Trx中的表的结构不被修改。维护表元数据一致性，避免DML与DDL冲突，保证读写正确性
- 事务包括两类，显式事务和AC-NL-RO（auto-commit non-locking read-only）事务
- 显式事务包括两类
- 关闭AutoCommit下的操作
- 以
begin或start transaction开始的操作
- AC-NL-RO可理解为AutoCommit开启下的select操作
- 显式事务包括两类
- 5.5引入MDL，对表增、删、查、改加（共享锁）。对表结构变更操作，加（排他锁）。系统自动控制，无需显式使用
- MDL的最大等待时间由
lock_wait_timeout参数决定，其默认值为31536000（365天）。在使用工具进行DDL操作时，这个值就不太合理- 事实上，pt-online-schema-change和gh-ost对其就进行了相应的调整，其中，前者60s，后者3s
- 如果一个sql语法上有效，但执行时报错。eg：列名不存在，其同样会获取MDL锁，直到事务结束才释放
| 名称 | 类型 | 优先级 | 说明 |
|---|---|---|---|
| SHARED_UPGRADABLE | 共享升级锁 | 0 | 一般在执行DDL时On-line情况下会产生该锁 |
| EXCLUSIVE | 排他锁X | 1 | 一般在执行DDL时会产生该锁 |
| SHARED_NO_READ_WRITE | 排他锁X | 1 | 执行lock tables xxx write产生该锁 |
| SHARED_WRITE | 意向排他锁IX | 2 | 一般执行DML的更新语句 或 select ... for update产生该锁 |
| SHARED_READ | 意向共享锁IS | 2.5 | select ... lock in share mode产生该锁（8.0以后select ... for share） |
| SHARED_READ_ONLY | 共享锁S | 3 | 执行lock tables xxx read产生该锁 |
On-line重建表流程
- 新建A1，扫描表A主键的所有数据页
- 数据页中表A的记录生成B+树，存储到A1中
- 在生成A1过程中，对表A的操作记录在日志文件（row_log）
- A1生成后，将（row_log）文件中操作在临时文件执行，得到逻辑上与表A相同的数据文件
- 用A1替换表A
- MDL不仅仅适用于表，同样也适用于其它对象，如下表。其中，"等待状态"对应的是
show processlist中的State
| 适用对象 | 等待状态（State） |
|---|---|
| tablespace | Waiting for tablespace metadata lock |
| schema | Waiting for schema metadata lock |
| table | Waiting for table metadata lock |
| function | Waiting for stored function metadata lock |
| procedure | Waiting for stored procedure metadata lock |
| trigger | Waiting for trigger metadata lock |
| event | Waiting for event metadata lock |
| resource groups | Waiting for resource groups metadata lock |
| foreign key | Waiting for foreign key metadata lock |
为了提高数据库的并发度，MDL被细分为了11种类型：
- INTENTION_EXCLUSIVE
- SHARED
- SHARED_HIGH_PRIO
- SHARED_READ：DQL操作
- SHARED_WRITE：DML操作
- SHARED_WRITE_LOW_PRIO
- SHARED_UPGRADABLE：DDL操作
- SHARED_READ_ONLY
- SHARED_NO_WRITE
- SHARED_NO_READ_WRITE
- EXCLUSIVE：DDL操作
- 常见有
SHARED_READ, SHARED_WRITE, EXCLUSIVE，分别用于DQL，DML及DDL。其它类型操作可参考源码sql/mdl.h - EXCLUSIVE是独占锁，在其持有期间不允许其它类型的MDL被授予，自然也包括DQL和DML操作。这也就是为什么DDL操作被阻塞时，后续其它操作也会被阻塞
3. DDL被阻塞
1. MDL的instrument
- instrument并没有开启（MySQL 8.0是默认开启的）
1. 临时生效
# 查看MDL的instrument状态
select *
from performance_schema.setup_instruments
where NAME = 'wait/lock/metadata/sql/mdl';
# 临时开启，MDL的instrument。实例重启后，又会恢复为默认值。
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
2. 永久生效
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
2. 测试场景
# t1
# 1. 查询mysql当前连接thread
show processlist;
# 2. 查看metadata_locks，需要开启instruments
# lock_type：EXCLUSIVE（排他锁），SHARED_READ（共享锁）
# lock_status："PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。27线程正在等待28线程释放SHARED_READ
select object_type, object_schema, object_name, lock_type, lock_duration, lock_status, owner_thread_id
from performance_schema.metadata_locks;
select object_schema,
object_name,
waiting_thread_id,
waiting_lock_type,
blocking_thread_id,
blocking_pid,
blocking_lock_type,
sql_kill_blocking_query,
sql_kill_blocking_connection
from sys.schema_table_lock_waits;
# t1
# 开启Trx，执行一条sql才真正开启
begin;
select * from mysql_lock where `primary` = '10' for update;
commit;
# t2
# 正常执行，shared_read 和 shared_write 可共存
insert into mysql_lock (`primary`, `unique`) values (rand(), rand());
# block_wait
alter table mysql_lock add column java int;
3. 找出阻塞的会话
1. metadata_locks
- owner_thread_id给出的只是线程ID，并不是
show processlist中的ID。如果要查找线程对应的processlist id，需查询performance_schema.threads表
# thread_51正在等待thread_50释放SHARED_READ
# kill掉50线程对应的《processlist_id》
select THREAD_ID, PROCESSLIST_ID,PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_STATE, PROCESSLIST_INFO
from performance_schema.threads
where thread_id in (50, 51);
- 将这两张表结合，借鉴
sys.innodb_lock_waits的输出，可以直观地呈现MDL的等待关系
SELECT a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
'Metadata Lock' AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID;
# 输出一目了然，DDL操作如果要获得MDL，执行`kill 8`即可
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
|locked_schema|locked_table|locked_type |waiting_processlist_id|waiting_age|waiting_query |
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
|mca |mysql_lock |Metadata Lock|9 |667 | alter table mysql_lock add column java int|
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
+-------------------------------+-----------------------+------------+--------------+----------------------------+
|waiting_state |blocking_processlist_id|blocking_age|blocking_query|sql_kill_blocking_connection|
+-------------------------------+-----------------------+------------+--------------+----------------------------+
|Waiting for table metadata lock|8 |669 |NULL |KILL 8 |
+-------------------------------+-----------------------+------------+--------------+----------------------------+
2. schema_table_lock_waits
- 官方的
sys.schema_table_lock_waits - 实际上，MySQL 5.7在sys库中也集成了类似功能，同样的场景，其输出如下
select *
from sys.schema_table_lock_waits;
select object_schema,
object_name,
waiting_lock_type, -- 排他锁
blocking_thread_id, -- 拥有锁和等待锁，thread_id
blocking_pid, -- processlist_id即要kill的id
blocking_lock_type, -- SHARED_READ共享锁（要kill的锁），SHARED_UPGRADABLE升级锁（升级为排他锁）
sql_kill_blocking_query, -- 执行kill_query_sql
sql_kill_blocking_connection -- 执行kill_sql
from sys.schema_table_lock_waits;
+-------------+-----------+-----------------+------------------+-----------------------+----------------------------+
|object_schema|object_name|waiting_lock_type|blocking_lock_type|sql_kill_blocking_query|sql_kill_blocking_connection|
+-------------+-----------+-----------------+------------------+-----------------------+----------------------------+
|mca |mysql_lock |EXCLUSIVE |SHARED_WRITE |KILL QUERY 8 |KILL 8 |
|mca |mysql_lock |EXCLUSIVE |SHARED_UPGRADABLE |KILL QUERY 9 |KILL 9 |
+-------------+-----------+-----------------+------------------+-----------------------+----------------------------+
4. 阻塞会话已执行sql
- 《threads》其blocking_query为NULL，而在session1中，其明明已经执行了三个SQL
- 这个与
performance_schema.threads（类似于show processlist）有关，其只会输出当前正在运行的SQLPerformance Schema中记录Statement Event（操作事件）的表
| 表 | 作用 | 开启情况 |
|---|---|---|
| events_statements_current | 记录了线程的最近一次操作 | 5.6、5.7、8.0默认开启 |
| events_statements_history | 记录了每个线程最近N个操作，N由performance_schema_events_statements_history_size参数决定，在5.6、5.7、8.0三个版本中，默认值为10 | 5.7、8.0均默认开启 |
| events_statements_history_long | 记录了全局级别的最近N个操作，N由performance_schema_events_statements_history_long_size参数决定，在5.6、5.7、8.0三个版本中，默认值为10000 | |
| prepared_statements_instances |
- 三者的表结构完全一致，其中，
events_statements_history又包含了events_statements_current的操作，这里使用events_statements_history
# 终极SQL
SELECT locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text, 'transaction_begin;', -1) AS blocking_query,
sql_kill_blocking_connection
FROM (
SELECT b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
'Metadata Lock' AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT thread_id,
group_concat(CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN 'transaction_begin' ELSE sql_text END
ORDER BY event_id SEPARATOR ';') AS sql_text
FROM performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE t1.granted_thread_id = t2.thread_id;
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
|locked_schema|locked_table|locked_type |waiting_processlist_id|waiting_age|waiting_query |
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
|mca |mysql_lock |Metadata Lock|10 |3 |alter table mysql_lock add column java int |
|mca |mysql_lock |Metadata Lock|10 |3 |alter table mysql_lock add column java int |
+-------------+------------+-------------+----------------------+-----------+-------------------------------------------+
+-------------------------------+-----------------------+------------+ +----------------------------+
|waiting_state |blocking_processlist_id|blocking_age| |sql_kill_blocking_connection|
+-------------------------------+-----------------------+------------+ +----------------------------+
|Waiting for table metadata lock|21 |7 | |KILL 21 |
|Waiting for table metadata lock|21 |7 | |KILL 21 |
+-------------------------------+-----------------------+------------+ +----------------------------+
# 1. blocking_query包含了T1中所有操作，按执行的先后顺序输出
# 2. 默认情况下，`events_statements_history`只会保留每个线程最近的10个操作，如果事务中进行的操作较多，便无能为力了
+--------------------------------------------------------------------------------------------------+
|blocking_query |
+--------------------------------------------------------------------------------------------------+
|show processlist;select * from mysql_lock;select * from mysql_lock where `primary` = 10 for update|
|show processlist;select * from mysql_lock;select * from mysql_lock where `primary` = 10 for update|
+--------------------------------------------------------------------------------------------------+
4. kill
# 等价于 kill connection 3;
# 终⽌与给定的 thread_id 有关的连接
kill 3;
# 终⽌连接当前正在执⾏的语句，但是会保持连接的状态
kill query 3;