MySQL锁：01.总览

锁如何发生，如何解除。

了解死锁的发生，和解决。

为什么数据库要有锁？作用和影响是什么？没有锁会怎样？

MySQL里都有什么锁？

MyISAM锁

InnoDB锁

锁类型

InnoDB锁实现

InnoDB锁案例

InnoDB死锁

InnoDB锁优化

锁状态监控

锁总览

锁的作用

避免并发请求时对同一个数据对象同时修改，导致数据不一致。

加锁流程

事务T1在对某个数据对象R1操作之前，先向系统发出请求，对其加锁L1
之后，事务T1对该数据对象R1有了相应的控制，在T1释放L1之前，其它事务不能修改R1

锁对数据库的影响

锁等待

锁L1锁定某个对象R1，锁L2等待该锁释放，如果不释放，会一直等待，或者达到系统预设的超时阈值后报错，整个事务回滚，或只回滚当前SQL。

可以配置参数，行锁超时后事务会被回滚。

mysql> show global variables like '%rollback%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+
2 rows in set (0.00 sec)

死锁

锁资源请求产生了回路，如：L1等待L2释放，L2等待L3释放，L3等待L1释放，死循环。

锁类型

排他锁（X）
共享锁（S）

锁范围

全局锁（global lock）instance级别
表级锁（table lock）
行级锁（row lock）
还有mutex，不属于锁，InnoDB内部保护机制，不可控，无法手动解锁，只能调整参数优化。

锁方式

悲观锁
- “不信任”其它事务，为了以防万一，总是先对数据对象加锁。
- 事先不加锁，冲突检测过程中才加锁。（就是当前加锁后，并未真正锁，当另一个session尝试锁或其它行为时发生冲突检测，才察觉到加锁了。如同用书占座，只有当其他人想坐过来的时候，帮占座的人才会出面提出该座位已经被占用（被锁））
  
  select .. for update/for share 就是悲观锁。
乐观锁
- 不急着加锁，而是等到真的需要进行更新漏了，才再次查询并更新数据。
- 提前加锁。MGR、PXC先在本节点加锁更新，再广播出去——如果其它节点加锁失败，那么本节点再回滚回去。
  - MGR、PXC用的就是乐观锁。

全局锁

全局读锁

global read lock 全局读锁
一般是因为mysqldump、xtrabackup等备份请求发起
FTWRL，关闭instance下所有表，并加上全局读锁，防止被修改，直到提交unlock tables;
（实例中可能有不同的表引擎，在发起整个实例备份前，先将表关闭一下，避免有其它会话正在修改表结构。关闭再打开并加全局读锁，防止表被DDL修改。）
若用mysqldum之备份InnoDB无需记录binlog时，可以只加--single-transaction，不加--master-data选项。传送门：一致性备份：所有的表状态都在一个时间点。
xtrabackup时可以分开备份InnoDB和MyISAM。
全局读锁时，任何innodb事务不能被提交。
如果都是InnoDB表，可以无需锁表在线热备。

全局QC锁

QC已经永远说再见了。

全局query cache锁（mutex）。最好关闭query cache
对QC中的数据有更新时都会引发query cache lock，对应状态为：waiting for query cache lock
关闭QC：query_cache_type=0 , query_cache_size=0

QC锁存在的问题:

全局任何表数据的更新都会造成请求全局QC锁。
当发起请求时，会将sql及结果放入qc，下次执行同样sql会从qc返回结果。
但是两次执行之间如果发生数据修改，需要更新qc的时候，MySQL简单粗暴的处理方式：只要表数据更新，就会更新qc里关于这个表的所有的cache。所以只要表发生更新，就会请求全局qc这个排他锁，当实例有大量并发更新时，qc就会发生很严重的锁等待。

备份锁 backup lock

8.0新增功能，为了保证备份一致性，需要backup_admin角色权限
发起备份前，执行lock instance for backup，备份结束后执行unlock instance解锁
backup lock的作用时备份期间依旧允许DML操作，以及session级别的DDL操作，例如生成临时表。
（但是建表、改表、删表、repair、truncate、optimize等都被禁止！）
多个会话可以并行持有该锁。
备份锁：降低全局读锁的影响。8.0开始所有数据都用innodb存储，发起备份锁便可以一致性备份。

A backup lock acquired by LOCK INSTANCE FOR BACKUP is independent of transactional locks and locks

taken by FLUSH TABLES tbl_name [, tbl_name****] ... WITH READ LOCK, and the following sequences of statements are permitted:

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

（但是建表、改表、删表、repair、truncate、optimize等都被禁止！）

mysql1> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)

mysql3> select  * from metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE    
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618985816432 | SHARED      | EXPLICIT      | GRANTED     | sql_backup
| TABLE       | performance_schema | metadata_locks | NULL        |       139619055733920 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
2 rows in set (0.00 sec)

mysql2> truncate table k0;
--hang

mysql3> select  * from metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SO
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618985816432 | SHARED              | EXPLICIT      | GRANTED     | sq
| SCHEMA      | kk                 | NULL           | NULL        |       139618851698880 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | dd
| GLOBAL      | NULL               | NULL           | NULL        |       139618851437968 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sq
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618851457584 | INTENTION_EXCLUSIVE | TRANSACTION   | PENDING     | sq
| TABLE       | performance_schema | metadata_locks | NULL        |       139619055733920 | SHARED_READ         | TRANSACTION   | GRANTED     | sq
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
5 rows in set (0.00 sec)


mysql1> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)

mysql1> truncate table k0;    --- 但是session1 自己却能DDL
Query OK, 0 rows affected (0.84 sec)

作为对比，对比一下FTWRL时：当前session也会被阻塞

mysql1> flush table with read lock; 
Query OK, 0 rows affected (0.01 sec) 

mysql1> truncate table k0; 
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

MDL锁

meta data lock

5.5开始引入
备份时防止对象被修改
tablesspace/schema、表、function/procedure/trigger/event等多种对象上加的锁
事务开启后，会锁定表的meta data，其它会话对表有DDL操作时，均需等待MDL释放后方可继续。
锁超时阈值定义参数：lock_wait_timeout，默认锁等待一年……强烈建议调低。避免加MDL锁忘记释放后导致超长阻塞。
即便是只读查询、只读事务，也要尽快结束以释放MDL

MDL锁类型

INTENTION_EXCLUSIVE	意向排他锁，只用于范围上锁，例如lock table write.
SHARED	共享锁，用于访问字典对象，而不访问数据，例如 create table a like b
SHARED_HIGH_PRIO	只访问字典对象，例如 desc table_a
SHARED_READ	共享读锁，用于读取数据，如事务中的select rows
SHARED_WRITE	共享写锁，用于修改数据，如事务中的update rows
SHARED_NO_WRITE	共享非写锁，允许读取数据，阻塞其它TX修改数据，用在ALTER TABLE第一阶段
SHARED_NO_READ_WRITE	用于访问字典，读写数据，阻塞其它TX读写数据，例如lock table write
SHARED_READ_ONLY	只读锁，常见于lock table x read
EXCLUSIVE	排他锁，可以修改字典和数据，例如alter table

MDL锁的兼容性

	IX	S	SH	SR	SW	SNW	SNRW	X
IX	√	√	√	√	√	√	√	√
S	√	√	√	√	√	√	√	×
SH	√	√	√	√	√	√	√	×
SR	√	√	√	√	√	√	×	×
SW	√	√	√	√	√	×	×	×
SNW	√	√	√	√	×	×	×	×
SNRW	√	√	√	×	×	×	×	×
X	√	×	×	×	×	×	×	×

确认MDL锁源头以及消除方法

上锁后便可以在performance_schema.metadata_locks 查询到。
上锁后发生锁等待时，可以在sys.schema_table_lock_waits 查询。
有没有锁，和有没有锁等待是两个不同的概念。

启用MDL的P_S统计
- p_s.setup_consumers.global_instrumentation
- p_s.setup_instruments.wait/lock/metadata/sql/mdl
打开可以动态打开，立即生效。
但是关闭的话，只有新的session才能有效果（等待亲测）
查询MDL锁状态信息：
- show proesslist;
- select * from performance_schema.metadata_locks\G

通过metadata_locks查看MDL锁事件

mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'\G
Empty set (0.00 sec)

mysql2> select *,sleep(1000) from k1 limit 2;

mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139618985616624   #是μs
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 52
       OWNER_EVENT_ID: 54
1 row in set (0.00 sec)

mysql3> select *,sleep(100) from k1 limit 2;

mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139618985616624
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 52
       OWNER_EVENT_ID: 56
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139619052075760
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 53
       OWNER_EVENT_ID: 13

如何拿着metadata lock视图查到的thread_id 找到对应的process？

mysql1> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info                                 |
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 260212 | Waiting on empty queue | NULL                                 |
| 12 | root            | localhost | NULL | Query   |      0 | starting               | show processlist                     |
| 13 | root            | localhost | kk   | Query   |     37 | User sleep             | select *,sleep(1000) from k1 limit 2 |
| 14 | root            | localhost | kk   | Query   |     27 | User sleep             | select *,sleep(100) from k1 limit 2  |
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
4 rows in set (0.00 sec)

mysql1> select * from performance_schema.threads where PROCESSLIST_ID in (13,14);
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
| THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                     | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
|        52 | thread/sql/one_connection | FOREGROUND |             13 | root             | localhost        | kk             | Query               |              149 | User sleep        | select *,sleep(1000) from k1 limit 2 |             NULL | NULL | YES          | YES     | Socket          |          691 | USR_default    |
|        53 | thread/sql/one_connection | FOREGROUND |             14 | root             | localhost        | kk             | Query               |              139 | User sleep        | select *,sleep(100) from k1 limit 2  |             NULL | NULL | YES          | YES     | Socket          |          693 | USR_default    |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+


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

mysql2> update k1 set id=22 where id=2;
Query OK, 1793 rows affected (0.01 sec)
Rows matched: 1793  Changed: 1793  Warnings: 0

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

mysql3> update k1 set id=22 where id=2;


mysql> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139618985616624
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED    #拿到了MDL锁
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 52
       OWNER_EVENT_ID: 66
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139619052075760
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED    #拿到了MDL锁
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 53
       OWNER_EVENT_ID: 19
2 rows in set (0.00 sec)

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info                           |
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 261969 | Waiting on empty queue | NULL                           |
| 12 | root            | localhost | NULL | Query   |      0 | starting               | show processlist               |
| 13 | root            | localhost | kk   | Sleep   |    277 |                        | NULL                           |
| 14 | root            | localhost | kk   | Query   |     14 | updating               | update k1 set id=22 where id=2 |
+----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> select * from sys.schema_table_lock_waits;
Empty set (0.01 sec)

为什么都拿到了MDL锁？
通过前面的MDL锁兼容性表格可知，SW和SW可以兼容并存的。
理解一下可以得知：行锁虽然被阻塞，但是更新所需的MDL锁是拿到的——允许同时加（获取）shared_write锁，只不过在等待行锁而已。

只有对表进行表级别锁时，才会互斥

表级别锁阻塞实验

mysql2> lock table k1 read;
Query OK, 0 rows affected (2 min 37.02 sec)

mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'\
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139618985329072
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 52
       OWNER_EVENT_ID: 69
1 row in set (0.00 sec)

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

mysql3> update k1 set id=22 where id=2;
--hang住了。

mysql1> mysql> select * from performance_schema.metadata_locks where object_schema != 'performance_sGhema'\ 
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139618985329072
            LOCK_TYPE: SHARED_READ_ONLY
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 52
       OWNER_EVENT_ID: 69
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: kk
          OBJECT_NAME: k1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139619052222560
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING # 加SW锁被阻塞
               SOURCE: sql_parse.cc:6052
      OWNER_THREAD_ID: 53
       OWNER_EVENT_ID: 28
2 rows in set (0.00 sec)

查询锁等待信息，利用sys schema查询MDL等待信息

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: kk
                 object_name: k1
           waiting_thread_id: 53
                 waiting_pid: 14
             waiting_account: root@localhost
           waiting_lock_type: SHARED_WRITE
       waiting_lock_duration: TRANSACTION
               waiting_query: update k1 set id=22 where id=2
          waiting_query_secs: 56
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 52
                blocking_pid: 13
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ_ONLY
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 13
sql_kill_blocking_connection: KILL 13
1 row in set (0.00 sec)

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
| Id | User            | Host      | db   | Command | Time   | State                           | Info                           |
+----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 263713 | Waiting on empty queue          | NULL                           |
| 12 | root            | localhost | NULL | Query   |      0 | starting                        | show processlist               |
| 13 | root            | localhost | kk   | Sleep   |    434 |                                 | NULL                           |
| 14 | root            | localhost | kk   | Query   |    218 | Waiting for table metadata lock | update k1 set id=22 where id=2 |
+----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
4 rows in set (0.00 sec)

由此可知，shared_write和shared_read_only互斥。
由查询结果可知，thread 52 阻塞了thread 53的请求，并且给出了处理办法：
- kill query 只能将id里当前在跑的sql中止在这个实验里， kill query 是没用的——show processlist看到，pid 13当前没有语句进行。
- 只有 kill 13才有效。
- 当然了，也可以在13中结束事务，释放锁：
```
mysql2> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 

mysql3> update k1 set id=22 where id=2; 
Query OK, 0 rows affected (5 min 40.74 sec) 
Rows matched: 0  Changed: 0 Warnings: 0 
```

表锁

表锁一般在server层面实现。
InnoDB表还有IS/IX表级锁，以及auto-inc锁。

读锁

lock table t1 read;

持有读锁的会话可以读表，但是不能写表。
允许多个会话同时持有读锁

写锁

lock table t1 write

持有写锁的会话既可以读表，也可以写表
只有持有写锁的会话才可以读写该表
其它会话访问该表或者请求加锁都会被阻塞，直到锁释放。

加锁

lock table t1 read;
- 多个session都可以对一个表加读锁
lock table t1 write
- 也许需要先解除之前上的读锁。（自己试一下）
- 写锁是X锁，同一时间一个表只能被一个session加写锁。

释放锁

MySQL解除表级锁目前是解除全部表级锁，无法单独解除某个锁。（help get_lock 好像可以，自己研究一下。）

unlock tables;
断开连接或者被kill
~~【错误，需要验证版本】执行lock table~~
~~【错误，需要验证版本】显示开启一个事务~~

MyISAM锁

默认是表锁，读写互斥，仅只读共享
读锁，lock table xxx read，自身只读，不能写。其它线程仍可读，不能写。多个线程都可提交read lock
写锁，lock table xxx write，自身可读写，其它线程完全不可读写。
写锁优先级高于读锁
select自动加读锁（共享锁）
其它DML、DDL自动加写锁（排他锁）
释放锁见上文。

放弃MyISAM吧，都是表锁。

posted @ 2020-12-14 16:22 孔个个阅读(509) 评论(0) 编辑收藏举报

刷新页面返回顶部

孔个个的菠萝歌

学习就是玩呀玩