锁如何发生,如何解除。
了解死锁的发生,和解决。
为什么数据库要有锁?作用和影响是什么?没有锁会怎样?
MySQL里都有什么锁?
- MyISAM锁
- InnoDB锁
- 锁类型
- InnoDB锁实现
- InnoDB锁案例
- InnoDB死锁
- InnoDB锁优化
- 锁状态监控
避免并发请求时对同一个数据对象同时修改,导致数据不一致。
锁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)
全局锁(global lock)instance级别
表级锁(table lock)
行级锁(row lock)
还有mutex,不属于锁,InnoDB内部保护机制,不可控,无法手动解锁,只能调整参数优化。
悲观锁
“不信任”其它事务,为了以防万一,总是先对数据对象加锁。
事先不加锁,冲突检测过程中才加锁。(就是当前加锁后,并未真正锁,当另一个session尝试锁或其它行为时发生冲突检测,才察觉到加锁了。如同用书占座,只有当其他人想坐过来的时候,帮占座的人才会出面提出该座位已经被占用(被锁))
select .. for update/for share 就是悲观锁。
乐观锁
不急着加锁,而是等到真的需要进行更新漏了,才再次查询并更新数据。
提前加锁。MGR、PXC先在本节点加锁更新,再广播出去——如果其它节点加锁失败,那么本节点再回滚回去。
QC已经永远说再见了。
waiting for query cache lock
query_cache_type=0 , query_cache_size=0
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
meta data lock
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 |
IX | S | SH | SR | SW | SNW | SNRW | X | |
---|---|---|---|---|---|---|---|---|
IX | √ | √ | √ | √ | √ | √ | √ | √ |
S | √ | √ | √ | √ | √ | √ | √ | × |
SH | √ | √ | √ | √ | √ | √ | √ | × |
SR | √ | √ | √ | √ | √ | √ | × | × |
SW | √ | √ | √ | √ | √ | × | × | × |
SNW | √ | √ | √ | √ | × | × | × | × |
SNRW | √ | √ | √ | × | × | × | × | × |
X | √ | × | × | × | × | × | × | × |
上锁后便可以在performance_schema.metadata_locks 查询到。
上锁后发生锁等待时,可以在sys.schema_table_lock_waits 查询。
有没有锁,和有没有锁等待是两个不同的概念。
启用MDL的P_S统计
打开可以动态打开,立即生效。
但是关闭的话,只有新的session才能有效果(等待亲测)
查询MDL锁状态信息:
通过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
MySQL解除表级锁目前是解除全部表级锁,无法单独解除某个锁。(help get_lock 好像可以,自己研究一下。)
放弃MyISAM吧,都是表锁。
联系客服