目录
目录
目录
目录
锁如何发生，如何解除。
了解死锁的发生，和解决。
为什么数据库要有锁？作用和影响是什么？没有锁会怎样？
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_locksG
-
通过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_waitsG *************************** 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吧，都是表锁。
